2

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?

JohnB
  • 1,743
  • 5
  • 21
  • 40

2 Answers2

4

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.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
3

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'
Jerad Rose
  • 15,235
  • 18
  • 82
  • 153
  • Is there a way to do this, when I don´t know the rst_month or rst_customer parameter before? E.g. I try to put the left join inside a view and thus can attach parameters only after the view has performed the left join and the parameters get indeed only applied in the where clause – Philipp Nov 14 '14 at 07:45
  • @Philipp the cleanest way would be to use a stored proc instead of a view. But I'm assuming that's not feasible (or at least, ideal), so with a few, the only way I can think of doing this would be with a nasty where clause on the view that takes into account potential missing data from the right table (i.e. `WHERE (RightTable.Id IS NULL OR RightTable.SomeFilter = 'MyValue'`). – Jerad Rose Nov 14 '14 at 15:00