Find rows in table A which do not exist in table B.

Question

Find rows in table A which do not exist in table B.

ben.brugman

SSChampion

Points: 13369
More actions
April 25, 2014 at 6:11 am

#284822

I want to find the rows in table A which are not present in table B.
Can this be done without comparing all colums ?
Ben
With the Count(*) function I can find the number of rows which are different, but not show the actual differences.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

By definition, there is no "first row" in a table. It all depends on the columns in the ORDER BY section of the SELECT statement, what row will be presented as the "first row".

If you perform a LEFT OUTER JOIN to table B you'll get all rows from table A that do not match the criteria specified in the ON section of the query.

Using ORDER BY you can define, which row will be the "first row" to display.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

gbritton1 SSCertifiable Points: 6522 More actions · Answer 2

Assuming A and B have the same schema:

select * from A

except

select * from B

SQLSeTTeR SSCertifiable Points: 5481 More actions · Answer 3

Below are three different ways to identify what exists in TableA that does not exist in TableB. Pick your flavor.

--EXCEPT

SELECT * FROM TABLEA

EXCEPT

SELECT * FROM TABLEB

--LEFT OUTER JOIN

SELECT a.* FROM TABLEA AS a

LEFT OUTER JOIN TABLEB AS b

ON a.COL1 = b.COL1

WHERE b.COL1 IS NULL

--NOT EXISTS

SELECT * FROM TABLEA AS a

WHERE NOT EXISTS (SELECT 1 FROM TABLEB as B

WHERE a.COL1 = b.COL1)

ben.brugman SSChampion Points: 13369 More actions · Answer 4

gbritton1 (4/25/2014)
Assuming A and B have the same schema:
select * from A
except
select * from B

Thank you this is exactly what I was looking for.

Ben

LutzM (4/25/2014)
By definition, there is no "first row" in a table. It all depends on the columns in the ORDER BY section of the SELECT statement, what row will be presented as the "first row".
Using ORDER BY you can define, which row will be the "first row" to display.

Although the above is completely true, I am missing the relevance off this in the scope of my question.

If you perform a LEFT OUTER JOIN to table B you'll get all rows from table A that do not match the criteria specified in the ON section of the query.

The question was : Can this be done without comparing all columns ?

I am familiar with the technique that I can find all rows in A with a left outer join, but I am looking for a technique were I do not have to compare all columns.

Thanks