Intuition The one core ideaA subquery is just a question hidden inside a bigger question , and the ONLY thing that matters is whether that inner question needs to peek at the current row of the outer question. If it peeks, it must be re-asked for every row (correlated ); if it doesn't, you ask it once and reuse the answer (uncorrelated ).
Before you can safely read the parent topic (index 4.4.7), you must be able to read every symbol it throws at you without stopping to guess . This page builds each one from nothing — plain words, then a picture, then why the topic needs it.
Everything in databases sits on top of one picture: a table — a grid of rows and columns, like a spreadsheet.
Definition Table, row, column
A table is a named grid of data (e.g. employee).
A row is one horizontal record — one employee, one order.
A column is one vertical field — e.g. salary, dept_id.
A cell is where a row meets a column: one single value.
Intuition Why the picture mattersWhen the topic says "runs once per outer row ", it literally means: point your finger at row 1, do something; move your finger to row 2, do it again. The rows are the things being looped over. Keep this grid in your head — every later idea is a movement across this grid.
A query is a sentence that reads rows out of a table.
Definition The three core keywords
FROM table — which grid am I reading?WHERE condition — keep only rows for which the condition is true (a filter).SELECT columns — which columns of the surviving rows do I show?
Worked example Plain readingSELECT name , salary FROM employee WHERE salary > 50000 ; "From the employee grid, keep rows where salary beats 50000, then show me their name and salary."
Intuition Why "reading order" is the whole reason subqueries existBecause WHERE runs row by row — looking at one row at a time — it cannot know a summary of all rows (like the average) yet. That summary hasn't been computed. The topic's opening motivation ("you can't write salary > AVG(salary) directly") is exactly this timing problem. A subquery is how you compute the summary first and hand it to WHERE.
A subquery is a complete SELECT written inside parentheses (...), placed inside a bigger ("outer") query. The parentheses mean "answer this small question first, then use its answer here."
Intuition Inner vs outer — the naming
The query in the brackets is the inner (or "sub") query.
The query around it is the outer query.
The whole correlated-vs-uncorrelated distinction is one question: does the inner query need a value from the outer query's current row?
A subquery's answer comes in one of three shapes. You must recognise each because different operators expect different shapes.
Definition The three return shapes
Scalar — a single cell. One number, e.g. 50000. Used with >, <, =.List (column) — one column, many rows. e.g. a column of customer_ids. Used with IN.Table — many columns/rows. Used with EXISTS (which only checks "did any row come back?").
Common mistake Shape mismatch is a real errorWhy it feels fine: you wrote a valid inner query. The trap: WHERE salary > (SELECT salary FROM employee) fails if that inner query returns many rows — > demands one number. Match the shape to the operator: scalar↔>, list↔IN, table↔EXISTS.
This is the single symbol most beginners trip on when reading correlated subqueries.
An alias is a short nickname for a table, written right after it: FROM employee e. Now e.salary means "the salary column of that particular copy of employee." (See the parent's employee e2 — a second nickname for the same table so it can be talked about separately.)
Intuition Why aliases are non-optional for correlationTo say "this row's department average", you need TWO views of employee at once: the outer view (e) and the inner view (e2). Without nicknames the database cannot tell which salary you mean. The alias is literally the wire that connects inner to outer:
e 2. dept_id = e . dept_id e.dept_id reaches out of the brackets to grab the outer row's department — that reach is what "correlated" means.
Common mistake "It errors if I run it alone"Why it feels wrong: you copy the inner query, run it standalone, and get "unknown column e.dept_id" . That's not a bug — it's the diagnostic! An inner query that fails alone is correlated (it needs the outer row). One that runs happily alone is uncorrelated .
Definition Aggregate functionAn aggregate takes a whole column of many rows and squashes it to one value: AVG (mean), COUNT, SUM, MIN, MAX. See Aggregate Functions GROUP BY .
Intuition Why aggregation forces the subqueryAVG(salary) needs to see all rows at once to add them. But WHERE only sees one row at a time. These two live in different time-zones of the query. The subquery is the bridge: compute the aggregate in the brackets (sees all rows), then let WHERE compare one row against that single answer.
IN (list)
x IN (list) is true when the value x appears somewhere in the list. Feed it a list-shaped subquery. (Careful with NULLs — see NULL handling in SQL and EXISTS vs IN vs ANY .)
EXISTS (subquery)
EXISTS (...) is true when the subquery returns at least one row — it doesn't care what the rows contain, only whether any exist . That's why the topic writes SELECT 1: the 1 is a throwaway placeholder — we only ask "did a row come back?", so the value returned is irrelevant.
Intuition Why EXISTS pairs with correlationEXISTS almost always wraps a correlated inner query, because the natural question is "does a matching row exist for this outer row ?" It short-circuits : the moment one match is found, it stops looking — a picture worth keeping.
The topic's cost formula uses three symbols. Here is each, plainly.
Definition The cost letters
N number of rows the outer query scans (how many times your finger moves down the grid).c one full run of the inner query (running it start to finish once).k one cheap comparison (e.g. salary > 50000), much smaller than c
Reading order FROM then WHERE then SELECT
Subquery a query in brackets
Return shapes scalar list table
Table aliases e and o and c
Cost symbols N and c and k
Correlated vs Uncorrelated
Point to a row versus a column in a table A row is one horizontal record (one employee); a column is one vertical field (all salaries).
The order the database actually processes a query FROM (pick grid) → WHERE (filter rows) → SELECT (choose columns).
Why WHERE cannot use AVG(salary) directly WHERE runs one row at a time, before any aggregate over all rows has been computed.
What a subquery is, in one phrase A complete SELECT inside parentheses whose answer the outer query uses.
The three shapes a subquery can return Scalar (one cell), list (one column), table (used by EXISTS).
Which operator goes with which shape scalar↔ > < = , list↔ IN , table↔ EXISTS.
What a table alias like e does Gives a table a nickname so e.salary names that specific copy's column.
The 5-second correlated test Does the inner query reference an outer alias/column? Yes → correlated; No → uncorrelated.
What SELECT 1 inside EXISTS means A throwaway value — EXISTS only checks whether any row came back, not what it holds.
What N, c, and k stand for in the cost model N = outer rows, c = cost of one inner-query run, k = cost of one cheap comparison.
Why naive correlated cost is N·c not c The inner query re-runs once for every outer row instead of a single time.
Parent topic (Hinglish) — where these foundations get used.Aggregate Functions GROUP BY — the source of AVG/COUNT that subqueries compare against.EXISTS vs IN vs ANY — choosing the right membership operator.NULL handling in SQL — why IN can misbehave where EXISTS is safe.Joins — inner outer cross — what optimizers rewrite correlated subqueries into.Query Optimizer Execution Plans — how the DB decides loop vs rewrite. 
