What is the difference between a LEFT JOIN and a sub-query?

Question

I am attempting to find all of the results in table1 that do not have an associated record in table2. I have 2 queries:

1. SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.table1_id WHERE table2.table1_id IS null This works and returns all of the results in table1 that are not in table 2

2. SELECT * FROM table1 WHERE table1.id NOT IN (SELECT table2.table1_id FROM table2) This does not work. It returns zero results. I've ran each of the parts individually and it appears this should work.

Will someone please explain why one works where the other doesn't

Edit: Added purpose of queries

Edit 2: Added sqlFiddle. http://sqlfiddle.com/#!17/ab473/1 It works on here, so I have no idea why it isn't working on my local computer

Edit 3: sqlFiddle replicating the issue http://sqlfiddle.com/#!17/bd92f7/1. Removing the null value resolved the issue.

Answer 1

I think that both of your queries are not ideal:

• The first query can return a row from table1 more than once if there is more than one matching row in table2. Sure, you can fix that with a DISTINCT, but the performance is not ideal.

• The second query won't work if there is a row in table2 where table1_id IS NULL:

  • x NOT IN (1, 2, NULL) is equivalent to x <> 1 AND x <> 2 AND x <> NULL

  • now x <> NULL is always NULL, no matter what x is

  • <anything> AND NULL is never TRUE

  • so the condition with never be TRUE, which explains why you get no result

The best (and most efficient) query is:

SELECT * FROM table1
WHERE NOT EXISTS (SELECT 1 FROM table2
                  WHERE table1.id = table2.table1_id);