Select unmatched data from two columns

Question

Select unmatched data from two columns

Viewing 10 posts - 16 through 24 (of 24 total)

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

SQL Kiwi (11/26/2012)
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.

Perfect! Very rightly said.

It is very easy to understand those joins when we compare and contrast with set-theory concepts 🙂

~ 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

paul s-306273 SSChampion Points: 10727 More actions · Answer 2

paul s-306273

SSChampion

Points: 10727

November 26, 2012 at 7:57 am

#1562245

Interesting qotd.

Dana Medley SSCertifiable Points: 6764 More actions · Answer 3

So many words for a Monday morning question; I ended up reading it 4 or 5 times before answering. :hehe:

Thank you very much, it was a good one.

Everything is awesome!

Kick6Tiger SSCrazy Points: 2380 More actions · Answer 4

The oddity that I find interesting about this question is the fact that the UNION automatically sorts the results. In my head I'm thinking the results would be 4,7,2,8 and 2,8,4,7 from the two correct options.

***I now see that using the UNION ALL would not do the sorting to look for duplicates and would have given me the results that I expected.

Aigle de Guerre!

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 5

(Bob Brown)

SSCrazy

Points: 2705

November 26, 2012 at 10:14 am

#1562306

Great question. Gave me something to think about.

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

Meow Now (11/26/2012)
The oddity that I find interesting about this question is the fact that the UNION automatically sorts the results. In my head I'm thinking the results would be 4,7,2,8 and 2,8,4,7 from the two correct options.
***I now see that using the UNION ALL would not do the sorting to look for duplicates and would have given me the results that I expected.

It depends on the physical implementation of the UNION / UNION ALL by the query optimizer. Depending on your SQL Server version, you will / might see different results with e.g. OPTION (HASH UNION) or OPTION (MERGE UNION). As always, ORDER BY is required for output ordering guarantees 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi