February 21, 2012 at 8:49 am
I'm trying to teach myself SQL; I apologize in advance if this is an easy question. I'm getting stuck on the following query b/c it is not returning null records:
select istatus, dtmoveout, bmovedout, *
from tenant
where istatus in (1,4)
and dtmoveout + 7 < getdate ()
and bmovedout <> -1
If bmovedout is null, it is unknown (and therefore not true)?
SQL returns all of the desired records if I run two select statements together:
select istatus, dtmoveout, bmovedout, *
from tenant
where istatus in (1,4)
and dtmoveout + 7 < getdate ()
and bmovedout <> -1
(returns all records with bmovedout = 0)
select istatus, dtmoveout, bmovedout, *
from tenant
where istatus in (1,4)
and dtmoveout + 7 < getdate ()
and bmovedout is null
(returns all records where bmovedout is null)
Is there a way to run this as one select statement?
February 21, 2012 at 8:53 am
Just add
and bmovedout <> -1 or bmovedout is null
in your first SQL query
February 21, 2012 at 8:53 am
nscott 48570 (2/21/2012)
I'm trying to teach myself SQL; I apologize in advance if this is an easy question. I'm getting stuck on the following query b/c it is not returning null records:select istatus, dtmoveout, bmovedout, *
from tenant
where istatus in (1,4)
and dtmoveout + 7 < getdate ()
and bmovedout <> -1
If bmovedout is null, it is unknown (and therefore not true)?
SQL returns all of the desired records if I run two select statements together:
select istatus, dtmoveout, bmovedout, *
from tenant
where istatus in (1,4)
and dtmoveout + 7 < getdate ()
and bmovedout <> -1
(returns all records with bmovedout = 0)
select istatus, dtmoveout, bmovedout, *
from tenant
where istatus in (1,4)
and dtmoveout + 7 < getdate ()
and bmovedout is null
(returns all records where bmovedout is null)
Is there a way to run this as one select statement?
You want either one of two different conditions on bmovedout so you have to specify both in an OR structure, like...
select istatus, dtmoveout, bmovedout, *
from tenant
where istatus in (1,4)
and dtmoveout + 7 < getdate ()
and (bmovedout <> -1 OR
bmovedout is NULL)
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 21, 2012 at 8:55 am
That worked BEAUTIFULLY, thank you!
February 21, 2012 at 9:00 am
select istatus, dtmoveout, bmovedout, *
from tenant
where istatus in (1,4)
and dtmoveout + 7 < getdate ()
and isnull(bmovedout,0) <> -1
Try like this.
February 21, 2012 at 10:15 am
Actually, you could simply flip the comparison around a bit and do it without the OR or the ISNULL.
(Code removed after double-checking).
Edit: hmm - foiled again. The result is still unknown, so it still fails the WHERe. Sorry - bad info. the OR is going to be necessary.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 22, 2012 at 6:17 am
nscott 48570 (2/21/2012)
That worked BEAUTIFULLY, thank you!
Glad to help.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 23, 2012 at 3:31 am
I believe that most of the articles I've read on performance suggest you do it like this:
select istatus, dtmoveout, bmovedout, *
from tenant
where istatus in (1,4)
and dtmoveout < DATEADD(day,-7, getdate ())
and ISNULL(bmovedout, 0) <> -1
Someone with more experience can correct me if I'm wrong, but if there's an index on dtmoveout, it can't be used with the derived field that is created by adding 7 to it.
Edited to replace the OR with ISNULL.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 23, 2012 at 5:45 am
dwain.c (2/23/2012)
I believe that most of the articles I've read on performance suggest you do it like this:
select istatus, dtmoveout, bmovedout, *from tenant
where istatus in (1,4)
and dtmoveout < DATEADD(day,-7, getdate ())
and ISNULL(bmovedout, 0) <> -1
Someone with more experience can correct me if I'm wrong, but if there's an index on dtmoveout, it can't be used with the derived field that is created by adding 7 to it.
Edited to replace the OR with ISNULL.
I may be wrong and some particular data distribution may prove me wrong but I think this query will be doing a full table scan no matter what. In many cases, when there is a non sargable predicate only a covered index can lure optimizer not to do a table scan but in this case it makes no sense to go for it because query is retrieving all the rows from target table... and three more.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 23, 2012 at 9:59 am
nscott 48570 (2/21/2012)If bmovedout is null, it is unknown (and therefore not true)?
The key point to help you understand what's going on is to know what NULL actually represents. It is unknown, but it also isn't "equal to" or "unequal to" anything else, not even itself.
For example, you can't say somefield = NULL or somefield <> NULL
It would seem logical to think that somefield <> -1 should include NULL values (after all they don't equal '-1', right?), but SQL doesn't work that way. If you want to include NULL values you have to use the ISNULL function the others used, or somefield IS NULL (or somefield IS NOT NULL, depending on what you're wanting to accomplish).
Research ISNULL, COALESCE and NULL to help round out your understanding.
February 23, 2012 at 11:47 am
ACinKC (2/23/2012)
nscott 48570 (2/21/2012)If bmovedout is null, it is unknown (and therefore not true)?
The key point to help you understand what's going on is to know what NULL actually represents. It is unknown, but it also isn't "equal to" or "unequal to" anything else, not even itself.
For example, you can't say
somefield = NULLorsomefield <> NULLIt would seem logical to think that
somefield <> -1should include NULL values (after all they don't equal '-1', right?), but SQL doesn't work that way. If you want to include NULL values you have to use the ISNULL function the others used, orsomefield IS NULL(orsomefield IS NOT NULL, depending on what you're wanting to accomplish).Research ISNULL, COALESCE and NULL to help round out your understanding.
Not entirely true. NULL searchability depends on the SET ANSI_NULLS options for that batch.
Run the following and check for yourself
IF OBJECT_ID('TempDB..#NullTest') IS NOT NULL
DROP TABLE #NullTest
CREATE TABLE #NullTest
( ID INT IDENTITY(1,1) , FullName VARCHAR(30))
INSERT INTO #NullTest (FullName)
SELECT 'Metallica'
UNION ALL SELECT 'Radiohead'
UNION ALL SELECT 'Linkin Park'
UNION ALL SELECT NULL
SET ANSI_NULLS ON
SELECT *
FROM #NullTest
WHERE FullName IS NULL
SELECT *
FROM #NullTest
WHERE FullName = NULL
SELECT *
FROM #NullTest
WHERE FullName IS NOT NULL
SELECT *
FROM #NullTest
WHERE FullName <> NULL
SET ANSI_NULLS OFF
SELECT *
FROM #NullTest
WHERE FullName IS NULL
SELECT *
FROM #NullTest
WHERE FullName = NULL
SELECT *
FROM #NullTest
WHERE FullName IS NOT NULL
SELECT *
FROM #NullTest
WHERE FullName <> NULL
February 28, 2012 at 6:18 am
bmovedout IS NULL
is a better option than:
ISNULL(bmovedout, 0) <> -1
if there are a index for the [bmovedout] column.
Since the second option forces the query to evaluate the ISNULL() function for each row to properly evaluate the inequality.
So even with a index in the column a full table scan ill be performed to evaluate the result from the function for each row.
February 28, 2012 at 7:33 am
Hi,
That sounds cool, i'm too like a small learning fish here.
hope your doubt is cleared by our experts here..!! and i want to share you something.
in your query (bmovedout <> -1) it tells that "accept TRUE" means that it simply rejects both FALSE and UNKNOWN(NULL),this query will give you the result which was present the value different that -1 only. you can do it in another way NOT(bmovedout = -1). you can't expect NULL values using this query.
answer you already got from '@PaulB-TheOneAndOnly'
Thanks.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply