getting rows that r not in other table

[kemu_]

my problem is that i would need to get all rows that r not in another table i have...

these rows have a certain col that has items "thing1" etc..
here is a little example:

table1:
thing1
thing2
thing3
thing4
etc...

table2:
thing3
thingx

so i would want to get those rows from table 1 that arent in table 2... but i havent figured out how...

select * from table1, table2 where not table1.col = table2.col
doesnt work

select * from table1, table2 where table1.col != table2.col
doesnt work either....

i know this isnt too well told, but its the best exmple i could come up with 🙂

[bormuff98]

Amusingly, I was having the same problem and came to this board to find an answer (or point me in the right direction). I now have the answer:

SELECT table1.* 
FROM table1 
LEFT JOIN table2 
ON table1.id=table2.id 
WHERE table2.id IS NULL

HTH

[Sxooter]

You can use except, like so:

select from a except (select from b);