CODEWITHSUNDEEP
sql-server
JohnB
JohnB

Reputation: 1793

SQL Server Rewriting Left Join

I was having a problem with a larger query in SQL Server which I traced back to this section of code which isn't performing as expected.

SELECT item_name,item_num,rst_units
FROM tbl_item left join tbl_sales_regional on item_num=rst_item
WHERE rst_month=7 and rst_customer='AB123'

The first table (tbl_item) has 10,000 records. The second table (tbl_sales_regional) has 83 for the shown criteria.

Instead of returning 10,000 records with 9,917 nulls, the execution plan shows SQL Server has rewritten as an inner join and consequently returns 83 results.

In order to achieve the intended result, I have to join off a subquery. Can someone provide an explanation why this doesn't work?

Upvotes: 2

Views: 733

Answers (2)

Quassnoi
Quassnoi

Reputation: 425593

Not sure which fields belong where, but you seem to have some fields from tbl_sales_regional in your WHERE condition.

Move them into the ON clause:

SELECT  item_name, item_num, rst_units
FROM    tbl_item
LEFT JOIN
        tbl_sales_regional
ON      rst_item = item_num
        AND rst_month = 7
        AND rst_customer = 'AB123'

The WHERE clause operates on the results of the join so these conditions cannot possibly hold true for any NULL records from tbl_sales_regional returned by the join, as NULL cannot be equal to anything.

That's why the optimizer transforms your query into the inner join.

Upvotes: 4

Jerad Rose
Jerad Rose

Reputation: 15513

Any conditions you have in your WHERE clause are applied regardless of the left join, effectively making it an inner join.

You need to change it to:

SELECT item_name,item_num,rst_units
FROM tbl_item left join tbl_sales_regional on item_num=rst_item
AND rst_month=7 and rst_customer='AB123'

Upvotes: 3

Related Questions