If I submit a query to SQL Server 2005 which contains a number of LEFT JOIN clauses where the table joined to is then never referenced, will the joins still happen or is SQL Server intelligent enough to discard them?
Obviously it wouldn't be able to discard INNER JOINs [false assumption! see answers] as that would potentially change the result, but can it do it for LEFT JOINs?
A left join could potentially multiply your result set if there are many matches.
So therefore it would still be evaluated.
It can discard both INNER and LEFT JOINs by inspecting the constraints. If you don't use a column in the table and there is a guaranteed single row existence by (FK) constraint, then the table does not need to be used. Obviously it depends on the query, it's possible that the table still gets used because it's the best plan.
This is yet another reason that constraints are tremendously useful and should be considered more frequently than they often are in designs.