Transact SQL – No Join Predicate – “Unioned” CTE

Background

In our last post, we laid the foundation for this post.

Foundation Post

In that previous post we covered two very simple examples that triggers the “No Join Predicate” warning.

Here is that post:

Transact SQL – Warning – “No Join Predicate”
Link

 

Common Table Expression – Union

Overview

In this post we will try to trigger same warning by using a Union within a common table expression.

Diagram

diagram_school_20180619_1102AM.png

Data

Base Table

data_baseTable_20180619_0945AM.png

Trip Participant

data_tripParticipant_20180619_1003AM.png

Stored Procedure

school.usp_TripParticipants_List

Code


use [DBLab]
go

if object_id('school.usp_TripParticipants_List') is null
begin

    exec('create procedure [school].[usp_TripParticipants_List] as ')

end
go

alter procedure [school].[usp_TripParticipants_List]
(
    @tripID bigint
)
as

begin

    set nocount on

    set XACT_ABORT on


    ; with cteSchoolBody
    (
          id
        , identifier
        , firstname
        , lastname
        , [poolID]
        , [pool]
    )
    as
    (

        select 
                 tblS.id
               , tblS.identifier
               , tblS.firstname
               , tblS.lastname
               , [poolID] = 1
               , [pool] = 'Student'

        from   [school].[student] tblS

        union all

        select 
                 tblF.id
               , tblF.identifier
               , tblF.firstname
               , tblF.lastname
               , [poolID] = 2
               , [pool] = 'Faculty'

        from   [school].[faculty] tblF

    )
    select 
              [tripID] = tblT.[id]

            , [tripName] = tblT.[name]

            , cteSB.[pool]

            , cteSB.lastname

            , cteSB.firstname

            , cteSB.identifier

    from   [school].[trip] tblT

    --inner join [school].[tripParticipant] tblTP
    inner join [tripParticipant] tblTP

        on tblT.id = tblTP.tripID

    inner join cteSchoolBody cteSB
        on  tblTP.participantID = cteSB.id
        /*
            intentionally left of participantTypeID
            and tblTP.[participantTypeID] 
				= cteSB.[poolID]
        */ 
    where (

            ( 
                tblT.[id] 
                    = isNull(@tripID, tblT.[id])
            )

          )

    order by
              [tripName]

            , [pool]

            , cteSB.lastname

            , cteSB.firstname

    
end
go




 

Output

tripParticipant_20180619_1012AM

Explanation
  1. Noticed duplicate records

 

Query Plan

qp_20180619_1015AM

Explanation
  1. Thought that missing the participantTypeID will trigger “No Join Predicate” warning.

Source Code

GitHub

DanielAdeniji/SQLServer.SampleDB.School
Link

Listening

Paul Simon – Take me to the Mardi Gras
Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s