CODEWITHSUNDEEP
mysqlsqlselectleft-join
Plant More Trees
Plant More Trees

Reputation: 65

Mysql: Select with Left Join not working as expected

I have a query of the following type:

select * from tbl_1 where [cond] as aliasA LEFT JOIN tbl_2 as aliasB 
 ON (aliasA.id = aliasB.id) WHERE aliasB.id IS NULL

It appears that it's working except that it's ignoring the last WHERE aliasB.id IS NULL. So, it's just returning the results of:

select * from tbl_1 where cond as aliasA LEFT JOIN tbl_2 as aliasB 
 ON (aliasA.id = aliasB.id)

How would I change the above query to get the results of querying tbl_1 where [cond] displaying only the rows that are not in tbl_2?

Thanks in advance!

Upvotes: 1

Views: 16376

Answers (5)

Pranay Rana
Pranay Rana

Reputation: 176896

Orignal Ans at : How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?

Best way i found is have look to below image clear out you doubt

Check the case 1 and 2 in image will give you your answer

and change you where condition to WHERE aliasB.id is NULL

alt text

Upvotes: 6

Griffin
Griffin

Reputation: 1595

http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

You need to use "IS NULL" instead of "= NULL"

Upvotes: 2

Ike Walker
Ike Walker

Reputation: 65537

You need to use IS NULL to check for NULL values, not = NULL:

WHERE aliasB.id IS NULL

Upvotes: 1

Erik Ekman
Erik Ekman

Reputation: 2066

aliasB.id = NULL will always be false. The correct way is aliasB.id IS NULL or the uglier MySQL-specific aliasB.id <=> NULL

Upvotes: 6

Marco
Marco

Reputation: 57573

You could try:

SELECT * FROM tbl_1 aliasA LEFT JOIN tbl_2 aliasB
ON aliasA.id = aliasB.id
WHERE condA 
  AND aliasB.id IS NULL

Upvotes: 3

Related Questions