[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS SQL Where Not In returns some records that ARE IN

Posted on 2011-10-06
9
Medium Priority
?
327 Views
Last Modified: 2012-08-14
I have two tables with a field that the two may have in common.  I want the records from one table where that field does not exist in the other table.  It works well except that it returns some records that are in fact in the other table.  I have checked for NULLs, leading and trailing spaces, and can find no reason why this is happening.  When I find a record that should not be returned but is I can query the other tables for that record and both will return the matching record.

FauxTable1:
SKU | Description | Cost
123 | Pink Bike | 20
456 | Blue Bike |25
ABC | Drumset | 50
DEF | Horn | 35

FauxTable2
SKU | Description | Cost
123 | Pink Bike | 20
456 | Blue Bike |25
GHI | Snake Skin | 15
JKL | Pajamas | 7

SELECT *
From FauxTable1
whereSKU NOT IN (Select SKU from FauxTable2 )

Expected Result:
SKU | Description | Cost
ABC | Drumset | 50
DEF | Horn | 35

Example Messed up Result:
SKU | Description | Cost
456 | Blue Bike |25
ABC | Drumset | 50
DEF | Horn | 35

I have simplified the number of columns etc for the example.  I am trying to figure out what may cause this query to return results that are IN both tables along with results that are NOT IN but it doesn't return all results that are IN
0
Comment
Question by:websuperman
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 7

Expert Comment

by:pdoelle
ID: 36926877
I have used three different approaches to the "does not exist another table" question:
1) NOT IN
2) NOT EXISTS
3) LEFT JOIN + IS NULL

The choice can depend on the cardinality of your tables, and how they indexed. Based on your simplified example, I might use the third option, since it does not require a subquery, as follows:

SELECT t1.*
FROM FauxTable1 t1 LEFT JOIN FauxTable2 t2 ON t1.sku = t2.sku
WHERE t2.sku IS NULL
0
 
LVL 4

Expert Comment

by:JeridA
ID: 36926897
Look closely at your SKU field in both tables for the 456 record.  Do you have any spaces in that field?
0
 
LVL 2

Author Comment

by:websuperman
ID: 36926929
I have attempted the query using NOT IN, NOT EXISTS and LEFT JOIN NULL and all still return some results that exist in both tables.  I can copy the data from the field and query the two tables I am working on and they will both return the result, each only exists once.  I just can't figure out why, if i can query each table where sku={result that should not be returned with NOT IN} and it shows up how that is happening.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 36927031
I would double check as advised here - http:#36926897

Depending on what the data types are, you may be getting false non-matches.
i.e., try:
SELECT *
FROM FauxTable1
WHERE LTRIM(RTRIM(SKU)) NOT IN (SELECT LTRIM(RTRIM(SKU)) FROM FauxTable2)

If that works, try to fix the underlying data, so that you do not have to wrap columns in a function that may hurt performance.

Additionally, I just helped someone where FauxTable2 in your example was say a staging table for data imported through Excel or another data source. Since carriage return (\r or CHAR(13)) and line feed (\n or CHAR(10)) are whitespace, but not spaces and therefore not eliminated automatically by LTRIM() or RTRIM(), you may have to replace those characters ... what test is to see what the DATALENGTH() of your column is. If you see a 3 character value return a length other than 3, given you are not using double byte characters, then you know there are extraneous characters not visible to you.
0
 
LVL 2

Author Comment

by:websuperman
ID: 36927158
While these tables are being created from csv's or xls the LTRIM and RTRIM seems to make the difference.  I am concerned it there are parts NOT IN that aren't being displayed now, but I'll have to dig to figure that out.  I tried to click on the provided link (mwvisa1) and it wouldn't let me go to it, even copying shortcut.  I will try to search the site for that number to see if that works.  For now that at least has gotten me to a new place. with results down from 862 to 47.
0
 
LVL 2

Author Comment

by:websuperman
ID: 36927237
I ran the test as suggested using DATALENGTH and the resulting length matches the number of visible characters in both tables, even on parts I know were showing up in the NOT IN query results that shouldn't have been.
0
 
LVL 2

Author Closing Comment

by:websuperman
ID: 36927243
Even though there doesn't appear to be evidence of spaces or characters in the field, the TRIM seems to have done the trick.  Thanks!
0
 
LVL 4

Expert Comment

by:JeridA
ID: 36927244
Sounds like the issue is what I was saying, you have spaces in the fields.  You need to look at your ANSI Padding settings, it could be that SQL Server is adding spaces to your data when you import it.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36928117
Yes. That is actually what my link was - a link to comment http://www.experts-exchange.com/Q_27384185.html#36926897 by JeridA.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

830 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