March 29, 2018 at 12:05 am
Comments posted to this topic are about the item Symmetric Except
Igor Micev,My blog: www.igormicev.com
March 29, 2018 at 6:42 am
I'm not quite sure how the correct answer will produce the line with dashes 😉
March 29, 2018 at 6:56 am
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 ... 🙂
March 29, 2018 at 7:05 am
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... 🙂
March 29, 2018 at 7:09 am
gvoshol 73146 - Thursday, March 29, 2018 6:42 AMI'm not quite sure how the correct answer will produce the line with dashes 😉
Run the query in SSMS with the "Results to Text (Ctrl + T)" mode turned on.
March 29, 2018 at 7:32 am
March 29, 2018 at 8:57 am
Another way:
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
March 29, 2018 at 9:54 am
I like this question. Thanks.
March 29, 2018 at 11:09 am
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:WITH
DSO AS (
SELECT *
FROM #Old
),
DSN AS (
SELECT *
FROM #New
)
SELECT 'Old' AS Tbl, * FROM (SELECT * FROM DSO EXCEPT SELECT * FROM DSN) AS U1
UNION ALL
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.
March 30, 2018 at 8:00 am
Thanks for the question! I'd be curious to know Gail Shaw's or Jeff Moden's take on this kind of query.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 1, 2018 at 11:32 pm
'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.
----------------------------------------------------
April 2, 2018 at 12:37 am
Nice question.
Just a tip, remember to use union all instead of union when there is no need to remove duplicates.
April 2, 2018 at 10:27 am
diego.perdices - Monday, April 2, 2018 12:37 AMNice question.
Just a tip, remember to use union all instead of union when there is no need to remove duplicates.
Good catch! I added an ALL to my generic table diff query.
April 2, 2018 at 11:31 pm
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)
You must be logged in to reply to this topic. Login to reply