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
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
Last Comment
Look closely at your SKU field in both tables for the 456 record. Do you have any spaces in that field?
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
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!
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.
Yes. That is actually what my link was - a link to comment https://www.experts-exchange.com/questions/27384185/MS-SQL-Where-Not-In-returns-some-records-that-ARE-IN.html#36926897 by JeridA.
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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