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

In this lesson, we’ll learn how to relate data on a condition that exists in multiple tables. The inner join will help us answer the question “Which users have backed our Kickstarter project?” We’ll also see what join conditions are all about.

[00:01] Go ahead and select all from the project table and give it an alias of just P, and we can inner join on backers B, on P.ID, that's the project.ID = backers.project_ID.

[00:18] This on clause is our condition, this is what tells us which rows to join. We take all of the IDs from the projects table. Everything right here is the projects table, and matches it up with the project ID in the backers table. Everything on this side is the backers table. We can see that we just smooshed the projects table together with the backers table to create new rows here.

[00:44] Any time the project_ID = project.ID, we're going to join and create a new row. We can see that all of these match up and that's because of this join condition.

[00:57] We also can say where project.ID is not equal to the project_ID, here we can see one does not equal two, 20 does not equal one, really this join condition wouldn't make a lot of sense, and it doesn't make any sense to use it.

[01:14] This is just showing you we can do this on any condition. Here, 1 = 1, this will take everything from the left table and join in with the right table, so projects and backers.

[01:25] We can see all of the correct IDs matched up and also the incorrect IDs matched up, and just to prove that that's true, we can say where 1 = 2, so that should evaluate to false. We could really just put in true or false, and these evaluate to the exact same thing, although often times, we'll actually just use the on clause to evaluate some meaningful condition.

[01:49] Let's go back to P.ID = B.project_ID, and just to show you what this is all about, we can do the same thing with the where clause, so we can say on TRUE where P.ID = B.project_ID.

[02:10] Someone might tell you this is not idiomatic SQL, it's not. Just to prove that these are fundamentally the same, we can also run an explain on this. We can also run an explain on this. We can see that the query planner will do the exact same thing under the hood.

[02:35] We don't have to understand what this means too much right now, but we can see that these are the exact same outputs. If you're having trouble thinking about the on clause, you can just think about it as if it was a where.

Course

Understand Joins and Unions in Postgres

Find Related Data with Inner Join in Postgres

Share this video with your friends