May 18, 2016 at 5:54 am
Nice and simple, but illustrate a set operator, which is usually a very efficient approach. Thanks.
May 18, 2016 at 6:29 am
Haven't used the EXCEPT operator much but am familiar how it would work.
May 18, 2016 at 6:56 am
Isn't this exactly the same as NOT EXISTS? Is there a reason to use one method over the other?
I rewrote the query using not exists and got the same results and same execution plan. "Not Exists" version below.
SELECT
*
FROM
( SELECT
1 AS Id
UNION ALL
SELECT
2
UNION ALL
SELECT
3
UNION ALL
SELECT
4
UNION ALL
SELECT
3
) TblA
WHERE NOT EXISTS
(SELECT *
FROM(SELECT
1 AS Id
UNION
SELECT
2
UNION
SELECT
3
UNION
SELECT
4
UNION
SELECT
3
) TblB
WHERE TblB.Id=TblA.Id);
May 18, 2016 at 7:07 am
I selected "3 rows", then just as I was about to submit my answer, I noticed the scrollbar. After scrolling down, I quickly changed my answer.
Apparently, I've had just enough coffee to function today. I'd better get another cup, just to be sure.
May 18, 2016 at 7:34 am
This was removed by the editor as SPAM
May 18, 2016 at 9:41 am
Very good question..
May 18, 2016 at 11:33 am
Ken Wymore (5/18/2016)
Isn't this exactly the same as NOT EXISTS? Is there a reason to use one method over the other?I rewrote the query using not exists and got the same results and same execution plan. "Not Exists" version below.
SELECT*
FROM
( SELECT
1 AS Id
UNION ALL
SELECT
2
UNION ALL
SELECT
3
UNION ALL
SELECT
4
UNION ALL
SELECT
3
) TblA
WHERE NOT EXISTS
(SELECT *
FROM(SELECT
1 AS Id
UNION
SELECT
2
UNION
SELECT
3
UNION
SELECT
4
UNION
SELECT
3
) TblB
WHERE TblB.Id=TblA.Id);
In this simple example, they are equivalent, but there are two main conditions where EXCEPT is simpler.
The WHERE clause in the NOT EXISTS version uses three-value logic (true, false, unknown) whereas the EXCEPT clause uses two-value logic (true, false). You need to handle logic to the NOT EXISTS version to determine whether the corresponding fields in both results are both NULL.
With multiple fields, your WHERE clause in the NOT EXISTS version quickly becomes more complex, particularly if you have to handle NULL values.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 19, 2016 at 5:18 am
92% right!
Pretty good for QOTD.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply