Background
A couple of weeks ago, I noticed a warning in a query plan.
The warning read “No Join Predicate“.
Query Plan
Warning – No Join Predicate
Sample Queries
Conventional Join
Code
use [DBLab]
go
if object_id('school.usp_TripParticipants_List_NJP_Legacy') is null
begin
exec('create procedure school.usp_TripParticipants_List_NJP_Legacy as ')
end
go
alter procedure [school].[usp_TripParticipants_List_NJP_Legacy]
as
begin
; with cteParticipant
as
(
select *
from [school].[student]
union
select *
from [school].[faculty]
)
select
[trip]
= tblT.[name]
, [tripDate]
= [tblT].tripDate
, [participant]
=
tblP.firstname
+ ' '
+ tblP.lastname
from
[school].[tripParticipant] tblTP
, [school].[trip] tblT
, cteParticipant tblP
where tblTP.tripID = tblT.id
-- and tblTP.participantID = tblP.id
end
go
Query Plan
Explanation
- In this example we are using the old classic join
- We have three tables, but only joined the first two tables
ANSI Join
Code
use [DBLab]
go
if object_id('school.usp_TripParticipants_List_NJP') is null
begin
exec('create procedure school.usp_TripParticipants_List_NJP as ')
end
go
alter procedure [school].[usp_TripParticipants_List_NJP]
as
begin
; with cteParticipant
as
(
select *
from [school].[student]
union
select *
from [school].[faculty]
)
select
[trip] = tblT.[name]
, [tripDate] = [tblT].tripDate
, tblP.firstname
, tblP.lastname
from [school].[tripParticipant] tblTP
inner join [school].[trip] tblT
on tblTP.tripID
= tblT.id
inner join cteParticipant tblP
/*
on tblTP.participantID
= tblP.id
*/
on tblTP.tripID
= tblT.id
end
go
Query Plan
Explanation
- In this example we are using ANSI join
- We have three tables and two join clauses
- Unfortunately the second join does not actually join the two tables that it is meant to join
- We intentionally cut and pasted the early join.
- But, did not modify the pasted clause and reference the actual tables we are trying to join
Summary
The samples listed above are simple and easy to correct.
The one I actually ran into is a bit more difficult to address.
