select all elements in one table that do not appear in another table

[lli2k5]

Say that I have a table A with column colA and table B with colB.

How do I select all elements of colA in table A that are not in colB of table B?

[Fumigator]

SELECT colA
FROM tableA as a
WHERE NOT EXISTS (SELECT * FROM tableB as b WHERE b.colB = a.colA)

[miller]

SELECT A.colA
FROM A
LEFT JOIN B ON B.colB=A.colA
WHERE B.colB IS NULL;


Yes, I know it looks wierd, but this is what you want. Please note, this may take a while if colB is not indexed and the tables are large.