Why is a join with a table variable causing a missing join column on the nested loop?

kudz 2016-03-10 14:24:42

Please ignore the 2 first queries and look at the last one(2.9% cost). The other ones are sorted out already.

Replacing the table variable by a temp table removes the missing join predicate.. Why ? All the tables are being joined correctly, why does a join with a table variable cause a missing join predicate ?

Thank you

Dan Holmes 2016-03-21 18:11:09
This is hard with only an anonymized plan.
SQLkiwi 2016-03-23 11:30:29
You be seeing this as the result of an implied predicate.

Generally, if you have written your joins correctly (no missing alias accidents, for example), this is nothing to worry about. It simply means the optimizer found a plan where a join predicate was redundant, or where translating the inner join to a cross product produced a plan with a lower expected cost.

Much more detail would be needed to provide an exact answer.

An example:

CREATE TABLE dbo.T1 (A integer NULL, B integer NULL);
CREATE TABLE dbo.T2 (A integer NULL, B integer NULL);
 
INSERT dbo.T1 
    (A, B) 
VALUES 
    (1, 1), 
    (1, 1), 
    (1, 1), 
    (2, 2),
    (2, 2),
    (2, 2);
 
INSERT dbo.T2 
    (A, B) 
VALUES 
    (1, 1), 
    (1, 1), 
    (1, 1), 
    (2, 2),
    (2, 2),
    (2, 2);
 
DECLARE @A integer = 1;
 
SELECT *
FROM dbo.T1 AS T1 
JOIN dbo.T2 AS T2 
    ON T2.A = T1.A
WHERE 
    T1.A = @A;
 
DROP TABLE 
    dbo.T1, 
    dbo.T2;

Execution plan:

alt text