Link to home
Start Free TrialLog in
Avatar of websuperman
websuperman

asked on

MS SQL Where Not In returns some records that ARE IN

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
Avatar of pdoelle
pdoelle
Flag of Canada image
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
Avatar of JeridA
JeridA
Look closely at your SKU field in both tables for the 456 record.  Do you have any spaces in that field?
Avatar of websuperman
websuperman

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of websuperman
websuperman

ASKER

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.
Avatar of websuperman
websuperman

ASKER

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.
Avatar of websuperman
websuperman

ASKER

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!
Avatar of JeridA
JeridA
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.