Definition of Subqueries

A subquery occurs when one SQL statement contains multiple SELECT clauses. Consider this example:

-- Example introducing subqueries:
-- Find all employees with higher salaries than "JONES"
-- Step 1: Get JONES's salary
SELECT salary FROM staff WHERE name = 'JONES';  -- Returns 2975

-- Step 2: Find all employees with salaries above 2975
SELECT * FROM staff WHERE salary > 2975;

-- Two separate commands - inefficient, second command depends on first
-- Combining both steps into a single query using subquery:
SELECT * FROM staff WHERE salary > (SELECT salary FROM staff WHERE name = 'JONES');
-- Single command - more efficient

Execution Order

Subuqeries execute before the outer query completes.

Independent Subqueries

When a subquery can run independently without depending on values from the outer query, it's called an independent subquery.

Categoreis of Independent Subqueries

Based on result set rows, independent subqueries fall into two categories: single-row and multi-row subqueries.

Single-Row Subqueries

-- Single-row subqueries example:
-- Retrieve employee names and salaries above average
SELECT employee_name, salary
FROM staff
WHERE salary > (SELECT AVG(salary) FROM staff);

-- Find employees in same department as JONES with lower pay
SELECT employee_name, salary
FROM staff
WHERE department_id = (SELECT department_id FROM staff WHERE name = 'JONES') 
      AND 
      salary < (SELECT salary FROM staff WHERE name = 'JONES');

-- Find employees with same position as ALLEN hired earlier
SELECT * 
FROM staff
WHERE position = (SELECT position FROM staff WHERE name = 'ALLEN') 
      AND 
      start_date < (SELECT start_date FROM staff WHERE name = 'ALLEN');

Multi-Row Subqueries

-- Multi-row subqueries:
-- [1] Find employees in department 30 who have roles matching department 20
-- Basic employee query
SELECT * FROM staff;

-- Employees in department 30
SELECT * FROM staff WHERE department_id = 30; -- Includes positions like SALESMAN, MANAGER, ANALYST

-- Positions in department 20:
SELECT position FROM staff WHERE department_id = 20; -- Includes MANAGER, PRESIDENT, CLERK

-- Find employees in department 30 with positions matching department 20
SELECT * FROM staff 
WHERE department_id = 30 
AND position IN (SELECT position FROM staff WHERE department_id = 20);

-- Alternative using ANY operator:
SELECT * FROM staff 
WHERE department_id = 30 
AND position = ANY(SELECT position FROM staff WHERE department_id = 20);

-- [2] Find employees with higher salaries than ALL managers
-- Employee details query
SELECT id, name, salary FROM staff;

-- Manager salaries:
SELECT salary FROM staff WHERE position = 'MANAGER';

-- Find employees with salaries higher than ALL managers
-- Using multi-row subquery:
SELECT id, name, salary 
FROM staff 
WHERE salary > ALL(SELECT salary FROM staff WHERE position = 'MANAGER');

-- Equivalent single-row approach:
SELECT id, name, salary 
FROM staff 
WHERE salary > (SELECT MAX(salary) FROM staff WHERE position = 'MANAGER');

-- [3] Find employees with salaries below any ANALYST
-- Employee information query
SELECT * FROM staff;

-- Find employees with salaries below any ANALYST
SELECT * 
FROM staff
WHERE salary < ANY(SELECT salary FROM staff WHERE position = 'ANALYST')
AND position != 'ANALYST';

-- Equivalent single-row version:
SELECT * 
FROM staff
WHERE salary < (SELECT MAX(salary) FROM staff WHERE position = 'ANALYST')
AND position != 'ANALYST';