1

I am totally bewildered by this one... Execution plan says No Join Predicate but if I hard code the ID for the where clause instead of using a variable, it's fine...

DECLARE @TableOneID int = 1234

    SELECT
         TableOne.JustAField, TableTwo.AnotherField
    FROM
        TableOne
    INNER JOIN 
        TableTwo on TableTwo.TableOneID = TableOne.TableOneID
    WHERE
        TableOne.TableOneID = @TableOneID

If I replace the variable in the where clause with 1234, the query plan is fine... what am I missing?

MS SQL Server 2019 (not sure what compatibility mode)

msimmons
  • 146
  • 1
  • 3
  • 15

1 Answers1

3

There is no problem here - you just need to check that the semantics of the query are definitely what you intended.

As you have

  • TableOne.TableOneID = @TableOneID
  • TableTwo.TableOneID = TableOne.TableOneID

It is valid for SQL Server to see the implied predicate of

  • TableTwo.TableOneID = @TableOneID

and just treat the query as a whole as (pseudo code)

FROM
    (TableOne WHERE TableOne.TableOneID = @TableOneID) 
CROSS JOIN 
    (TableTwo WHERE TableTwo.TableOneID = @TableOneID)

Hence the "No Join Predicate" warning.

This isn't detrimental to anything.

There is no additional work caused by the elimination of the join predicate vs retaining it. It still needs to look up and join the same rows either way. It doesn't matter to this that the predicate is referencing the variable rather than a column from the joined table. Either way it will end up joining every row where TableOneID = 1234 from one side with every such row from the other side.

This is discussed more in Implied Predicates and Query Hints

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • that doesn't resolve it ``` SELECT cj_to.fieldone, cj_tt.fieldtwo FROM (SELECT * from tableOne to WHERE to.TableOneId = @TableOneId) cj_to CROSS JOIN (SELECT * FROM TableTwo tt WHERE tt.TableOneId = @TableOneId) cj_tt ``` – msimmons Jun 24 '23 at 19:46
  • What doesn't resolve it? I didn't propose any resolution because there is nothing to resolve. You just need to understand the reason for it and stop worrying about it. If the predicate was referencing the other table's column instead of the variable the warning would go away but this is still going to resolve to the exact same value – Martin Smith Jun 24 '23 at 19:47
  • sorry, i meant, when I make that change, i still have the No Join Predicate in the execution plan – msimmons Jun 24 '23 at 19:49
  • Yes of course you do because this is just a more explicit statement of the `CROSS JOIN` that you are getting anyway – Martin Smith Jun 24 '23 at 19:50
  • If the highlighted predicate in the plan was referencing the column from the other table instead of the variable you wouldn't see this warning but it still ends up looking up the exact same value https://i.stack.imgur.com/cgZ2I.png - there is no benefit from that extra layer of obfuscation being there – Martin Smith Jun 24 '23 at 19:52
  • I don't follow. The only place the variable is being used it in the where clause. – msimmons Jun 24 '23 at 20:30
  • If you don't follow either re-read the answer or look at the link. I am not going to go through endless iterations of re-explaining it in different words. It is quite simple. The implied predicate means that the query is able to be transformed from an inner join with a join predicate to a cross join with the predicate pushed down. This is not detrimental in this case so you can ignore the warning as the cross join is just a result of a valid and unharmful transformation – Martin Smith Jun 24 '23 at 21:43