CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Sergio Tapia
Sergio Tapia

Reputation: 41128

Having trouble with this simple SQL Select statement

I have two tables, A and B.

Both have the exact same columns.

I need to select all the items in TableA that ARE NOT in TableB.

This is intersection, right? How can I do this?

Upvotes: 0

Views: 557

Answers (5)

joeslice
joeslice

Reputation: 3454

assuming TableA and TableB have a primary key of name id.

select TableA.* 
  from TableA 
left outer join TableB on TableB.id = TableA.id
where TableB.id is null;

This will find all entries where table b does not have an instance of table a's id.

Upvotes: 6

Ralph Lavelle
Ralph Lavelle

Reputation: 5769

or NOT IN

SELECT *
FROM TableA
WHERE TableA.Id NOT IN (SELECT TableB.Id FROM TableB)

Upvotes: 1

tvanfosson
tvanfosson

Reputation: 532445

You have your terminology wrong. The intersection would be the rows that are in both Table A and Table B. What you are actually looking for is the relative complement of A and B. To get a relative complement you want to do an antijoin:

SELECT * FROM TableA EXCEPT SELECT * FROM TableB.

Upvotes: 2

Kibbee
Kibbee

Reputation: 66112

You could use the EXISTS clause

SELECT * FROM TableA
WHERE NOT Exists
(
SELECT Column1 FROM TableB 
WHERE TableA.Column1 = Table2.Column1 
AND TableA.Column2 = Table2.Column2 
....
)

Replace .... with the rest of the columns in the two tables.

Upvotes: 4

Pierre-Alain Vigeant
Pierre-Alain Vigeant

Reputation: 23083

SELECT ColumnA, ColumnB
FROM TableA
EXCEPT
SELECT ColumnA, ColumnB
FROM TableB

Upvotes: 2

Related Questions