Mysql: Select with Left Join not working as expected

Question

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!

dont forget to mark answer as accpeted and upvote it if you got the info you want — Pranay Rana, Mar 14 '12 at 19:07

score 6 · Answer 1 · answered Mar 14 '12 at 18:37

6

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

answered Mar 14 '12 at 18:37

Erik Ekman

You're correct. Unfortunately, I meant to say IS NULL (which is how my code is written) but the results are as I said (not giving me what I want). I edited my post, correcting the IS NULL portion. – Plant More Trees Mar 14 '12 at 18:54
Then I think you should try and move the first condition to the `WHERE` clause after the join, like Marco writes. – Erik Ekman Mar 14 '12 at 18:58

score 6 · Answer 2 · edited Jun 20 '20 at 09:12

6

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

edited Jun 20 '20 at 09:12

Community

answered Mar 14 '12 at 18:39

Pranay Rana

score 3 · Accepted Answer · answered Mar 14 '12 at 18:37

3

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

answered Mar 14 '12 at 18:37

Marco

score 2 · Answer 4 · answered Mar 14 '12 at 18:39

2

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

answered Mar 14 '12 at 18:39

Griffin

score 1 · Answer 5 · answered Mar 14 '12 at 18:38

1

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

WHERE aliasB.id IS NULL

answered Mar 14 '12 at 18:38

Ike Walker

5 Answers5