So I executed this command:

select*
from customer
full.join orders
on customer.ID = orders.customerid;

it gave me this error "error 1054 unknown column 'customer.ID' in 'on clause'

The strange thing is, when I execute the same set of commands for an inner join, it works just fine.

What's that dot for between full join?


https://www.codeproject.com/Articles/33052/...on-of-SQL-Joins

This post has been edited by narf03: Sep 16 2020, 10:42 PM

Thanks, I removed the dot, same problem.

Already suggested before, post to sqlfiddle ... it will be easier for others to help.

Try this on Oracle SQL. Might be diff for other variants



This post has been edited by jackoats.my: Sep 17 2020, 08:34 PM

Is there a column called ID in your customer table?

>>>unknown column 'customer.ID'



This post has been edited by jackoats.my: Sep 17 2020, 08:38 PM

FULL OUTER JOIN is equivalent to

LEFT OUTER JOIN
UNION
RIGHT OUTER JOIN

So you can also try the above step if the FULL OUTER JOIN doesn't work.

yes there is

very strange...

There is nothing strange about it. If there's actually no intersections between the two tables, then there will be no outer join. The error message might be telling you that.

But then you're saying there is an inner join between the two tables...

So there could a typo when you change from inner join to full outer join, or syntax error if MySQL does not support the syntax FULL JOIN.

This post has been edited by jackoats.my: Sep 17 2020, 09:14 PM

ts, you sure you are using mysql?

last i checked mysql dont support FULL JOIN and needs to be written as below to achieve it . UNION vs. UNION ALL depends on the outcome you're looking for

SELECT * FROM A
LEFT JOIN B ON A.key = B.key

UNION

SELECT * FROM A
RIGHT JOIN B ON A.key = B.key

yes I am. I’m new to this, so a lot of syntax I dont know.

interestingly enough when using FULL JOIN, mysql complains about unknown column but when infact it doesnt support it
your best bet is to rewrite it using UNION/UNION ALL

Ahh I just realized something. There is no ID column for both tables. In one table the column is called 'customerID', in the other table is just 'ID'. Is that the reason for the error ?

I used right outer join and it works

Thanks again. Could you suggest a website where I could get the whole list of syntax ?

error is complaining about 'on clause' meaning your joining condition.
most likely mysql is wrongly returning the error due to "full join"

Ha, you can read the MySQL manual (documentation)


This post has been edited by jackoats.my: Sep 20 2020, 12:11 AM