Subquery
ELI5 — The Vibe Check
A subquery is a query inside a query. The inner query runs first and its result is used by the outer query. It is like asking 'give me all users whose ID appears in (give me all user IDs who ordered something last week)'.
Real Talk
A subquery is a SELECT statement nested inside another SQL statement. It can appear in the WHERE clause (to filter based on another query's results), the FROM clause (as a derived table), or the SELECT clause (as a scalar subquery). Correlated subqueries reference the outer query and execute once per outer row.
Show Me The Code
-- Users who have placed at least one order
SELECT name FROM users
WHERE id IN (
SELECT DISTINCT user_id FROM orders
);
When You'll Hear This
"Use a subquery to filter based on aggregate results." / "Subqueries can often be rewritten as JOINs for better performance."
Related Terms
JOIN
JOIN combines rows from two tables based on a related column.
N+1 Query
N+1 is when your code runs 1 query to get a list of things, then runs 1 more query for EACH thing on the list.
SELECT
SELECT is how you ask a database to give you data. It is the 'Read' in CRUD.
WHERE
WHERE is how you filter which rows a query affects. Without WHERE, SELECT returns everything, UPDATE changes everything, DELETE deletes everything.