Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1213141516»»

Four Rules for NULLs Expand / Collapse
Author
Message
Posted Friday, April 4, 2008 1:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 8:26 AM
Points: 32, Visits: 186
Mike,

Not BIG on DBA, but the only question I have with your code:

SELECT ...
FROM ...
WHERE COALESCE(DateTo, Today) >= Today

is, if DateTo is an indexed column, wouldn't wrapping it in COALESCE() preclude using the index for retrieving values, whereas Kenneth's suggestion would use indexes if present?
Post #480245
Posted Friday, April 4, 2008 2:44 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
dbishop (4/4/2008)
Mike,

Not BIG on DBA, but the only question I have with your code:

SELECT ...
FROM ...
WHERE COALESCE(DateTo, Today) >= Today

is, if DateTo is an indexed column, wouldn't wrapping it in COALESCE() preclude using the index for retrieving values, whereas Kenneth's suggestion would use indexes if present?


Good point. For a large data set using COALESCE in the WHERE clause could cause an unacceptable performance impact. For the example the OP gave (employees hired and fired), I was assuming a small number of rows (which could be a bad assumption since very large organizations may have burned through a lot of employees - e.g., McDonald's). SQL Server won't necessarily ignore an index because you use COALESCE in the WHERE clause, but could generate an index scan instead of a seek. A lot of other factors play into whether SQL Server decides to use an index or not: e.g., are there any bookmark lookups (RID lookups) or is the index covering, etc. You could also UNION ALL two queries together, which probably sits somewhere in the middle performance-wise, but much lower on the readability scale.

SELECT ...
FROM ...
WHERE DateTo >= Today

UNION ALL

SELECT ...
FROM ...
WHERE DateTo IS NULL;

Post #480286
Posted Tuesday, June 23, 2009 4:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:04 AM
Points: 176, Visits: 567
This query will work regardless of ANSI settings:

select rep
from salesteam a
where rep NOT in (
select fullname from manpower
where fullname = a.rep )
group by rep

The secret ingredient is how the inner select works - it throws out NULL values leaving the outer "NOT" filter to work as expected. The "group by" is optional.

In a different SQL website, they were wrangling over the nulls too, but no answer to the original sql query.
Post #740038
Posted Tuesday, June 23, 2009 7:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:55 AM
Points: 201, Visits: 404
hmmm... the solution provided by "bear in a box" looks very Oracle-ish. I believe MS & Oracle spend HOURS trying to figure out how to make their competitors code run like CRAP! This may be a sample of that.

I tried to figure out where the thread was going with the original code or a subsequent post but I got lost...

It looks like an OR clause would work just fine...

SELECT  [column list]
FROM dbo.SomeTable
WHERE DateTo >= @today
OR DateTo IS NULL

I know from the original post that it was about NULLS and some of the samples were a bit weird. For Example: NULL should never equal NULL since unknown cannot equal unknown. Also, concatenation behavior is based on the CONCAT_NULL_YIELDS_NULL setting. If it's ON 'A' + NULL yields NULL, if it's OFF then 'A' + NULL yields 'A'.


--Paul Hunter
Post #740693
Posted Tuesday, June 23, 2009 10:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:04 AM
Points: 176, Visits: 567
Please qualify how it can be bad when it works fine without having to do convoluted steps cursor tables, etc.
Post #740734
Posted Wednesday, June 24, 2009 8:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:55 AM
Points: 201, Visits: 404
Maybe I went blind last night while typing in the code..... nope, no blindness. Bear, where did you see the cursor? I looked under every letter and couldn't find it.

--Paul Hunter
Post #741495
Posted Wednesday, June 24, 2009 9:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:04 AM
Points: 176, Visits: 567
...it must be somewhere there within the 15 pages of posts.

the answer i was expecting from you would have been, "because the filter will take a hit with a large table scan." i modified the code to be "where [field] is not null", the query runs faster; but this assumes one knows very field in the table has been allowed or not allowed nulls. this is not the case which is why the issue comes up during actual.

some of the helpful posts generalize to having the "where" part remove the nulls. again, it assumes one knows...etc.

i was trying to find an invariant way where i don't have to set on then after, set off. nor use functions or make "is not null" part of every query. the query originates from a more difficult query where i needed two to three values to filter out one transaction among many.
Post #741517
Posted Wednesday, June 24, 2009 10:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 23,299, Visits: 32,046
bear in a box (6/24/2009)
...it must be somewhere there within the 15 pages of posts.

the answer i was expecting from you would have been, "because the filter will take a hit with a large table scan." i modified the code to be "where [field] is not null", the query runs faster; but this assumes one knows very field in the table has been allowed or not allowed nulls. this is not the case which is why the issue comes up during actual.

some of the helpful posts generalize to having the "where" part remove the nulls. again, it assumes one knows...etc.

i was trying to find an invariant way where i don't have to set on then after, set off. nor use functions or make "is not null" part of every query. the query originates from a more difficult query where i needed two to three values to filter out one transaction among many.


How would you NOT know if a column in a table allowed nulls or not? You can look at the table definition to see if it does, or you can query the table with specific queries to determine if a column you are selecting contains any nulls or not.

The best bet, of course, is through documentation that tells you what you need to know.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #741543
Posted Thursday, January 7, 2010 2:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 27, 2012 8:55 AM
Points: 1, Visits: 38
Hello and happy new year everyone.

I've looked in the thread and not managed to see an answer to this. I use SQL server.

If I have a Customers table, with a nullable field Country, and want to find all Customers except ones in Russia:

SELECT * FROM CUSTOMERS WHERE NOT Country = 'Russia'

will not show me customers with a null country. And that seems rotten to me, the query is simple and I would expect to see all except customers with the country specified Russia.

I could use ansi_nulls off, or have to do

SELECT * FROM CUSTOMERS WHERE NOT ISNULL(Country, '') = 'Russia'

both of these seem ugly, as if you must walk on eggshells if a null may be present. Is there a better way?

All the best,

Greg

Post #843895
Posted Thursday, January 7, 2010 3:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 23,299, Visits: 32,046
Greg Martin-419640 (1/7/2010)
Hello and happy new year everyone.

I've looked in the thread and not managed to see an answer to this. I use SQL server.

If I have a Customers table, with a nullable field Country, and want to find all Customers except ones in Russia:

SELECT * FROM CUSTOMERS WHERE NOT Country = 'Russia'

will not show me customers with a null country. And that seems rotten to me, the query is simple and I would expect to see all except customers with the country specified Russia.

I could use ansi_nulls off, or have to do

SELECT * FROM CUSTOMERS WHERE NOT ISNULL(Country, '') = 'Russia'

both of these seem ugly, as if you must walk on eggshells if a null may be present. Is there a better way?

All the best,

Greg



Or this
SELECT * FROM CUSTOMERS WHERE NOT Country = 'Russia' OR Country IS NULL




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #843952
« Prev Topic | Next Topic »

Add to briefcase «««1213141516»»

Permissions Expand / Collapse