Autoplay

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

    Brett CassetteBrett Cassette

    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.

    postgresPostgreSQL
    Code

    Code

    Become a Member to view code

    You must be a Member to view code

    Access all courses and lessons, track your progress, gain confidence and expertise.

    Become a Member
    and unlock code for this lesson
    Transcript

    Transcript

    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.

    Discuss

    Discuss