I've two tables that are defined exactly the same but have different number of rows. There are five fields that are partial keys of the tables(which is the primary key for both the tables). I wanted to find the rows that are in one table but no the other. I tried using the exists as follows but didn't work.
select * from table1 where not exists (select * from table2)
These tables have the exact same create statement but have difference in the number of rows. I don't know if it is possible to find the difference by using joins. Thanks!
Try the LEFT JOIN ... IS NULL pattern.
SELECT a.*
FROM table1 a
LEFT JOIN tableb b
ON a.f1 = b.f1
AND a.f2 = b.f2
AND a.f3 = b.f3
AND a.f4 = b.f4
AND a.f5 = b.f5
WHERE b.f1 IS NULL
This works because LEFT JOIN, when finding no matching row in b, returns the data from a in its resultset, but returns NULLs for the values in b.
The ON condition looks hairy. But the query planner knows the fields are indexed, so it does the right thing.