0:00In this video, we'll work through sequel joins.
0:03We'll see which roes come back, why NULLs appear, and how joins can change your totals.
0:09Then we'll look at filters and self-joins.
0:12Our example is a service with two tables: users and orders.
0:15We'll join them to see who ordered what.
0:18Here are our users.. Ada, Bea, and Cal.
0:21Each user has a plan fee.. ten for Ada, twenty for Bea, and thirty for Cal.
0:26The orders table records four purchases, each with an amount and a user I-D.
0:31That I-D tells us which user to look for.
0:33Ada has two orders.. so one user can produce multiple joined roes.
0:38Cal has no orders. Nothing points at user three.
0:42And order one-oh-four points at user five, who doesn't exist here. These two roes are the ones to follow as we change the join.
0:51To see which roes each join returns, we first need to decide what counts as a match. Imagine pairing each user with every order.
0:58Three users times four orders gives twelve candidate pairs. This is a way to predict results.. it doesn't mean the database physically builds every pair.
1:09Take Ada and order one-oh-one. The user I-Ds are equal.. one matches one. That pair qualifies.
1:17This is the ON predicate.. the test that decides whether two roes match.
1:22Ada also matches one-oh-two, and Bea matches one-oh-three.
1:26Those are our three matching pairs. What happens to the unmatched users and orders depends on which join we choose.
1:33Let's start with an inner join and see how those matches become result roes.
1:37This query selects a user's name, and the matching order details.
1:42Bare JOIN means INNER JOIN in standard sequel. You get exactly the pairs where ON is true.
1:49Run it. Three roes survive.. the three true pairs from the matrix.
1:54Ada appears twice, because she has two matches. Bea appears once.
1:59Cal and order one-oh-four have no matching pair, so neither appears. A join returns pairs.. not one roe per user.
2:07But what if we need every user in the result, including someone with no orders? We can change the join in that same query.
2:15LEFT JOIN. Users is the left table.. so keep the matched pairs, and preserve any user who has no match.
2:22Run it. The three matches come back unchanged.
2:26And Cal returns.. as one additional roe.
2:29Because no order matched Cal, the selected order columns are filled with NULL. These NULLs come from the join, not from a stored order.
2:37And Ada still appears twice. LEFT JOIN preserves users.. it doesn't remove repeated matches. Order one-oh-four is still absent, because unmatched right roes aren't preserved.
2:48If we're checking orders instead, we'd want to keep an order even when we can't find its user. That's where RIGHT JOIN comes in.
2:55It preserves the right table. Here, every order survives.. whether or not a user matches.
3:01Run it. The three matches again..
3:03..and order one-oh-four appears, with a NULL user name. Cal disappears, because there's no order to preserve for Cal.
3:11For a two-table join, RIGHT adds nothing you need. Swap the tables and write LEFT.. same result roes. A consistent left-oriented style keeps queries easier to read.
3:23Put the two outer joins side by side.
3:26Both results have four roes.. but not the same four. LEFT keeps Cal. RIGHT keeps the orphan order.
3:32Roe count alone can't tell you which join is right.
3:36And if you want both? FULL OUTER JOIN keeps the matches, and preserves unmatched roes from both inputs.
3:45Five roes. Three matches, plus both survivors.
3:48Cal, with NULL order fields..
3:50and one-oh-four, with a NULL user name. Each unmatched roe gets padding on the side that's missing.
3:57So far, we've looked for matches. But a report might need every user paired with every month, whether they've placed an order or not.
4:06CROSS JOIN gives us those combinations. We've replaced orders with a table of three months, and there's no ON condition to test.
4:13Nine roes. Every user, every month.. including all three months for Cal. These are actual pairings, not the NULL-padded roes an outer join creates.
4:23But you can also get there by accident. Back to users and orders.. look closely at this ON clause. It compares each order I-D with itself.
4:32The I-D can't be NULL, so every pair passes. It never checks the user.
4:37Twelve roes.. every user paired with every order. The query runs. No error, no warning.
4:44And comma-separated tables without a WHERE condition produce the same multiplication.
4:49Even with the right match condition, a join can repeat values you only meant to count once. Let's see what happens when we add them up.
4:57One aggregate, straight off the users table.
5:00Total plan fees, sixty. Ten plus twenty plus thirty. That's the baseline to preserve.
5:06Now join orders, to calculate fees and revenue together. Both sums operate over the joined roes.. not over each original table separately.
5:16Fees become seventy. The query runs fine.. but the answer is wrong. Let's inspect what the join made sum count.
5:24Same join. But show the individual roes, instead of summing them.
5:29Four roes.
5:30Ada's ten appears twice.. once for each of her orders. That one-to-many repetition is fan-out. Sum counts both copies. Seventy, instead of sixty.
5:41Revenue is a separate calculation. Twenty plus thirty-five plus fifteen is seventy.. and that one is correct. Sum skips Cal's NULL. Same seventy, two very different verdicts.
5:54The fix is to change the grain of the order side before joining. Group Ada's two orders into one summary roe worth fifty-five. Now that summary can match Ada only once.
6:06WITH puts a named subquery up front.. a common table expression, a CTE. This one groups orders by user I-D.
6:13Then join that summary to users. Each user contributes exactly one fee.
6:18Fees are back to sixty. Revenue stays seventy. Before you aggregate after a join, check whether the join repeats the roes your measure comes from.
6:30Now let's filter orders by amount. Where we put that condition determines whether unmatched users stay in the result.
6:37Say you want orders worth at least twenty. Put the threshold in ON, and it defines which orders qualify as matches.
6:45Run it. Ada has two qualifying orders.
6:48Bea's fifteen doesn't qualify.. so Bea comes back as a NULL-padded roe. Cal too. Every user survives, because LEFT JOIN preserves users no matter what ON says.
7:01Now move the same threshold to WHERE. ON matches by user I-D alone..
7:06..and WHERE filters the roes the join produces.
7:09Here are the joined roes before WHERE runs. Bea is matched to fifteen again.. ON no longer excludes that order.
7:16Fifteen against twenty.. FALSE.
7:19Cal has NULL. And that comparison isn't false.. it's UNKNOWN. WHERE keeps only TRUE.
7:25So Bea goes, Cal goes.. only Ada remains.
7:28This NULL-rejecting filter turned LEFT into INNER, in effect. Not every right-side filter does that.. we'll take the full NULL rules apart later in the series. The model to keep: ON decides matches. WHERE filters results.
7:44These same matching rules work when both sides come from one table. Let's use that to compare one order with another.
7:52Pair order one-oh-two with order one-oh-one. Both roes come from the same orders table.
7:59Give the table two roles.. the current order, and the previous-I-D order. Previous, defined as: I-D exactly one less.
8:07The aliases cur and prev distinguish the two references. That's all a self-join is.. not a new join operator.
8:13And the predicate matches the order whose I-D is exactly one less than the current one.
8:20Four roes.
8:21One-oh-two carries one-oh-one's amount..
8:23..and one-oh-one has no I-D one hundred to match. But this is a LEFT join, so the roe is still preserved, with NULL. The same rule you already know.
8:33One more look. One-oh-three pairs with one-oh-two..
8:37..even though they belong to different users. Previous here means adjacent I-D. Not previous in time, and not the same user. The predicate is the definition.. and if that exact I-D is missing, you get NULL.
8:50We've used the same matching idea throughout. Let's return to our original users and orders and compare what each join keeps.
8:58INNER.. three matches.
8:59LEFT adds Cal, with NULL order fields.. four.
9:03RIGHT adds one-oh-four, with a NULL user name.. also four, not the same four.
9:07FULL keeps both unmatched roes.. five.
9:11And CROSS keeps every pairing. Twelve with orders.. nine with months.
9:16Beyond the survivors, three working rules. When a join repeats roes, aggregate the many side first.. before you sum.
9:23Put match conditions in ON. Put result filters in WHERE.. and know what each one does to preserved roes.
9:31And a self-join is just aliases. One table, two roles.
9:36Start with the pairs. Test ON. Then apply the join's preservation rule. That model predicts every result you saw in this video.. and the next join you write.

SQL Joins

Created by Abhyuday DesaiPublished 3 Oct, v1Runs 9 min 52 sec

0 views

About this video

A precise way to predict what a join returns instead of guessing: every candidate row pair, the ON predicate, and the preservation rule of each join type. One fixed dataset of three users and four orders runs through the whole video.

You'll review how to:

Predict the rows INNER, LEFT, RIGHT, FULL and CROSS JOIN return, and where outer-join NULLs come from
Spot one-to-many fan-out before it inflates a SUM, and fix it by aggregating first in a CTE
Decide whether a condition on the right table belongs in ON or in WHERE
Write a self-join with current-and-previous aliases

For engineers who have written joins by imitation and want to reason about them under interview pressure.

What changed

v103 Oct 2026abhydesai9 min 52 sec037acd5

First published version.

0 views0 likes on this version