SQL Server not Performing Join Elimination

Question

Given the StackOverflow2010 database, I have created some foreign keys in order to explore join elimination:

Foreign Key 1 - All comments must have a post

Delete comments without a post

DELETE  c
FROM    Comments c
        LEFT JOIN Posts p
            ON c.PostId = p.Id
WHERE   p.Id IS NULL

create the foreign key

ALTER TABLE Comments 
    ADD CONSTRAINT fk_Comments_PostId 
    FOREIGN KEY (PostId) REFERENCES Posts(Id)

We can now run the query below and see join elimination:

SELECT  c.Id
FROM    Comments c
        JOIN Posts p
            ON p.Id = c.PostId

the plan is here and shows as I would expect, that only the comments table is read

Foreign Key 2 - All Posts must have an owning user

Contrary to the above, If I reset things and try with the following foreign key, I don't see join elimination:

Delete posts without an OwnerUser

DELETE  p
FROM    Posts p
        LEFT JOIN Users u
            ON p.Owneruserid = u.id
WHERE   u.Id IS NULL

Create the foreign key

ALTER TABLE Posts 
    ADD CONSTRAINT fk_Posts_UserId 
    FOREIGN KEY (OwnerUserId) REFERENCES Users(Id)

Run my query

SELECT  p.Id
FROM    Posts p
        JOIN Users u
            ON p.OwnerUserId = u.Id

the plan shows SQL Server accesses both tables.

Why does foreign key 2 not benefit from join elimination but foreign key 1 does?

Answer 1

The OwnerUserId column on Posts allows nulls. The PostId column on Comments does not.

Any nulls in OwnerUserId will result in the Posts row being eliminated by the join predicate, which rejects nulls:

SELECT
    P.Id
FROM dbo.P AS P
JOIN dbo.U AS U
    -- Rejects nulls
    ON U.Id = P.OwnerUserId;

Therefore, the join can affect the results and cannot be eliminated.

You need to either filter out the nulls explicitly (resulting in the incomplete set of Posts rows) or use an outer join to see table elimination:

SELECT
    P.Id
FROM dbo.Posts AS P
JOIN dbo.Users AS U
    -- Rejects nulls
    ON U.Id = P.OwnerUserId
WHERE
    -- Explicitly filter out null rows that couldn't join
    P.OwnerUserId IS NOT NULL;

-- All Posts rows, but an outer join
SELECT
    P.Id
FROM dbo.Posts AS P
LEFT JOIN dbo.Users AS U
    ON U.Id = P.OwnerUserId;

In principle, SQL Server could eliminate the join and add the IS NOT NULL predicate for you, but that isn't implemented. Join simplification is very limited in general. It doesn't work with multi-column foreign keys, for example.

Related questions

• View columns elimination
• Inner join elimination inhibited by prior outer join
• Single Predicate Check Constraint Gives Constant Scan but Two Predicate Constraint does not