SQL Server 2016是否忽略未使用列的Left Join？求验证及建议

Great catch! This behavior is absolutely legitimate—it's called Redundant Join Elimination, a core optimization from SQL Server's query optimizer. Let me break down what's happening and share some actionable advice:

Why the Left Join Gets Ignored #

When you add a LEFT JOIN but don't reference any columns from the joined table in your SELECT clause, WHERE clause, or other query components (like GROUP BY/HAVING), the optimizer determines this table contributes nothing to the final result set. Since a LEFT JOIN without referenced columns doesn't filter rows (unlike an INNER JOIN) and adds no data to the output, the optimizer safely removes this join entirely to cut down execution overhead.

Performance Difference Explained #

• When you include columns from the joined table in SELECT, the optimizer has to:
  • Read data from the joined table
  • Execute the join operation (matching rows between your base table and the joined table)
  • Include those columns in the output
    This extra work is why your query takes 9 seconds.
• When you exclude those columns, the optimizer skips all that work—only scanning your base table, hence the 1-second runtime.

Verification Example #

You can test this with a simple query pair:

-- This query will eliminate the LEFT JOIN (no reference to Orders columns)
SELECT a.Id, a.CustomerName
FROM Customers a
LEFT JOIN Orders b ON a.Id = b.CustomerId;

-- This query will retain the LEFT JOIN (includes Orders column)
SELECT a.Id, a.CustomerName, b.OrderDate
FROM Customers a
LEFT JOIN Orders b ON a.Id = b.CustomerId;

Check the actual execution plan for both: the first will show no trace of the Orders table, while the second will include the join operator and table scan/seek for Orders.

Practical Advice #

1. Embrace the optimization if it's intentional: If you accidentally added a redundant LEFT JOIN, there's no performance penalty—this is the optimizer doing its job. No need to worry about it.
2. Force the join if you need it: If you have a reason to keep the join (e.g., triggering a table-level trigger on the joined table, or planning to add columns later), you can trick the optimizer into retaining it:
   • Add a dummy reference to the joined table, like SELECT ..., b.Id AS DummyColumn (ignore this column in your application)
   • Add a non-filtering condition that references the table, e.g., WHERE (b.Id IS NULL OR b.Id IS NOT NULL)
3. Clean up your queries: While redundant joins don't hurt performance, they make your SQL harder to read and maintain. Get in the habit of removing joins you don't need.

内容的提问来源于stack exchange，提问作者Dinesh