Mastering Postgres
Introduction
Introduction to the course
Overview of course structure
Postgres vs. everyone
The psql CLI
Data Types
Introduction to schema
Integers
Numeric
Floating point
Storing money
NaNs and infinity
Casting types
Characters types
Check constraints
Domain types
Charsets and collations
Binary data
UUIDs
Boolean
Enums
Timestamps
Timezones
Dates and times
Advanced Data Types
Intervals
Serial type
Sequences
Identity
Network and mac addresses
JSON
Arrays
Generated columns
Text search types
Bit string
Ranges
Composite types
Nulls
Unique constraints
Exclusion constraints
Foreign key constraints
Indexing
Introduction to indexes
Heaps and CTIDs
B-Tree overview
Primary keys vs. secondary indexes
Primary key types
Where to add indexes
Index selectivity
Composite indexes
Composite range
Combining multiple indexes
Covering indexes
Partial indexes
Index ordering
Ordering nulls in indexes
Advanced Indexing
Functional indexes
Duplicate indexes
Hash indexes
Naming indexes
Understanding Query Plans
Introduction to explain
Explain structure
Scan nodes
Costs and rows
Explain analyze
Generating Results
Introduction to queries
Cross joins
Inner joins
Outer joins
Subqueries
Lateral joins
ROWS FROM
Filling gaps in sequences
Subquery elimination
Combining queries
Set generating functions
Indexing joins
Advanced SQL
Introduction to advanced SQL
Grouping
Grouping sets, rollups, cubes
Window functions
CTEs
CTEs with window functions
Recursive CTE
Hierarchical recursive CTE
Handling nulls
Row value syntax
Views
Materialized views
Removing duplicate rows
Upsert
Returning keyword
COALESCE + generated column
Full Text Search
Introduction to full text search
Searching with LIKE
Vectors, queries, and ranks
Websearch
Ranking
Indexing full text search
Highlighting
Advanced JSON
Intro to JSON
JSON vs JSONB
Validating JSON
Creating JSON objects + arrays
JSON extraction
JSON containment
JSON existence
JSON recordset
Updating JSON
Indexing JSON parts
GIN index
Vectors (pgvector)
Intro to pgvector
Vector embedding columns
Find related articles
Upsert vector embedding
Semantic search
Other operators
Vector indexes
Outro
Thank you
Bonus interviews
Heroku's glory days & Postgres vs the world (with Craig Kerstiens)
Creating a Postgres platform with Monica & Tudor from Xata.io
Bootstrapping an email service provider (with Jesse Hanley)
Locked video

Please purchase the course to watch this video.

Video thumbnail
Generating Results
Outer joins

Full Course

$
349
$399
USD, one-time fee
Mastering Postgres is single-handedly the most thorough and informative database course I’ve seen. I’ve gone from someone who barely knew the basics of Postgres and how it works to being comfortable with the advanced topics of Postgres.
Ryan Hendrickson
Ryan Hendrickson

PostgreSQL database platform

Shorten dev cycles with branching and zero-downtime schema migrations.

Test Data

I've made my test data available for you to use and follow along.

Download
or
Use on Xata

Summary

Learn the key differences between SQL join types, including inner joins and outer joins—left, right, and full. You'll see how a left join keeps all rows from the left table and matches from the right, while a right join does the reverse. A full join includes all rows from both tables, matched or not—less commonly used but helpful to understand for complex data relationships.

Video Transcript

We're working our way down the list. We've done cross join, we've done inner join, and now we're going to do outer join, which is kind of a category that contains, well, it contains three types of joins.

The outer join category contains a left outer, a right outer, and a full outer join. Now remember with the inner join, what it does is it takes a left table, a right table, joins it on the condition, and regardless of which table it's in, if it doesn't have a match, it's gone.

With an outer join, you have control over that. You can decide, I want to keep everything from the left table, no matter what. I want to keep everything from the right table, no matter what, or match everything together. If there are no matches, just give me everything back anyway, I don't really care.

Let's take a look at a left outer join to start. The syntax is very similar. We're going to do select * from users and then we're going to say, left join bookmarks. Interesting, we didn't write outer. Left join bookmarks on users.id equals bookmarks.user_id. In fact, user ID is not ambiguous. We don't have to write bookmarks there, but I do like to see it. Let's do that, and then let's throw a limit 10 on there. Let's make a little space down here and let's run that. We see Kyleigh and Waylon are back at the top. Welcome back folks, and we can limit this down.

Let's do this again. Users.id, bookmarks.user_id, first name, and URL. That seems good enough. We see Kyleigh has all of their bookmarks and then Waylon has their bookmarks following. What is different here? Well, what's different here is every single user will show up in this query. Every single user is going to show up in this query, even if they don't have bookmarks. Because we set the left table to be users and then we said left join, which means keep the left table and join in the right table where it works, but no matter what, you got to hang onto that left table for me.

Let me see if that is provable. And how can we prove that? Well, let's drop down here and say where bookmarks.id, we could pick anything but bookmarks.id is null. If we run that, we will see there are some users that have been joined to null bookmarks because they don't have any bookmarks. It just shows up as the left-hand table. In fact, the entire left-hand table is there. It looks a little sparse right now, but you'll see the user ID column is null, 'cause that comes from the bookmarks table. The URL column is null because that comes from the bookmarks table. It's not qualified, but it does come from the bookmarks table. Everything that was in the left-hand table has been preserved. You can switch this around.

If we were to do a right join, think about it. If we were to do a right join, what are we saying and are we going to see these users? What we're saying is the user's table is the left-hand table and the bookmarks table is the right-hand table. We are giving preference to the right-hand table and saying no matter what, give me everything from the right-hand table. This is going to be totally empty. This is going to be empty because the entire bookmarks table is brought back. But we might see bookmarks, not bookmarks, user ID, users.id might be null. Oh, there's one. There's one right there. There you go. So users.id, we'll do this as, let's just get rid of this. Let's just say bookmarks.id. This bookmark right here, 4 9 5 7 3 6 has no owner. This bookmark has no owner because if we look at bookmarks.user_id, we'll see in fact that that is null. Because it was in the right-hand table, it was in the right table and we said, prefer a right join, it is going to be brought back.

A left join prefers the left table and a right join prefers the right table. What about a full join? (instructor snaps fingers) It does both.

If we were to change this from right join to full join, we'll see that we still have this one orphaned bookmark, but also we still have, how did we do that? We did bookmarks.id is null. We still have all of the users that have no bookmarks as well. But if we were to drop this, we see we still have all the matches. We still have all the matches. We got Ladarius at the top now. We have all of the matches. Ladarius and his bookmarks have matched, but we also preserved the entirety of the left-hand table. For whatever reason, we preserved the entirety of the right-hand table.

You may have guessed from my little chuckle there that I don't use full outer joins hardly ever. I can't think of an actual time I've ever used it. If you have, please let me know. I would love to hear about it. Sincerely, I would love to hear about it.

Remember that an inner join is the default for a conditional join. If you want to switch to one of the outer joins, you need only to write full, left or right. If you do that, it is implied that it is an outer join. You can of course, write left outer join. You could write full outer join, but you don't have to.

Keep in mind what order you declare in your query, what order you declare your tables, and what type of join you're doing. And that will declare which tables are returned in their entirety. Unless you do full, then you get everything.