Select unmatched data from two columns

Question

Post reply

Select unmatched data from two columns

chgn01

SSCarpal Tunnel

Points: 4354
More actions
November 24, 2012 at 11:51 pm

#271153

Comments posted to this topic are about the item Select unmatched data from two columns
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
November 26, 2012 at 12:20 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 24 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Thanks for a good question to start the week.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

bochambers Say Hey Kid Points: 700 More actions · Answer 2

I've had to deal with this issue a lot, due to incomplete relationships in databases due to poor rules governing primary/foreign key relationships not being enforced. Another alternative is to use NOT EXISTS in a where clauses which from experience can be more efficient, particularly for relationships that aren't over a unique join condition. I hadn't looked at EXCEPT before and found it a useful comment and will see how it performs in the future.

Thanks.

Paul White SSC Guru Points: 150467 More actions · Answer 3

I like this way of expressing the solution:

SELECT t1.ID FROM dbo.T1 AS t1

UNION

SELECT t2.ID FROM dbo.T2 AS t2

EXCEPT

SELECT t1.ID FROM dbo.T1 AS t1

INTERSECT

SELECT t2.ID FROM dbo.T2 AS t2;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Lokesh Vij SSChampion Points: 10836 More actions · Answer 4

Thanks for a good question to start the week!

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

demonfox SSCertifiable Points: 6289 More actions · Answer 5

Too many queries , too many aliases ; I started to loose patience ...

but it said 2 correct , and found that initially ...

saved the day ..

an easy one to start the day..

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

So much to read on a Monday morning 🙂

I wonder why the explanation talks about EXCEPT when none of the answers use it.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Toreador SSChampion Points: 11365 More actions · Answer 7

Koen Verbeeck (11/26/2012)
I wonder why the explanation talks about EXCEPT when none of the answers use it.

+1 😉

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Nice basics question.

Odd sort of explanation though - maybe the author thought the references explained it well enough that he didn't need to say anything himself about why the actual answer is right and other options wrong. For the few people who got this wrong it might have been worth pointing out that any select statement of the general form

select col from something where col is null

can't return anything that isn't null, which immediately eliminates options 2,4,5 and 6, leaving only 1 and 3 as possibilities.

Tom

Lokesh Vij SSChampion Points: 10836 More actions · Answer 9

SQL Kiwi (11/25/2012)
I like this way of expressing the solution:
SELECT t1.ID FROM dbo.T1 AS t1
UNION
SELECT t2.ID FROM dbo.T2 AS t2
EXCEPT
SELECT t1.ID FROM dbo.T1 AS t1
INTERSECT
SELECT t2.ID FROM dbo.T2 AS t2;

One more way without using EXCEPT or INTERSECT

SELECT Isnull(a.id, b.id)

FROM t1 a

FULL OUTER JOIN t2 b

ON a.id = b.id

WHERE a.id IS NULL

OR b.id IS NULL

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

kapil_kk SSC-Insane Points: 21316 More actions · Answer 10

Very good question to start of the week with joins 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kalyani.k478 Default port Points: 1400 More actions · Answer 11

kalyani.k478

Default port

Points: 1400

November 26, 2012 at 3:10 am

#1562115

+1:) easy basic question

Paul White SSC Guru Points: 150467 More actions · Answer 12

Lokesh Vij (11/26/2012)
One more way without using EXCEPT or INTERSECT
SELECT Isnull(a.id, b.id)
FROM t1 a
FULL OUTER JOIN t2 b
ON a.id = b.id
WHERE a.id IS NULL
OR b.id IS NULL

That's the one I wrote first (though I used COALESCE, as it happens). The optimizer's expansion of the full join to left join concat anti-semi join eventually led me to the one that uses UNION, INTERSECT and EXCEPT. I like that combination of operators; it's the set-theoretic idea of difference being the union minus the intersection.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 13

Koen Verbeeck (11/26/2012)
I wonder why the explanation talks about EXCEPT when none of the answers use it.

Probably because the author of the question realized that it would have been so much easier to do this with EXCEPT instead of using these left and right anti-semi-join patterns.

In code that is supposed to be supported in the future, I'd always use

SELECT a.Id

FROM T1 AS a

WHERE NOT EXISTS

(SELECT *

FROM T2 AS b

WHERE b.Id = a.Id)

UNION ALL -- All solutions in the question should have used UNION ALL too!!

SELECT b.Id

FROM T2 AS b

WHERE NOT EXISTS

(SELECT *

FROM T1 AS a

WHERE a.Id = b.Id);

I also worked out a solution with a full outer join and no union, and a solution with intersect and except, but those (or cariations that are close enough) are already posted.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/