Subqueries

Subqueries are queries inside other queries.

Types

• IN � match against subquery results
• EXISTS � check if subquery returns rows
• Correlated � references outer query

Syntax

MYSQL

1
2
3
4
5
6
7
8

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

Subqueries

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- Subquery in WHERE
SELECT name, age
FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- Subquery with IN
SELECT name
FROM users
WHERE id IN (
    SELECT user_id FROM orders WHERE total > 100
);

-- Correlated subquery
SELECT u.name, (
    SELECT COUNT(*)
    FROM orders o
    WHERE o.user_id = u.id
) AS order_count
FROM users u;

-- Subquery in FROM
SELECT city, total FROM (
    SELECT city, COUNT(*) AS total
    FROM users
    GROUP BY city
) AS stats
WHERE total > 5;

Try It Yourself

Practice

1

Exercise

Find users who ordered more than 5 times.

Answer

SELECT name FROM users
WHERE id IN (
    SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) > 5
);

Quick Quiz

1

IN vs EXISTS?

Same

IN for small subqueries, EXISTS for large

EXISTS is always faster

IN is better for small result sets. EXISTS is better when the subquery is large.

Retry Quiz

Interview Questions

When to use subqueries vs JOINs?

Subqueries for readability and when checking existence. JOINs for combining columns from multiple tables or when performance matters.

Next Lesson

Views & Indexes