Comments posted to this topic are about the item Symmetric Except
Igor Micev,My blog: www.igormicev.com
Hall of Fame
I'm not quite sure how the correct answer will produce the line with dashes 😉
Hall of Fame
Very interesting and I think this would be the same as
(select a from tbl1 WHERE a NOT IN (select a from tbl2)) union (select a from tbl2 WHERE a NOT IN (select a from tbl1))
BUT, how often do you run into selecting 1 column of data from 2 or more tables with no Primary key to link ?
Can anyone provide a bigger picture of how this could be used in corporate data structure ?
---Always learning, and never as smart as I used to believe I was ... 🙂
Thanks for an interesting question Igor. I had the biggest problem with an estimate of the result the answer No. 4, where there were no brackets but I did it... 🙂
gvoshol 73146 - Thursday, March 29, 2018 6:42 AM
Run the query in SSMS with the "Results to Text (Ctrl + T)" mode turned on.
select coalesce ( tbl1.a, tbl2.a ) as a
from tbl1 full outer join tbl2 on tbl1.a = tbl2.a
where tbl1.a is null or tbl2.a is null
Hall of Fame
I like this question. Thanks.
The advantages of this syntax are that it lets you compare two output streams to identify rows that differ, and that it treats NULLs as being equivalent. EXCEPT and INTERSECT are really useful when you need NULL to equal NULL, and as Paul White has discussed, they use a special SQL Plan Operator to perform better than alternative approaches.
For comparing query output, before and after snapshots of tables, etc., I typically use the following snippet:
DSO AS (
DSN AS (
SELECT 'Old' AS Tbl, * FROM (SELECT * FROM DSO EXCEPT SELECT * FROM DSN) AS U1
SELECT 'New' AS Tbl, * FROM (SELECT * FROM DSN EXCEPT SELECT * FROM DSO) AS U2
ORDER BY Tbl DESC;
The reason I use the DSO and DSN CTEs is to isolate the "diff" portion of the query from the portion that identifies the data sets being diffed. This way I only need to replace #Old and #New in one place each. It also lets me replace the * in the DSO and DSN CTEs with an explicit column list if I want to exclude specific columns from the comparison. I can further customize it by modifying the ORDER BY clause at the end - I will frequently stick a candidate key at the beginning. This can be useful for regression testing - dump the output from two different versions of a query into temp tables, then compare the output.
Thanks for the question! I'd be curious to know Gail Shaw's or Jeff Moden's take on this kind of query.
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
One Orange Chip
'a' is the column name here and the dashes is the separator between the column name and values returned for this confused me at first glance.
Just a tip, remember to use union all instead of union when there is no need to remove duplicates.
diego.perdices - Monday, April 2, 2018 12:37 AM
Good catch! I added an ALL to my generic table diff query.
Nice one, thanks Igor
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Viewing 14 posts - 1 through 13 (of 13 total)