Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have this fairly straight-forward snippet of a query:

DECLARE @ClientGUID numeric(16) = 9000011916900200;

  ch.GUID, c.LastName, c.FirstName, mrn.ClientIDCode AS MRN
FROM Client c
  ON mrn.ClientGUID = c.GUID AND mrn.TypeCode = 'MRN1'
INNER JOIN Chart ch ON ch.ClientGUID = c.GUID

This throws a "No Join Predicate" warning on the join between Chart and Client because the optimizer pushes the variable into the index seek predicate leaving nothing to join on and essentially doing a CROSS JOIN after the index seek. I get that this is a good thing.

My question is, if in six months someone sees that warning and starts freaking out, how can i tell the optimizer that this okay (intended behavior) and not to throw the warning?

I've tried changing the INNER JOIN to a LEFT JOIN (which I saw on another question about this warning), CROSS APPLY, and an explicit CROSS JOIN (and adding the condition in the WHERE clause) -- none of them get rid of the blasted warning.

I see this issue on both @@VERSION:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1765.0 (X64) Enterprise Edition (64-bit)
Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) Enterprise Edition: Core-based Licensing (64-bit)


share|improve this question
How about adding AND ch.ClientGUID = @ClientGUID to the WHERE clause? Sometimes you can be redundant to give SQL Server as much information as you can... – Aaron Bertrand Mar 31 '14 at 18:21
@AaronBertrand: nope. which makes sense, since that's what the optimizer is doing on its own. – JoeNahmias Mar 31 '14 at 18:23
Well, I've seen cases where adding redundant info does help the optimizer. How about adding both conditions to the ON clause instead of the WHERE, or in addition to? I don't really like that, but it's worth a try. Also, how about embedding a comment in the query text that explains there is a silly warning that should be ignored, even if you can't figure out a way to prevent the warning? – Aaron Bertrand Mar 31 '14 at 18:24
@AaronBertrand: no help in moving the conditions to the ON clause. Also, SQL Server 2012 throws the same warning. What's puzzling is that explicitly doing it as a CROSS JOIN doesn't quiet the warning -- but I guess that's the optimizer re-write for you... – JoeNahmias Mar 31 '14 at 18:28
Do these GUIDs have unique constraints? – ypercubeᵀᴹ Mar 31 '14 at 18:41

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.