SQL Subqueries

What is a Subquery?

A subquery is a query inside another SQL query.

The inner query runs first and its result is used by the outer query.

Basic Subquery

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

Subquery with IN

SELECT name
FROM employees
WHERE department_id IN (
  SELECT id
  FROM departments
  WHERE location = 'NY'
);

Subquery in SELECT

SELECT name,
       (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

Subquery in FROM

SELECT *
FROM (
  SELECT name, salary
  FROM employees
) AS temp;

Correlated Subquery

SELECT name
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
);

Full Example

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

Why Subqueries are Important

• Enable advanced filtering
• Compare values dynamically
• Used in real-world queries
• Make complex queries possible

Common Mistakes

• Using subqueries instead of simpler joins
• Returning multiple values when one is expected
• Forgetting parentheses

Practice

Find employees who earn above the average salary.

SELECT name
FROM employees
WHERE salary > (
  -- write subquery here
);

Need Help?

Ask the AI if you need help understanding or want to dive deeper in any topic