June 23, 2015 at 2:25 pm
I would like to query for two columns- either of the two returns null but not both.
For example:
If I use this
Select * from students
where requirementGradYear is null or ExpectedGradYear is null;
it will have records both are nulls and rows that either of them are nulls.
How can I exclude those both are nulls?
Thanks,
June 23, 2015 at 2:43 pm
for example
SELECT
A
, B
FROM Table
WHERE (A IS NULL)
AND (B IS NOT NULL)
OR (A IS NOT NULL)
AND (B IS NULL);
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 24, 2015 at 6:34 am
sqlfriends (6/23/2015)
I would like to query for two columns- either of the two returns null but not both.For example:
If I use this
Select * from students
where requirementGradYear is null or ExpectedGradYear is null;
it will have records both are nulls and rows that either of them are nulls.
How can I exclude those both are nulls?
Thanks,
Another alternative that might be more obvious as to it's objective:
SELECT *
FROM students
WHERE COALESCE(requirementGradYear, ExpectedGradYear) IS NOT NULL
AND (
requirementGradYear IS NULL
OR
ExpectedGradYear IS NULL
);
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 25, 2015 at 12:24 pm
I prefer this approach [Edit: Because of its clarity, and to avoid using functions in the WHERE clause, which should be avoided whenever (reasonably) possible]:
WHERE
(requirementGradYear IS NULL AND ExpectedGradYear IS NOT NULL) OR
(requirementGradYear IS NOT NULL AND ExpectedGradYear IS NULL)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 25, 2015 at 1:45 pm
ScottPletcher (6/25/2015)
I prefer this approach [Edit: Because of its clarity, and to avoid using functions in the WHERE clause, which should be avoided whenever (reasonably) possible]:
WHERE
(requirementGradYear IS NULL AND ExpectedGradYear IS NOT NULL) OR
(requirementGradYear IS NOT NULL AND ExpectedGradYear IS NULL)
Scott,
I could understand that if we were talking about using a user-defined function, but we all use DATEADD() or DATEDIFF() or GETDATE() in WHERE clauses all the time, and no one raises their hand and says "not a good idea" about it. With something like COALESCE or ISNULL, I wouldn't be all that worried about performance, but if you have a query that can demonstrate a performance hit on a sizable number of records, I'm all ears to learn about it... Keep in mind that I often create sparse indexes on fields where NULL values are common, with conditions to either detect NULL or NOT NULL, and they've come in darn handy.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 25, 2015 at 2:18 pm
sgmunson (6/25/2015)
ScottPletcher (6/25/2015)
I prefer this approach [Edit: Because of its clarity, and to avoid using functions in the WHERE clause, which should be avoided whenever (reasonably) possible]:
WHERE
(requirementGradYear IS NULL AND ExpectedGradYear IS NOT NULL) OR
(requirementGradYear IS NOT NULL AND ExpectedGradYear IS NULL)
Scott,
I could understand that if we were talking about using a user-defined function, but we all use DATEADD() or DATEDIFF() or GETDATE() in WHERE clauses all the time, and no one raises their hand and says "not a good idea" about it. With something like COALESCE or ISNULL, I wouldn't be all that worried about performance, but if you have a query that can demonstrate a performance hit on a sizable number of records, I'm all ears to learn about it... Keep in mind that I often create sparse indexes on fields where NULL values are common, with conditions to either detect NULL or NOT NULL, and they've come in darn handy.
Easy to demonstrate that ISNULL() harms performance when an index seek would work better. The easy rule is:
NEVER use ISNULL() in a WHERE or JOIN.
But I was a bit careless in my wording above: avoid functions against any table column. Functions on static values, like GETDATE() or @variables, are fine.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 26, 2015 at 3:28 am
I think this will help you...
SELECT *
FROM students
WHERE (requirementGradYear IS NULL AND ExpectedGradYear IS NOT NULL)
OR (requirementGradYear IS NOT NULL AND ExpectedGradYear IS NULL)
Keep Smiling,
Anuj 🙂
June 26, 2015 at 3:36 am
sgmunson (6/25/2015)
but we all use DATEADD() or DATEDIFF() or GETDATE() in WHERE clauses all the time, and no one raises their hand and says "not a good idea" about it.
When the functions are applied to a column, we certainly do say 'not a good idea' on a regular basis. It's not the presence of the function that's the problem, it's that a function on a column means SQL can't use an index seek for that predicate. Functions on parameters, constants, variables and stuff like GETDATE() are mostly fine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply