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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

LVL 60

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 60

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

752 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