select a value where it doesn't exist in another table

sql sql-server-2000
Wai Wong picture Wai Wong · Jun 4, 2010 · Viewed 133.1k times · Source

I have two tables

Table A:

ID
1
2
3
4

Table B:

ID
1
2
3

I have two requests:

  • I want to select all rows in table A that table B doesn't have, which in this case is row 4.
  • I want to delete all rows that table B doesn't have.

I am using SQL Server 2000.

Answer

Tim Schmelter picture Tim Schmelter · Jun 4, 2010

You could use NOT IN:

SELECT A.* FROM A WHERE ID NOT IN(SELECT ID FROM B)

However, meanwhile i prefer NOT EXISTS:

SELECT A.* FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE B.ID=A.ID)

There are other options as well, this article explains all advantages and disadvantages very well:

Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
How to Join to first row

I'll use a concrete, but hypothetical, example. Each Order normally has only one line item: Orders: OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A LineItems: LineItemGUID Order ID Quantity Description ============ ======== ======== ================================= {098FBE3...} 1 7 prefabulated amulite {1609B09...} 2 32 spurving bearing But occasionally there …

Read More
How to get the first and last date of the current year?

Using SQL Server 2000, how can I get the first and last date of the current year? Expected Output: 01/01/2012 and 31/12/2012

Read More