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: http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg — Cᴏʀʏ, May 28 '16 at 00:59
I know that part but I don't know what to put in the WHERE clause. Thank you though! — ShadyBears, May 28 '16 at 01:01

Alex Kudryashev · Answer 1 · 2016-05-28T01:48:03.093

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. — Shannon Severance, May 28 '16 at 06:52

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

1 Answers1