September 30, 2011 at 3:00 am
The 2 following statements don't give the same result.Why is it so ?
SELECT id FROM a except select id from b ORDER BY id desc;
id
------
3651
(1 row)
SELECT id FROM a WHERE id not in (select
id from b) ORDER BY id desc;
id
----
(0 rows)
Little googling told that
EXCEPT is defined in terms of duplicates based on distinctness, and for
example (1 is distinct from 1) is false, (1 is distinct from NULL) is true
and (NULL is distinct from NULL) if false.
NOT IN is defined in terms of equality, and for example, (1=1) is true,
(1=NULL) is unknown and (NULL=NULL) is unknown.
Can you guys give some more clarity or pointers?
September 30, 2011 at 3:31 am
They should return the same results. Probably you oversimplified the original issue when posting the question.
Can you post the original query and sample data?
Something like this:
DECLARE @a TABLE (
id int
)
INSERT INTO @a VALUES (1)
INSERT INTO @a VALUES (2)
INSERT INTO @a VALUES (3)
INSERT INTO @a VALUES (4)
DECLARE @b-2 TABLE (
id int
)
INSERT INTO @b-2 VALUES (1)
INSERT INTO @b-2 VALUES (2)
INSERT INTO @b-2 VALUES (4)
SELECT id FROM @a
EXCEPT
select id FROM @b-2
ORDER BY id desc;
SELECT id
FROM @a
WHERE id not in (
SELECT id
FROM @b-2
)
ORDER BY id desc;
Anyway, EXCEPT compares the whole result sets with a SORT/DISTINCT operation, while NOT IN compares just the field you decide to compare.
-- Gianluca Sartori
September 30, 2011 at 3:37 am
You should not compare NULL with equality operators. you must use 'IS NULL' or 'IS NOT NULL' operators.
Thus (1=NULL) and (NULL=NULL) are bad operations.
Else, you may override default behavior of SQL Server by setting ANSI NULL settings.
September 30, 2011 at 3:52 am
~Dev~ (9/30/2011)
You should not compare NULL with equality operators. you must use 'IS NULL' or 'IS NOT NULL' operators.Thus (1=NULL) and (NULL=NULL) are bad operations.
Else, you may override default behavior of SQL Server by setting ANSI NULL settings.
Quite true, but has nothing to do with the question as it is phrased.
Probably the OP has left out an important part of the question here.
-- Gianluca Sartori
September 30, 2011 at 4:34 am
Please try following piece of code. Hope it will justify my previous post.
CREATE TABLE T1
(C1 INT);
CREATE TABLE T2
(C2 INT);
INSERT INTO T1 VALUES (3651);
INSERT INTO T2 VALUES (NULL);
SELECT * FROM T1,T2;
SELECT * FROM T1
EXCEPT
SELECT * FROM T2;
SELECT * FROM T1 WHERE C1 NOT IN (SELECT * FROM T2);
September 30, 2011 at 5:47 am
Oh, I see what you mean, thanks.
-- Gianluca Sartori
October 2, 2011 at 11:26 am
~Dev~ (9/30/2011)
You should not compare NULL with equality operators. you must use 'IS NULL' or 'IS NOT NULL' operators.Thus (1=NULL) and (NULL=NULL) are bad operations.
Else, you may override default behavior of SQL Server by setting ANSI NULL settings.
In most cases, I'd agree with 100% but that's not true with EXCEPT. EXCEPT correctly evaulates (NULL=NULL) as True and both (1=NULL) and (NULL=1) as False. If you lookup EXCEPT in Books Online, you'll find the following statement in the REMARKS section...
When you compare rows for determining distinct values, two NULL values are considered equal.
Here's some code that demonstrates that fact...
WITH
cteA (ID) AS
(
SELECT CAST(NULL AS INT) UNION ALL
SELECT 1 UNION ALL
SELECT 2
),
cteB (ID) AS
(
SELECT CAST(NULL AS INT) UNION ALL
SELECT 1 UNION ALL
SELECT 3
)
SELECT ID FROM cteA
EXCEPT
SELECT ID FROM cteB
GO
WITH
cteA (ID) AS
(
SELECT CAST(NULL AS INT) UNION ALL
SELECT 1 UNION ALL
SELECT 2
),
cteB (ID) AS
(
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 3
)
SELECT ID FROM cteA
EXCEPT
SELECT ID FROM cteB
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply