SQL - Subqueries & EXISTS

Overview

Subqueries return intermediate results for use in a larger query. EXISTS checks for existence efficiently.

-- IN subquery
SELECT first_name FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE name='Engineering');

-- EXISTS
SELECT first_name FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id=e.department_id);

-- Scalar subquery
SELECT first_name,
  (SELECT name FROM departments d WHERE d.id=e.department_id) AS department
FROM employees e;

-- IN subquery
SELECT first_name FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE name=N'Engineering');

-- EXISTS
SELECT first_name FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id=e.department_id);

-- Scalar subquery
SELECT first_name,
  (SELECT name FROM departments d WHERE d.id=e.department_id) AS department
FROM employees e;

-- IN subquery
SELECT first_name FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE name='Engineering');

-- EXISTS
SELECT first_name FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id=e.department_id);

-- Scalar subquery
SELECT first_name,
  (SELECT name FROM departments d WHERE d.id=e.department_id) AS department
FROM employees e;

Expected Output (live)

first_name
Ada
Bob
Eli

Sample Use Cases (3)

1) Customers with orders (EXISTS)

name
Alice
Ben
Cara

2) Products appearing in order_items (IN)

name
Coffee Beans
Laptop Pro
Office Chair
Standing Desk
Wireless Mouse

3) Department name via scalar subquery

first_name	department
Ada	Engineering
Bob	Engineering
Chen	HR
Dee	Sales
Eli	Engineering

« Prev: SQL - UNION Next: SQL - Alter Table »