SAME Query Returns different results

Posted on 2011-09-29
Last Modified: 2012-05-12
I have the query below, and it has prompted the question today.

select *
from DB..TableA  
where (Discontinued = 0 or Discontinued = null)
and (Accessory = 0 or Accessory is null)
and (ltrim(Rtrim(PartNumber)) not in (Select distinct ltrim(Rtrim(PartNumber)) from DB..TableB))
and (ltrim(Rtrim(PartNumber)) not in (select Distinct ltrim(Rtrim(PartNumber)) from DB..TableC))

The first 5 times I ran the query zero records were returned, which I know was incorrect, on the 6th time running the query, WITHOUT modifying it or any data with in the databases, I received over 600 results which is more along the lines of what I am expecting.

I have seen this once before but marked it up as a PC issue as I was having a lot of issues with the PC that day.  

What would cause this behavior when the query is not returning what it should and then with out any changes it starts returning the correct records?

Question by:cpence
LVL 18

Accepted Solution

lludden earned 125 total points
ID: 36817647
One problem:
where (Discontinued = 0 or Discontinued = null) is a problem.  It should be IS NULL

It might have timed out - having a calculation in your WHERE clause like that forces a table scan (ltrim(Rtrim(PartNumber)) isn't indexed, so it will scan every row in the table.


Expert Comment

ID: 36818807
If the query does take a while to run, then I would agree with lludden above. The query is timing out. You could be having communication issues with the database from the tool you are using or just from your PC. Do you have any problems running the query on anyone else's PC?

If the issue is the query timing out, then you may need to alter the query's where clause. Depending on the size of the tables in my Sub queries, I often use the EXISTS statement.

select *
from DB..TableA A
where (Discontinued = 0 or Discontinued = null)
and (Accessory = 0 or Accessory is null)
and not exists(select * from DB..TableB B where ltrim(Rtrim(B.PartNumber)) = ltrim(Rtrim(A.PartNumber)) 
and not exists(select * from DB..TableC C where ltrim(Rtrim(B.PartNumber)) = ltrim(Rtrim(A.PartNumber))

Open in new window


Author Comment

ID: 36893393
it is occuring on multiple PCs running SSMS
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 125 total points
ID: 36896953
Please review lludden's comment. NULLs are filtered automatically if you do a direct comparison on a field like (Discontinued = 0); however, to fix this one can NOT do direct comparisons to NULL. It has to be (Discontinued IS NULL) as pointed out by lludden. The reason the query fails is (Discontinued = NULL) will always be a false statement and so no results meet the criteria. If this works on occasion, either that scenario does not involve rows with NULL in Discontinued field OR you are getting lucky that the query picks up the (Discontinued = 0) and skips processing of the =NULL bit. This is not guaranteed and erratic as you have seen and should be avoided.

You query should be:
select * /* replace with list of columns you want. */
from DB..TableA  
where (Discontinued = 0 or Discontinued is null)
and (Accessory = 0 or Accessory is null)
and (ltrim(Rtrim(PartNumber)) not in (Select distinct ltrim(Rtrim(PartNumber)) from DB..TableB))
and (ltrim(Rtrim(PartNumber)) not in (select Distinct ltrim(Rtrim(PartNumber)) from DB..TableC))

Also as pointed out, the highlighted statement might be causing some inefficiency. Try fixing the original data if you can to eliminate the need to LTRIM() and RTRIM() each time you reference that field.   Good luck!

Author Closing Comment

ID: 36905199
Thanks guys,
LVL 59

Expert Comment

by:Kevin Cross
ID: 36905220
Why the "B"? What else is missing from our explanation if you do not mind my asking?
Not critical, I am just curious. Anyway, glad we could help.

Best regards and happy coding,


Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now