17

CHAPTER 17

Subqueries

Queries within queries — nesting for power.

7

Learning Blocks

SQL

Interactive Queries

In This Chapter

Concepts you'll master

Subquery in WHERE to filter on a computed value

Subquery with IN for list-based filtering

Subquery in FROM as a derived table

EXISTS — checking if a subquery returns any rows

🌟 Think of it this way: Subqueries are like asking two questions where the answer to the first feeds the second: 'What is the average salary?' → ₹70,125. 'Who earns more than ₹70,125?' Both steps happen in one SQL statement.

15.1 - Subquery in WHERE

SQL QUERY

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

15.2 - Subquery in IN

SQL QUERY

SELECT name FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders
  WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
);

15.3 - EXITS

EXISTS is faster than IN for large subqueries. It short-circuits as soon as one matching row is found:

SQL QUERY

SELECT c.name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

💡 Engineering Insight: EXISTS vs IN performance: EXISTS stops scanning as soon as it finds the first match. IN collects every matching ID first. On tables with millions of rows, EXISTS can be 10-100x faster. Use EXISTS when you only care 'does a matching row exist?' not 'how many?'

Exercise

Practice your SQL skills

SQL EDITOR

Interactive Query Environment

Press semicolon (;) to auto-run query

Tasks

Complete all SQL challenges

Get the name and price of products whose price is greater than the average price of all products.

Current Task

Find customers who have not placed any orders.

Find products that have been ordered with quantity greater than 1.

Find customers who have orders using EXISTS.

Find customers whose total order amount is greater than 50000.

Find products whose price is greater than the average price of all products.

Need help solving this task?