Left Join Problem

Question

I have two table:

CREATE TABLE [dbo].[TableLeft] ON [PRIMARY]
CREATE TABLE [dbo].[TableRight] ON [PRIMARY]
INSERT INTO TableLeft values (1, 'TableLeft_1')
INSERT INTO TableLeft values (2, 'TableLeft_2')
INSERT INTO TableLeft values (3, 'TableLeft_3')
INSERT INTO TableRight values (2, 'TableRight_1')
INSERT INTO TableRight values (3, 'TableRight_2')
INSERT INTO TableRight values (4, 'TableRight_3')

When run this query:

SELECT * From TableLeft l Left Join TableRight r on l.ID = r.ID

return Three rows:

1	TableLeft_1	NULL	NULL
2	TableLeft_2	2	TableRight_1
3	TableLeft_3	3	TableRight_2

and when I run following query for filter and return just first record:

SELECT * From TableLeft l Left Join TableRight r on l.ID = r.ID and r.ID is null

I expect than return first record, but result is this three record:

1	TableLeft_1	NULL	NULL
2	TableLeft_2	NULL	NULL
3	TableLeft_3	NULL	NULL

what is problem?

Answer 1

Hi Masoud, When analyzing your query I noticed, The problem in your scenario arises from where the filter condition is placed in your SQL query. When you perform a LEFT JOIN between TableLeft and TableRight and use the condition r.ID is null in the JOIN clause itself, it doesn't act as a post-join filter. Instead, it affects how the JOIN is made by only considering rows from TableRight that have a NULL ID, which is effectively none because ID cannot be NULL in your actual data. This results in all rows from TableLeft not matching any rows in TableRight, hence producing NULL values for all columns of TableRight.

The condition r.ID is null is meant to filter rows after the join has occurred, to find rows in TableLeft that do not have a corresponding row in TableRight. To achieve this, you should move the r.ID is null condition to the WHERE clause of your query, like this:

SELECT * FROM TableLeft l LEFT JOIN TableRight r ON l.ID = r.ID WHERE r.ID IS NULL

This query will correctly return only the rows from TableLeft that do not have a match in TableRight, which, based on your data, should be just the first record:

1 TableLeft_1 NULL NULL

By placing the filter in the WHERE clause, you correctly apply the filter after the LEFT JOIN has combined the rows, allowing you to filter for rows that did not find a match in TableRight.

Hopefully this will help, if you have any further questions please let me know.