Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


SAME Query Returns different results

Posted on 2011-09-29
Medium Priority
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 375 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
Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 375 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

719 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