0:01Here's a question that takes two steps to answer.
0:03Which orders are bigger than average? You can't filter anything until you know the average.
0:08One query to find it.. then a second query to keep the orders above it.
0:13Or, put the first query inside the second, right where its value belongs. A query inside a query.
0:20That's a subquery. And this video explains how to use it.
0:25Four parts in this video:
0:27First, the simplest. A whole query standing in for one single value.
0:32Then IN and EXISTS. Two different ways of asking whether something has a match.
0:37Then correlation, where the inner query reads from the roe outside it.
0:42And finally, when you'd reach for a join or a CTE instead.
0:46Before any of that, the data. Two small tables power everything in this video. Three customers.. Ada, Grace, and Alan.
0:53And three orders. Each one points back at a customer through customer id.. that column is the link between the two tables.
1:01Ada placed two orders. A hundred and twenty, and thirty-five.
1:04Grace placed one, for eighty.
1:06And Alan has placed none at all. Remember Alan.. half the queries in this video exist to either find him or skip him.
1:14Here's the query from the open again, now with data behind it. The value the filter needs comes from the inner query, in the parentheses.
1:23That inner select runs first, in effect. It averages all three amounts.. about seventy-eight point three three.
1:31Two orders clear that bar. A hundred and twenty, and eighty.
1:35This form is called a scalar subquery. One column, at most one roe.. so it can stand anywhere a single value could stand.
1:43And anywhere includes the select list. Same inner query as before.
1:47This time we subtract the average from every amount, and name the difference.
1:51Positive means above average, negative means below. Every roe had the same single value subtracted from it.
1:59Now, two ways this can go sideways. First, what if the inner query finds nothing? There is no order nine ninety-nine.
2:07No error, and no zero either. An empty scalar result becomes NULL.. the same unknown we met in the last video.
2:15Second, what if the inner query returns more than one roe? This one returns all three amounts.
2:22Postgres refuses. More than one roe, where a single value was promised. Sequellite quietly takes the first roe instead.. so make sure the inner query really produces one value.
2:34The second form answers a different question. Is this value among the ones another query returns? Let's run that inner query on its own first.
2:45Three roes come back. Customer ids one, one again, and two.
2:49Now the membership test. Is one among those values? It is.. and the fact that it appears twice changes nothing.
2:56Is three among them? No. That's the entire test. Present, or not present.
3:02So here's the full query. Keep each customer whose id is in the set that inner query returns.
3:08Ada and Grace come back. Alan's id is three, so he doesn't.
3:11And notice Ada appears once, not twice. IN filters the customer roes.. it doesn't create a roe for every matching order.
3:20The third form doesn't collect values at all. It asks the yes-no question directly. Does a matching roe exist?
3:27The inner query hunts for orders belonging to the customer we're currently looking at.
3:32This condition is the match. An order whose customer id equals this customer's id.
3:38And EXISTS asks exactly one thing. Did at least one roe come back?
3:44Ada and Grace each have at least one. Alan has none, so he fails the test.
3:48Now, that select one tends to bother people. It's just a convention. EXISTS checks whether a roe came back, not which value was selected.. select star would behave exactly the same here.
4:01So IN and EXISTS can answer the same question, phrased two ways.
4:05IN reads like membership. Is my id among the values that came back?
4:10EXISTS spells the matching condition out. And that condition can be anything, not just equality. Both come back with Ada and Grace.
4:20Same query as before. Now let's flip it, because what I actually want is the customers with no orders.
4:27One word does it. NOT EXISTS keeps a customer only when no matching order comes back.
4:34Ada and Grace have matches, so now they're the ones excluded. And there's Alan.
4:39You might reach for NOT IN instead, and usually it works. But there's a trap in it. Suppose the value list ends up containing a NULL.
4:47We've added one deliberately here, so the values are one, one, two.. and unknown.
4:52Zero roes. Not even Alan. No error, no warning.. the filter just quietly stopped working.
4:58So for no-matching-roe questions, prefer NOT EXISTS. It asks about roes rather than values, and that question always has an answer. The NULL video walks through exactly why this happens.
5:13The last idea has been hiding in this query the whole time. Look closely at the inner select.
5:19That c dot id doesn't belong to the inner query at all. It reaches out to whichever customer roe the outer query is holding right now.
5:27An inner query that reads its outer roe is called correlated. And it's a dependency, not a form of subquery.. an EXISTS test or a scalar subquery can each be correlated, or not.
5:41Here's a correlated scalar subquery doing real work. Each customer's largest order. This time the outer roes are the orders themselves.
5:50For each order, the inner query finds the biggest amount.
5:53For that order's own customer. There's the outer-roe reference again.
5:58And we keep an order when its amount equals that customer's maximum. So one oh one survives for Ada, one oh three for Grace.. each compared only against its own customer's orders.
6:11So which tool do you actually reach for? Match it to the question. Customers with no orders only needs names.. and a match test.
6:21A subquery fits that perfectly. You filter customers without dragging any order columns in. For a value, or a filter.. reach for a subquery first.
6:33But say you want customer names next to order amounts. Now you need columns from both tables.. and that's a join.
6:40Three roes come back.
6:41And look at Ada.. she appears once per matching order. A join can multiply roes. An EXISTS filter never does that.
6:50The third option is about reading, not filtering. Here's our average query again. Let's give that inner step a name.
6:57The inner query lifts out into its own named block at the top. The where clause now just refers to it. Same calculation, same result.
7:06That's a CTE. Worth it when a step deserves its own name, or gets used more than once. It does not promise the database will cache it.
7:15One last thing, about speed. These are starting points.. not performance rules.
7:20A subquery version and a join version both land on the same optimizer.
7:25And they may well end up sharing a plan. Not always, though. So write the clear version first.. and when speed matters, check the plan and measure.
7:35That's the whole picture.
7:37You can use an entire query as one single value, anywhere a value could go.
7:42You can test membership with IN, and matches with EXISTS.. and you know which of the two survives a NULL in the list.
7:49You can spot an outer-roe reference, and read a correlated query the way it's meant to be read.. roe by roe.
7:56And you can match the tool to the question. A subquery for a value or a filter, a join for columns from both sides, a CTE to name a step.
8:08Here's one to try before the next video. Customers with no orders, written both ways.. NOT EXISTS, and NOT IN. Then sneak a NULL into the NOT IN list, and watch what breaks.

SQL Subqueries

Created by Abhyuday DesaiPublished 3 Oct, v1Runs 8 min 27 sec

0 views

About this video

The four subquery forms interviewers expect you to tell apart, and when each is the right tool. Three customers and three orders, one of them with no orders at all, power every example.

You'll review how to:

Use a scalar subquery as a single value in WHERE or the SELECT list, and what happens when it returns none or many rows
Test membership with IN and existence with EXISTS and NOT EXISTS, and why NOT IN breaks on NULL
Read a correlated subquery one outer row at a time
Choose between a subquery, a JOIN and a CTE for a presence or absence question

For engineers who know SELECT, WHERE, GROUP BY and basic joins. Presence and absence questions, and the anti-join.

What changed

v103 Oct 2026abhydesai8 min 27 sec037acd5

First published version.

0 views0 likes on this version