SQL Server opposite of join?

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.

You can do something like this:

Code:

---

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

---

You could also use:

Code:

---

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. :)