What is the logic behind doing a full outer join with multiple join statements?

Question

SELECT MP.* FROM SurveyFrontend..WebResult WR JOIN MeetingHistory MH ON MH.WebResultID=WR.WebResultID JOIN Meeting M ON MH.MeetingID=M.MeetingID JOIN MeetingPlanner MP ON MP.MeetingPlannerID=M.MeetingPlannerID WHERE PrimaryEntityID=2424 AND WR.TimeResultTaken>='1/1/2016' AND CardSet=2

I've looked it up but I can't find any examples on how to do a full outer join with multiple joins. I want to pull the exact opposite of the above query.

How would I go about doing this?

Here is exactly what I'm looking for:

Updated code:

SELECT MP.* FROM SurveyFrontend..WebResult WR FULL OUTER JOIN MeetingHistory MH ON MH.WebResultID=WR.WebResultID FULL OUTER JOIN Meeting M ON MH.MeetingID=M.MeetingID FULL OUTER JOIN MeetingPlanner MP ON MP.MeetingPlannerID=M.MeetingPlannerID WHERE PrimaryEntityID=2424 AND WR.TimeResultTaken>='1/1/2016' AND CardSet=2 AND (MH.WebResultID IS NULL OR MH.MeetingID IS NULL OR MP.MeetingPlannerID IS NULL OR WR.WebResultID IS NULL OR M.MeetingID IS NULL OR M.MeetingPlannerID IS NULL)

Replace all instances of JOIN here (which is implicitly an INNER JOIN) with FULL OUTER JOIN, WHERE anyA key(s) are NULL or any B keys are NULL. The syntax is shown in the image you cropped: codeproject.com/KB/database/Visual_SQL_Joins/… — Cᴏʀʏ
– Cᴏʀʏ, Commented May 28, 2016 at 0:59
I know that part but I don't know what to put in the WHERE clause. Thank you though! — ShadyBears
– ShadyBears, Commented May 28, 2016 at 1:01

Alex Kudryashev · Accepted Answer · 2016-05-28 01:48:03Z

full outer join returns all rows which match on condition and all rows from left table which don't match condition and all rows from right table which don't match condition. Any where restrictions reduce full outer join to left, right, or inner join. Some examples:

create table #a(id int, name varchar(10)) insert #a values (1,'test1'),(2,'test2'),(3,'test3') create table #b(id int, name varchar(10)) insert #b values (1,'test1'),(4,'test4'),(5,'test5') select a.id aid,a.name aname, b.id bid,b.name bname from #a a full outer join #b b on a.id=b.id --same as left join select a.id aid,a.name aname, b.id bid,b.name bname from #a a full outer join #b b on a.id=b.id where a.id=2 --same as right join select a.id aid,a.name aname, b.id bid,b.name bname from #a a full outer join #b b on a.id=b.id where b.id=4 --same as inner join select a.id aid,a.name aname, b.id bid,b.name bname from #a a full outer join #b b on a.id=b.id where a.id=1 and b.id=1 --same as inner join better example select a.id aid,a.name aname, b.id bid,b.name bname from #a a full outer join #b b on a.id=b.id where a.id = b.id

Tests of is null in the where clause don't always reduce full outer to a left, right, or inner join. Specifically LeftPK is null or RightPK is null gives rows from both side that don't have matches.

Collectives™ on Stack Overflow

What is the logic behind doing a full outer join with multiple join statements?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related