PostgreSQL is a powerful, open source object-relational database system.

postgres

You know the basics of Postgres, but most meaningful queries will require you to combine tables in novel ways to ask questions you couldn't ask with a single table alone. Joins and unions come up over and over again in interview questions -- because of how fundamental they are. This course will make you a master of your tables, and give you the power to answer questions you couldn't before. 

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.

Find Postgres Data That Doesn’t Match Join Conditions with Full Outer 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.


Find Lonely Postgres Data with Left and Right Join

The cartesian product is a humdinger -- it relates all data in one table to all data in another! Now that’s a lot of rows, and fast. Surprisingly, cartesian products are as fast as inner joins at duplicate detection, thanks to the magic of the query planner!

Detect Duplicates in Postgres with Cartesian Product

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.

Find Related Data with Inner Join in Postgres

By default, union removes duplicate rows in the combined table. Union all, however, leaves all rows intact.

See All Rows from Different Postgres Tables with Union All

While our data is sometimes nicely managed with surrogate primary key joins, often times older tables can feature join conditions with complex natural keys. 

Join Tables in Postgres with Complex Conditions

Possibly my favorite data management trick is the history table. With history tables, we record all changes in two locations -- once in the primary table (as an update), and once in a history table (as an insert). With this handy trick, we save an audited record of all data, when it was live, and who changed it and when. Coupled with non-destructive deletes, you can explore the complete history of your data with joins.

Create a History Table in Postgres with Joins

Unfortunately, sometimes we don’t add the right constraints to our data. A user gets deleted and old data doesn’t get cleaned up. Primary information is deleted, but its audit trail isn’t. With left and right joins, we can flag anomalous data for deletion.

Understand Joins and Unions in Postgres

Brett Cassette

Course Content