SQL Subqueries

by




💻 Introduction

A subquery is a query inside another query. It helps retrieve results that depend on other queries. Subqueries can return single values, multiple rows, or be correlated with the main query.

1️⃣ Single-row Subquery

Returns a single value (used with =, <, >).

SELECT first_name, salary
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);

2️⃣ Multi-row Subquery

Returns multiple rows (used with IN, ANY, ALL).

SELECT first_name, department
FROM Employees
WHERE department IN (SELECT department FROM Departments WHERE location = 'New York');

3️⃣ Correlated Subquery

Executes once for every row in the outer query.

SELECT e.first_name, e.salary
FROM Employees e
WHERE salary > (
  SELECT AVG(salary) 
  FROM Employees 
  WHERE department = e.department
);
Tip: Use correlated subqueries carefully—they may impact performance on large datasets.

✅ Key Takeaways

  • Single-row: Returns one value (use =, <, >)
  • Multi-row: Returns multiple values (use IN, ANY, ALL)
  • Correlated: Executes row-by-row with outer query

📚 Continue Learning

💬 Got a tricky subquery? Drop it in the comments and we’ll solve it together!




Leave a Comment