SQL Server opposite of join?

[longhairfreak]

What is the opposite of join in sql server 7 and how do I use it? I have two tables and I want to pull the records that are in one table but not in the other. I know in paradox its count = 0 but i cant figure it out for sql.

[BruceG]

You can do something like this:

select * from t1 where t1.keyfield not in  (select t2.keyfield from t2)

[Mongo]

You could also use:

SELECT tableA.fieldname 
FROM tableA 
LEFT OUTER JOIN tableB 
  ON tableA.fieldname = tableB.fieldname 
WHERE tableB.fieldname is null

Assuming your comparison fields are indexed, this is kinder to your server than using an IN clause on larger tables. Hope it helps.