No actual columns selected from referenced table

06-JUL-2008, last improvement 08-AUG-2011

The purpose of the following query is to retrieve all OrderLines that have a corresponding Product.
One would normally not write such a query. However, it is not uncommon to have a view with such a join,
and to only select columns from one of the tables in the view. Such scenario comes down to the query below.

SQL Server 7.0

So let's see what SQL Server 7.0 did:

It looks up the Products row for each OrderLines row.

Obviously, there is no need to access table Products, because the foreign key constraint guarantees that
all product_ids will match, and that the join to Products will not cause duplication of rows, since there is
always exactly one matching row.

SQL Server 2000

SQL Server 2000 knows what to do with this query!

It only accesses the OrderLines tables, and does not needlessly look up rows in the Products table.

Unfortunately, this optimization only seems to take place for single column Primary Key/Foreign Key relations!

There is a similar scenario to the one above. It starts the same. A SELECT that only selects from OrderLines,
but joins to Products. The difference is, that in this case, column OrderLines.product_id is allowed to be NULL.