CODEWITHSUNDEEP
sqlsql-servert-sql
JamesG
JamesG

Reputation: 79

TSQL - LEFT JOIN strange results

I am coming across an issue with the results of a seemingly basic query (on SQL Server 2017 CU17), and am hoping that people could suggest some things that I may not have checked or tried to get the correct results out.

The premise of the issue is that I am attempting to identify rows in one table, where an ID exists in another. This can usually be done with a LEFT JOIN, in this case, the query is as simple as follows:

SELECT  t1.id,
        t2.id
FROM Table1 AS t1
LEFT JOIN Table2 t2 ON t2.id = t1.id
WHERE t2.id IS NULL

This query should identify rows that exist in Table1 that do not exist in Table2, based on the 'id' columns, and is running against static data that isn't being manipulated in any way when I run the query.

LEFT JOIN query results image

I am getting a strange result from this, where rows are being returned where t1.id is returned, but t2.id is NULL - as if there are rows that exist in t1, but not t2. However, if I take one of the IDs returned from the first query, and manually check if it exists in both tables, it looks like the id does exist in both - even if I put that id into a query with an inner join such as follows:

SELECT  t1.id,
        t2.id
FROM Table1 AS t1
INNER JOIN Table2 t2 ON t2.id = t1.id
WHERE t1.id = 761179370

INNER JOIN query results for a given ID image

If I run the LEFT JOIN query a number of times, I get a different number of rows returned each time. Important to note that the id columns are both int datatypes, and the tables have the exact same collation.

What I have tried:

Any idea what the problem may be, or what I could check to figure out why I am getting these results - any guidance would be appreciated!

Upvotes: 7

Views: 719

Answers (3)

Rafa Barragan
Rafa Barragan

Reputation: 602

Sometimes if you have that issue you can paste the value on Notepad++ to validate that there is no other char or something similar added to the second one.

You can try the (NOLOCK) to avoid this kind of behavior, something like this:

SELECT
    *
FROM TABLE1 T1 (NOLOCK)
LEFT JOIN TABLE2 T2 (NOLOCK) -- LEFT JOIN IS THE SAME AS LEFT OUTER JOIN 
    ON T1.ID = T2.ID

Upvotes: 0

Erick de Vathaire
Erick de Vathaire

Reputation: 173

If you post the real query we may see a mistake there, check the query another time, some condition in the left join can change the "usual logic".

If still wrong, try this:

SELECT
t1.id,
t2.id
FROM Table1 AS t1
LEFT JOIN Table2 t2 ON t2.id = t1.id
WHERE t2.id IS NULL
option (recompile) --> This will ignore any cached data

Or create 2 temps to store some data and test there, database in production may change the results very quickly.

Upvotes: 1

Metanormal
Metanormal

Reputation: 27

I had a similiar problem a while ago. The reason was, that the SQL-Server Management Studio had cached some of the results. The famous off and on again did solve my problem. Your experiments pretty much say this wasnt the case, so ..

Here i would suggest comparing the results to the following:

SELECT  t1.id FROM Table1 AS t1
where t1.id not in (select id from table2)

If these results dont match with your query i would suggest a restart.

PS: Sorry for not using the comment, i have not enough reputation for that. :-\

Upvotes: 0

Related Questions