Select unmatched data from two columns
-
Comments posted to this topic are about the item Select unmatched data from two columns
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius -
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.
-
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;
-
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 questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
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: -
This was removed by the editor as SPAM
-
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 -
Koen Verbeeck (11/26/2012)
I wonder why the explanation talks about EXCEPT when none of the answers use it.+1 😉
-
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 nullcan't return anything that isn't null, which immediately eliminates options 2,4,5 and 6, leaving only 1 and 3 as possibilities.
Tom
-
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 questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
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/ -
+1:) easy basic question
-
Lokesh Vij (11/26/2012)
One more way without using EXCEPT or INTERSECTSELECT 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.
-
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.IdFROM 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/
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply