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

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'

CodePudding user response：

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

CodePudding user response：

When you use uppercase letters in SQL use quotation marks both for table names and columns. Try:

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