Subqueries and CTEs help you write cleaner, modular, and more powerful SQL queries. They're often asked in interviews!
1๏ธโฃ Subqueries (Nested Queries)
A query inside another query.
Example:
SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
๐ Use case: Find employees earning above average.
Types:
โฆ In SELECT
โฆ In WHERE
โฆ In FROM (Inline Views)
2๏ธโฃ Correlated Subqueries
Inner query depends on outer query.
Example:
SELECT name
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);
๐ Use case: Find employees earning above average in their department.
3๏ธโฃ Common Table Expressions (CTE)
Temporary result set using WITH. Improves readability.
Example:
WITH high_paid AS (
SELECT name, salary FROM employees WHERE salary > 100000
)
SELECT * FROM high_paid;
๐ Use case: Simplify complex queries, recursive queries.
4๏ธโฃ Recursive CTE
Used for hierarchical data (e.g. org charts, folders).
Example:
WITH RECURSIVE emp_tree AS (
SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN emp_tree et ON e.manager_id = et.id
)
SELECT * FROM emp_tree;
๐ง Interview Questions
Q1: When should you use a subquery vs JOIN?
A: Use subquery when working with aggregates or filtering logic. JOINs are better for combining related data.
Q2: What's the difference between subquery and CTE?
A: Subquery is inline; CTE improves readability and can be reused in the query.
Q3: What is a correlated subquery?
A: It depends on data from the outer query. Runs row by row.
Q4: When do you use recursive CTEs?
A: For hierarchical/parent-child relationships like org charts, file systems.
Q5: Can subqueries be used in the FROM clause?
A: Yes, they're called derived tables or inline views.
๐ฌ Double Tap โค๏ธ for more!