March 1, 2012 at 2:54 am
Same problem if you specify a subquery in the NOT IN clause instead of values.
If one row returned from the subquery contains NULL, the main query doesn't return rows.
SELECT * FROM Statuses s
WHERE s.Stat not in(SELECT top 2 Statuses.Stat FROM Statuses ORDER BY Statuses.Stat)
March 1, 2012 at 3:50 am
Great question, nice and straightforward.
Without getting into the detail, comparing against NULL other than using IS or IS NOT mostly results in nothing.
_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]
March 1, 2012 at 4:12 am
Strange that the discussion is about the fact that the explanation is incorrect, and that no one says that the anwers is incorrect. The correct answer is 0, and not nothing. 😀
March 1, 2012 at 4:45 am
Woooohooooo 1 is mine today. 🙂
SELECT ID, Name FROM
(
SELECT 1 AS ID, 'Active' AS name
UNION ALL
SELECT 2, 'Active'
UNION ALL
SELECT 3, 'InActive'
UNION ALL
SELECT 4, NULL
UNION ALL
SELECT 5, 'Idle'
UNION ALL
SELECT 6, 'Idle'
) AS a
WHERE
a.name NOT IN(NULL, 'Idle')
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
March 1, 2012 at 4:47 am
Because the ANSI_NULLS is on, if you set it off , it will show and work
I born to learn forever!
March 1, 2012 at 5:16 am
hamid_exe2007 (3/1/2012)
Because the ANSI_NULLS is on, if you set it off , it will show and work
Awesome 🙂
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
March 1, 2012 at 6:13 am
hamid_exe2007 (3/1/2012)
Because the ANSI_NULLS is on, if you set it off , it will show and work
If you set ANSI_NULLS off, your code will be incompatible with current standards and future editions of SQL Server: http://msdn.microsoft.com/en-us/library/ms188048.aspx
Edit: Might as well make the address a hyperlink.
March 1, 2012 at 6:22 am
Very easy remedy to this problem: ISNULL() function
select * from Statuses s
Where ISNULL(s.Stat,'NULL') not in ('NULL', 'Idle')
March 1, 2012 at 6:23 am
sknox (3/1/2012)
hamid_exe2007 (3/1/2012)
Because the ANSI_NULLS is on, if you set it off , it will show and workIf you set ANSI_NULLS off, your code will be incompatible with current standards and future editions of SQL Server: http://msdn.microsoft.com/en-us/library/ms188048.aspx
Edit: Might as well make the address a hyperlink.
As the artical say "Also, when you execute a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server will ignore the index values on computed columns or views and resolve the select operation as if there were no such indexes on the tables or views.
I guess this is the whole point... ON is the default standard, even if still the specific query needed the output, i guess we can turn it off and on in that batch to get the data, but not to set it to OFF as a default option.
SET ANSI_NULLS OFF
GO
SELECT ID, Name FROM
(
SELECT 1 AS ID, 'Active' AS name
UNION ALL
SELECT 2, 'Active'
UNION ALL
SELECT 3, 'InActive'
UNION ALL
SELECT 4, NULL
UNION ALL
SELECT 5, 'Idle'
UNION ALL
SELECT 6, 'Idle'
) AS a
WHERE
a.name NOT IN(NULL, 'Idle')
SET ANSI_NULLS ON
GO
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
March 1, 2012 at 6:28 am
hamid_exe2007 (3/1/2012)
Because the ANSI_NULLS is on, if you set it off , it will show and work
Except that it is deprecated and will not be honored in future versions of SQL Server.
March 1, 2012 at 6:31 am
omyrox (3/1/2012)
Very easy remedy to this problem: ISNULL() functionselect * from Statuses s
Where ISNULL(s.Stat,'NULL') not in ('NULL', 'Idle')
Very bad remedy.
#1: ISNULL (or any function for that matter) on the column will destroy all possibilities for an index seek.
#2 NULL is not the same as 'NULL'. You suggestion will go haywire in the real work. 😉
March 1, 2012 at 6:59 am
Agreed on #1
On #2: In the explanation they say that the problem is basically that NULL is not the same datatype as 'Idle', when I used ISNULL() and replaced NULL with string 'NULL' didn't it kind of cast NULL value as a string which is kind of the same as 'Idle'?:unsure:
March 1, 2012 at 7:05 am
omyrox (3/1/2012)
Agreed on #1On #2: In the explanation they say that the problem is basically that NULL is not the same datatype as 'Idle',
The explanation is wrong. NULL is not a data type. It's a property of the data. Specifically, it means there is no data. A logical test on nothing cannot be resolved as true or false, so returns UNKNOWN. Since NULL is in the list to test against, every row tested returns UNKNOWN, so doesn't satisfy the criteria of the WHERE clause.
when I used ISNULL() and replaced NULL with string 'NULL' didn't it kind of cast NULL value as a string which is kind of the same as 'Idle'?:unsure:
No, when you used ISNULL(), you inserted artificial data to test.
March 1, 2012 at 7:10 am
Raghavendra Mudugal (3/1/2012)
sknox (3/1/2012)
hamid_exe2007 (3/1/2012)
Because the ANSI_NULLS is on, if you set it off , it will show and workIf you set ANSI_NULLS off, your code will be incompatible with current standards and future editions of SQL Server: http://msdn.microsoft.com/en-us/library/ms188048.aspx
Edit: Might as well make the address a hyperlink.
As the artical say "Also, when you execute a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server will ignore the index values on computed columns or views and resolve the select operation as if there were no such indexes on the tables or views.
I guess this is the whole point... ON is the default standard, even if still the specific query needed the output, i guess we can turn it off and on in that batch to get the data, but not to set it to OFF as a default option.
We don't need to use the ANSI_NULLS option. We can simply rewrite the code using logic. Then it's effective, compliant, and future proof.
For example, since a NULL in the column will not be returned anyway, we don't need NULL in the list to test against (since we're using NOT IN). Now the list is a testable list, and will return the results we want. Try this code (I've changed Name in row 6 to 'Active' to show that the NULL in row 4 doesn't stop the query.)
SELECT ID, Name FROM
(
SELECT 1 AS ID, 'Active' AS name
UNION ALL
SELECT 2, 'Active'
UNION ALL
SELECT 3, 'InActive'
UNION ALL
SELECT 4, NULL
UNION ALL
SELECT 5, 'Idle'
UNION ALL
SELECT 6, 'Active'
) AS a
WHERE
a.name NOT IN('Idle')
Now if we needed to return the NULL, we'd use a WHERE clause like this:
WHERE
a.name NOT IN('Idle') or a.name IS NULL
More commonly, if the list is actually another table, which may contain NULLs:
WHERE
a.name NOT IN(SELECT name from UnwantedStatuses WHERE name IS NOT NULL)
March 1, 2012 at 7:18 am
I'm with Nils,
This is everything to do with NULL since IN/NOT IN is just translated into a pile of equivalence tests or not equivalence tests.
!= NULL
using the same query if you add
SET ANSI_NULLS off
You get a whole different result.
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply