Find Lonely Postgres Data with Left and Right Join
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.