Got it wrong (yes, I didn't run the code) 😀

Excellent question which shows how the query optimizer works.

A similar example is how the INNER JOIN is removed (resulting in only a scan on table "child") in the following example, due to the fact that parent_id cannot be NULL and has a FK reference to table "parent".

create table parent(id int primary key)

create table child(id int primary key, parent_id int not null references parent(id))

select c.*

from dbo.child c

inner join parent p on p.id = c.parent_id