SQL Subqueries

A subquery is a query inside another query. It’s used to fetch intermediate results for comparison, filtering, or transformation.

Types of subqueries:

• Scalar Subquery → returns a single value
• Row Subquery → returns a single row
• Table Subquery → returns a full result set (multiple rows & columns)

🔹 Scalar Subquery – One Value

Used where a single value is expected (e.g., in SELECT, WHERE, SET).

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

✅ Filters employees earning above average salary.

🔹 Row Subquery – One Row, Multiple Columns

Used to compare a row with another row.

SELECT employee_id, first_name
FROM employees
WHERE (department_id, job_id) = (
  SELECT department_id, job_id
  FROM employees
  WHERE employee_id = 101
);

✅ Returns employees who share both department and job with employee 101.

🔹 Table Subquery – Used in FROM

Returns a full table-like result that can be queried.

SELECT dept_name, emp_count
FROM (
  SELECT department_id, COUNT(*) AS emp_count
  FROM employees
  GROUP BY department_id
) AS dept_summary
JOIN departments d ON d.department_id = dept_summary.department_id;

✅ You can treat the subquery as a temporary table.

🔹 Subquery in SELECT

SELECT
  employee_name,
  (SELECT department_name FROM departments d WHERE d.department_id = e.department_id) AS dept_name
FROM employees e;

🧠 Quick Recap

💡 Subqueries add power and flexibility — ideal for comparisons and reusable logic.