SQL Server wrongly using Inner Join instead of Left join.

Question

I am working on SQL Server version 2019 with compatibility mode = 150. There is a scenario where I am using two tables TableA & TableB. Where I have to pull records which are not present in TableB but present in TableA. So I have created a simulation script (please refer attached script Left_Join_Issue.txt) where I am able to reproduce the issue of left join. I used the below query to perform TableA left join TableB but here it is produced as an inner join result set. When I checked in the execution plan it was performing an inner join. But more curious on understanding this issue why it is not showing all the results from Left table i.e. TableA ? Could you please help me understand more on this ?

Answer 1

Hi @Vikrant Ashokrao More

It's because of the WHERE clause.

Left join sets all columns of TableB to NULL for those rows that don't exist in TableA. This implies that the comparison where b.userid = 1 is not true, that is why those rows are not being returned.

When you specify a column value from the right table in a WHERE clause, you necessarily eliminate all of the NULL values and it essentially becomes an INNER JOIN.

Try this: on a.mid=b.mid and b.userid=1

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Hi Vikrant Ashokrao More ,

Thank you for reaching out on the Microsoft Q&A .

The problem arises from the condition b.useid = 1 in the WHERE clause, which filters out rows where b.useid is NULL, effectively converting the left join into an inner join.

Solution:

To address this issue, you should move the filtering condition to the ON clause of the left join. By doing so, the join condition ensures that all rows from TableA are included, with NULL values for columns from TableB where there's no match or where b.useid is not 1. Here’s how you can update the query:

SELECT *
FROM dbo.TableA a
LEFT JOIN dbo.TableB b ON a.mid = b.mid AND b.useid = 1;

This adjustment maintains the left join behavior and should produce the desired result set. I hope this helps! If you have any further questions, feel free to ask.

If the information is useful, please accept the answer and upvote it to assist other community members.

Answer 3

SQL Server wrongly using Inner Join instead of Left join.

Not the engine works wrong, you query is it, by using the OUTER table in the WHERE condition with a fix filter value.

To get a real OUTER JOJ change it to

WHERE b.useid = 1 OR buseid IS NULL