TSQL -- how does optimizer deal with joins with unused tables
SELECT table1.col1, table2.col1, table2.col2 -- etc.
FROM dbo.table1
LEFT JOIN dbo.table2
on (table1.id = table2.id)
LEFT JOIN dbo.table3
on (table1.id = table3.id)
In the simple example above it is obvious that table 3 isn't needed, however more intricate cases can exist.
Question: Is the TSQL query optimizer able to determine that the JOIN with table 3 isn't needed? If so, would it potentially miss other optimizations for more complex queries, if table3 wasn't manually removed from the query?
I use SSMS 14.017 with an underlying SQL database of select @@version = Microsoft SQL Server 2014 (SP3)
Yes, query optimizer could skip table3 because it is not referenced in SELECT list and the join condition is LEFT OUTER JOIN so there is no filtering.
Related: 10 Cool SQL Optimisations That do not Depend on the Cost Model by Lukas Eder:
JOIN Elimination: An Essential Optimiser Feature for Advanced SQL Usage