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

How can we find out which Kickstart projects have no backers? Or which backers have not backed any projects? Left and right join can answer these questions.


[00:01] We can select * from projects, and inner join on backers on projects.id = backers.project_id. This gives us a list where every backers project ID matches up with the project's own ID, and gives us the intersection of those fields.

[00:27] If we look for any row where the backers.id is null, we would get no rows back, because for every project row, there has to be an equivalent backer row.

[00:43] What is a left join? A left join gives us everything from projects regardless of whether or not that project has any backers. In this case, we see that these two projects have no backers, so we've joined them on nulls.

[00:59] If we only wanted to see projects that don't have backers, we could say where backers.id is null. Now, we have some rows. To state it more plainly, the project is the left table and the backer is the right table. This is in part because of the way they're presented to us.

[01:25] For a left join, we'll take everything in the left table. For a right join, we'll take everything in the right table. Regardless of whether there are nulls in the other table, we'll still join every row.

[01:40] If we perform the right join, we'd have a null in this column. Here, we could say where projects.id is null and this would show us only backers who have backed no projects. Those are the left and right join.

Course

Understand Joins and Unions in Postgres

Find Lonely Postgres Data with Left and Right Join

Share this video with your friends