What is wrong with the WHERE clause in this SQL query?

select *
from tblProduct full join tblProductSales on tblProduct.id = tblProductSales.id
where tblProduct.id <> tblProductSales.id;

I fixed the syntax errors and it still won’t run. I keep getting the following error:

unknown column ‘tblProduct.id’ in ‘where clause’

Note that there is a column ‘id’ in the table ‘tblProduct’

Answer

MySql does not support full joins, the error does not describe the problem.

One workaround for full joins in MySql would be to union two queries to return all from left, with gaps, and all from the right, with gaps.

create table tblProduct (id integer);

✓

create table tblProductSales (id integer);

✓

INSERT INTO tblProduct VALUES (1),(2),(3),(8),(9),(9);

INSERT INTO tblProductSales VALUES (1),(1),(2),(3),(4),(5);


SELECT * FROM tblProduct P
LEFT JOIN tblProductSales S ON P.id = S.id
UNION ALL
SELECT * FROM tblProduct P
RIGHT JOIN tblProductSales S ON P.id = S.id
WHERE P.id IS NULL

✓

✓

  id |   id
---: | ---:
   1 |    1
   1 |    1
   2 |    2
   3 |    3
   8 | null
   9 | null
   9 | null
null |    4
null |    5

db<>fiddle here