Prev: Dynamic SQL
Next: CTE with IF statement
From: Dan Holmes on 7 Apr 2010 17:52
If table a LEFT JOINs to table b but the SQL only reads columns from a, doesn't the optimizer know that it doesn't need
to read from b at all?

SELECT a.*
FROM a
LEFT JOIN b ON a.col1 = b.col1
WHERE ...
From: Sylvain Lafontaine on 7 Apr 2010 18:13
You still need to read from B because you might have more than 0 or 1 join
for your foreign key.

However, if you add a unique index on b.col1 then yes, the optimizer should
drop the reading from table B.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"Dan Holmes" <dan.holmes(a)routematch.com> wrote in message
news:%234TNVyp1KHA.3868(a)TK2MSFTNGP06.phx.gbl...
> If table a LEFT JOINs to table b but the SQL only reads columns from a,
> doesn't the optimizer know that it doesn't need to read from b at all?
>
> SELECT a.*
> FROM a
> LEFT JOIN b ON a.col1 = b.col1
> WHERE ...


 | 
Pages: 1
Prev: Dynamic SQL
Next: CTE with IF statement