Find Postgres Data That Doesn’t Match Join Conditions with Full Outer Join
What if we want to see all Kickstarter projects with backers, plus projects that have no backers, plus backers that have no projects? We can use full outer join for that. In the wild, full outer join, left join, and right join will most often be used for orphan detection.
[00:00] Let's take a look at an inner join. We'll select star from projects, inner join backers and projects.ID = backers.project_ID. An inner join, once again, is all of the rows which have a project ID in the backers table matching a project ID in the projects table.
[00:24] A left join gives us everything in the projects table regardless of whether or not it matches a project ID in the backers table.
[00:34] A right join gives us everything in the backers table regardless of whether or not a project ID matches a project in the projects table. A full outer join gives us all of these. This ensures that we have everything from an inner join, everything from a left join, and everything from a right join put together.
[00:58] This is why it's often called the full Venn diagram where the inner joins are the things that intersect, the left joins are the things that are in the left Venn diagram but not the right, and the right join is everything that's in the right side of the Venn diagram but not the left.