Find Postgres Data That Doesn’t Match Join Conditions with Full Outer Join

Find Lonely Postgres Data with Left and Right Join

Detect Duplicates in Postgres with Cartesian Product

Find Related Data with Inner Join in Postgres

See All Rows from Different Postgres Tables with Union All

Join Tables in Postgres with Complex Conditions

Create a History Table in Postgres with Joins

Use Joins to Detect Orphaned Data in Postgres

Understand Joins and Unions in Postgres

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.

Course

Understand Joins and Unions in Postgres

Find Postgres Data That Doesn’t Match Join Conditions with Full Outer Join

Share this video with your friends