sql: select not in

Question

how can I rewrite the following query using JOIN

SELECT * 
FROM table1 
WHERE id NOT IN 
( 
    SELECT t1Id 
    FROM table2 
);

not it is not, I was just curious :) – Muhammad Aug 12 '10 at 13:03 — Muhammad, Aug 12 '10 at 13:03

score 8 · Answer 1 · answered Aug 12 '10 at 12:49

8

SELECT * 
FROM table1 t1
left outer join table2 t2 on t1.id=t2.id
where t2.id is null

answered Aug 12 '10 at 12:49

D'Arcy Rittich

thank you very much! but I don't understand the logic, why use "on t1.id=t2.id"? We are looking for rows in t1 which are not referenced in t2, and you tell it to join on t1.id=t2.id, which means it will join on rows that are referenced in t2... it looks somehow illogical for me also, I noticed that the same query without "outer" works the same.. SELECT * FROM table1 t1 left join table2 t2 on t1.id=t2.id where t2.id is null – Muhammad Aug 12 '10 at 13:00
@Muhammand - the `left outer join` syntax means the rows from t1 will be returned even if the join condition to t2 fails, and the `where t2.id is null` will filter out all the rows where the join condition failed, as in those cases, t2.id will be mapped to null. – Adam Musch Aug 12 '10 at 13:03
@Muhammad: if you remove the `WHERE` clause, you will see that there are `t2.id` values that are `NULL`. This is how `LEFT OUTER JOIN` works, it returns records from the table on the left, regardless of whether the join succeeds or not. We can then take advantage of that to filter by adding a `WHERE` clause that says, "show us only the records that did NOT match." – D'Arcy Rittich Aug 12 '10 at 14:34
If you have many rows in t2 for each row in t1 then you'll have different output than NOT IN. NOT EXISTS is better – gbn Aug 16 '10 at 19:17

score 0 · Answer 2 · answered Aug 12 '10 at 12:49

0

       SELECT * FROM table1 t1
       WHERE NOT EXISTS( 
        SELECT *
        FROM table2 t2 
        Where t1.Id = t2.t1Id);

answered Aug 12 '10 at 12:49

OP did specifically ask for a method using JOIN – Mark Baker Aug 12 '10 at 13:12
It seems a pointless spec to me e.g. they could have said, "rewrite the following query without using the letter 'u'" and you'd have got the points instead ;) – onedaywhen Aug 12 '10 at 14:29
+1 NOT EXISTS is better and safer. @Mark Baker: Whether OP asked for JOIN does not matter: should we blindly answer or aim to improve matters...? JOIN may not give the same output as NOT IN and NOT EXISTS deals with NULL (NOT IN fails) – gbn Aug 16 '10 at 19:16
@gbn Can you explain a bit more, please? You mean the `LEFT JOIN ... WHERE .. IS NULL` does not give same output as `NOT IN` under certain circumstances? Why? – fancyPants Aug 21 '12 at 15:03
@tombom: NOT IN, NOT EXISTS are semijoins. LEFT JOIN is an equijoin. You can get more rows from the LEFT JOIN. See http://dba.stackexchange.com/a/4010/630 and http://dba.stackexchange.com/a/17408/630 and http://stackoverflow.com/a/6966259/27535 – gbn Aug 21 '12 at 17:00
@gbn Thank you very much, will have a look at it. – fancyPants Aug 21 '12 at 17:06

2 Answers2