SyntaxShelf

In subquery

3 snippets in SQL

Also written as all subquery · subquery in from

SQLSQL

Subquery in WHERE with IN

SQL · Subqueries
Syntax
WHERE column IN (SELECT column FROM ...)
Example
SELECT first_name, email
FROM users
WHERE id IN (
  SELECT user_id
  FROM orders
  WHERE total_amount > 500
);
Output
-- Users who have placed at least one order over 500

Note For large datasets, EXISTS often performs better than IN with a subquery because EXISTS short-circuits on the first match. IN materializes the entire subquery result first.

Derived Table (Subquery in FROM)

SQL · Subqueries
Syntax
SELECT columns
FROM (SELECT ... FROM ...) AS alias;
Example
SELECT
  top_customers.first_name,
  top_customers.total_spent
FROM (
  SELECT u.first_name, SUM(o.total_amount) AS total_spent
  FROM users u
  JOIN orders o ON o.user_id = u.id
  GROUP BY u.id, u.first_name
  HAVING SUM(o.total_amount) > 1000
) AS top_customers
ORDER BY top_customers.total_spent DESC;
Output
-- first_name | total_spent
-- Alice      | 4520.00
-- Bob        | 2310.50

Note Derived tables (subqueries in FROM) must have an alias. They are computed once and treated like a temporary table. CTEs (WITH clause) are generally more readable for the same purpose.

Subquery with ANY / ALL

SQL · Subqueries
Syntax
WHERE column > ANY (subquery)
WHERE column > ALL (subquery)
Example
SELECT first_name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary
  FROM employees
  WHERE department_id = 3
);
Output
-- Employees earning more than everyone in department 3

Note ANY means 'at least one' - the condition must hold for at least one row from the subquery. ALL means 'every' - it must hold for all rows. An empty subquery makes ALL true and ANY false.

Frequently asked questions

How does SQL handle in subquery?
SQL covers this with 3 copy-ready snippets on this page. The "Subquery in WHERE with IN" snippet in SQL uses `WHERE column IN (SELECT column FROM ...)`.
Which statement does the SQL example use?
The "Subquery in WHERE with IN" snippet uses `WHERE column IN (SELECT column FROM ...)`, from the Subqueries section of the SQL cheat sheet.
What other SQL snippets are shown for "in subquery"?
Besides "Subquery in WHERE with IN", this page also shows "Derived Table (Subquery in FROM)", "Subquery with ANY / ALL".
Is there anything to watch out for?
Yes. For "Subquery in WHERE with IN": For large datasets, EXISTS often performs better than IN with a subquery because EXISTS short-circuits on the first match. IN materializes the entire subquery result first.

Browse all tasks by intent