how can I rewrite the following query using JOIN
SELECT *
FROM table1
WHERE id NOT IN
(
SELECT t1Id
FROM table2
);
views:
52answers:
2
A:
SELECT * FROM table1 t1
WHERE NOT EXISTS(
SELECT *
FROM table2 t2
Where t1.Id = t2.t1Id);
Michael Pakhantsov
2010-08-12 12:49:29
OP did specifically ask for a method using JOIN
Mark Baker
2010-08-12 13:12:56
@Mark, ops, missed that :(
Michael Pakhantsov
2010-08-12 13:19:30
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
2010-08-12 14:29:26
+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
2010-08-16 19:16:34
+7
A:
SELECT *
FROM table1 t1
left outer join table2 t2 on t1.id=t2.id
where t2.id is null
RedFilter
2010-08-12 12:49:38
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 mealso, I noticed that the same query without "outer" works the same.. SELECT * FROM table1 t1left join table2 t2 on t1.id=t2.idwhere t2.id is null
Muhammad
2010-08-12 13:00:31
@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
2010-08-12 13:03:23
@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."
RedFilter
2010-08-12 14:34:21
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
2010-08-16 19:17:35