May 18, 2012 at 6:18 am
SQL Kiwi (5/18/2012)
john.p.lantz (5/17/2012)
I've got a simple SQL...select * from table1 a
join table2 bon a.id_rssd = b.id_rssd
left outer join table3 con a.id_rssd = c.id_rssd
where a.id_rssd = 12932
and a.dt_start = 98321212
When looking at the query execution plan, there is a warning on the Nested Left Outer Loop saying "warning no join predicates". Clearly that isn't the case. I'm trying to solve a much more complex SQL, and in analyzing that I noticed this. I've stripped everything else down to this SQL. SQL Server seems to say this on any left outer join I code?
Is this correct? Perhaps I never noticed it before.
The optimizer sees the join predicate [a.id_rssd = c.id_rssd] and the WHERE clause predicate [a.id_rssd = 12932], and implies [c.id_rssd = 12932] from those two pieces of information. It pushes the resulting implied predicate [c.id_rssd = 12932] down from the join to produce a seek on the table aliased as 'c'. The join no longer has a predicate, and this triggers the warning. More details here: http://blogs.msdn.com/b/craigfr/archive/2009/04/28/implied-predicates-and-query-hints.aspx
The warning is therefore benign in this case (the optimizer is careful to preserve outer join semantics). The warning is useful as a clue to the query writer to check the query is correctly written. Outer joining to a result that is not correlated with the driving input is a little unusual.
Shows how much attention I was paying. I didn't even see the A to C join condition.
Thanks, Paul. That's good information to have for future reference.
May 18, 2012 at 6:33 am
Great post Paul.. Thanks everybody for taking the time to respond, much appreciated.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply