SQL — Joins, Aggregations, Subqueries

In simple words

A database keeps your data spread across several tables — for example one table of customers and another of their orders. SQL is the language you use to ask questions across those tables: 'show me every order Alice placed this month, and how much she spent in total.'

This note covers the three things you do all the time: joining tables together, adding things up per group (like a total per customer), and using one query's answer inside another.

What is SQL?

SQL (Structured Query Language) is the standard language for interacting with relational databases — querying, inserting, updating, and deleting data.

DDL → Data Definition Language   → CREATE, ALTER, DROP  (structure)
DML → Data Manipulation Language → SELECT, INSERT, UPDATE, DELETE  (data)
DCL → Data Control Language      → GRANT, REVOKE  (permissions)
TCL → Transaction Control        → COMMIT, ROLLBACK  (transactions)

For system design interviews, focus on SELECT — the most complex and tested operation.

Tables Used Throughout This File

users                               orders
┌─────────┬───────┬───────────┐     ┌──────────┬─────────┬────────┬───────────┐
│ user_id │ name  │ city      │     │ order_id │ user_id │ amount │ status    │
├─────────┼───────┼───────────┤     ├──────────┼─────────┼────────┼───────────┤
│ 1       │ Alice │ Mumbai    │     │ 1001     │ 1       │ 500    │ DELIVERED │
│ 2       │ Bob   │ Delhi     │     │ 1002     │ 1       │ 200    │ SHIPPED   │
│ 3       │ Carol │ Bangalore │     │ 1003     │ 2       │ 750    │ PENDING   │
│ 4       │ Dave  │ Mumbai    │     │ 1004     │ 2       │ 300    │ DELIVERED │
│ 5       │ Eve   │ Chennai   │     │ 1005     │ 3       │ 100    │ CANCELLED │
└─────────┴───────┴───────────┘     └──────────┴─────────┴────────┴───────────┘

products                            order_items
┌────────────┬──────────┬───────┐   ┌─────────┬──────────┬────────────┬──────┬───────┐
│ product_id │ name     │ price │   │ item_id │ order_id │ product_id │ qty  │ price │
├────────────┼──────────┼───────┤   ├─────────┼──────────┼────────────┼──────┼───────┤
│ P01        │ Shoes    │ 500   │   │ 1       │ 1001     │ P01        │ 1    │ 500   │
│ P02        │ T-shirt  │ 200   │   │ 2       │ 1002     │ P02        │ 1    │ 200   │
│ P03        │ Watch    │ 1500  │   │ 3       │ 1003     │ P03        │ 1    │ 1500  │
│ P04        │ Bag      │ 800   │   │ 4       │ 1004     │ P02        │ 2    │ 400   │
└────────────┴──────────┴───────┘   └─────────┴──────────┴────────────┴──────┴───────┘

Note: user_id 4 (Dave) and user_id 5 (Eve) have NO orders — important for JOIN examples.

SELECT Basics (Quick Reference)

-- Basic structure
SELECT column1, column2        -- what to return
FROM table_name                -- where to look
WHERE condition                -- filter rows
ORDER BY column ASC|DESC       -- sort
LIMIT n OFFSET m;              -- paginate

-- Example
SELECT name, city
FROM users
WHERE city = 'Mumbai'
ORDER BY name ASC
LIMIT 10;
-- Returns: Alice, Dave (both in Mumbai)

-- Aliases
SELECT u.name AS customer_name, o.amount AS order_value
FROM users u                   -- 'u' is alias for users
JOIN orders o ON u.user_id = o.user_id;

JOINS — The Most Important SQL Concept for Interviews

A JOIN combines rows from two or more tables based on a related column.

INNER JOIN

Returns rows where there is a match in BOTH tables. Non-matching rows from either side are excluded.

SELECT u.name, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;

Result:
┌───────┬──────────┬────────┐
│ name  │ order_id │ amount │
├───────┼──────────┼────────┤
│ Alice │ 1001     │ 500    │
│ Alice │ 1002     │ 200    │
│ Bob   │ 1003     │ 750    │
│ Bob   │ 1004     │ 300    │
│ Carol │ 1005     │ 100    │
└───────┴──────────┴────────┘

Dave and Eve are NOT here — they have no orders.
INNER JOIN excludes them.

Venn diagram:
   users  ∩  orders
   Only the overlapping part.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the LEFT table, and matching rows from the right. If no match on the right, fills with NULL.

SELECT u.name, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;

Result:
┌───────┬──────────┬────────┐
│ name  │ order_id │ amount │
├───────┼──────────┼────────┤
│ Alice │ 1001     │ 500    │
│ Alice │ 1002     │ 200    │
│ Bob   │ 1003     │ 750    │
│ Bob   │ 1004     │ 300    │
│ Carol │ 1005     │ 100    │
│ Dave  │ NULL     │ NULL   │ ← Dave has no orders — still included
│ Eve   │ NULL     │ NULL   │ ← Eve has no orders — still included
└───────┴──────────┴────────┘

Use case: "Get all users and their orders — include users who haven't ordered yet." Classic for dashboards, user activity reports.

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the RIGHT table, matching from the left. Mirror of LEFT JOIN. Rarely used — most developers rewrite as LEFT JOIN.

-- Equivalent: all orders, include user info if available
SELECT u.name, o.order_id, o.amount
FROM users u
RIGHT JOIN orders o ON u.user_id = o.user_id;
-- Returns all 5 orders + user name. If an order had no user (shouldn't happen with FK),
-- user columns would be NULL.

FULL OUTER JOIN

Returns all rows from BOTH tables, NULLs where no match.

SELECT u.name, o.order_id
FROM users u
FULL OUTER JOIN orders o ON u.user_id = o.user_id;
-- All users (including Dave, Eve with NULL orders)
-- All orders (including any order with no matching user)

MySQL doesn't support FULL OUTER JOIN directly. Emulate with: LEFT JOIN UNION RIGHT JOIN

SELF JOIN

A table joined to itself. Used for hierarchical data.

-- employees table with manager_id pointing to another employee
CREATE TABLE employees (
    emp_id     INT PRIMARY KEY,
    name       VARCHAR(100),
    manager_id INT,    -- FK to emp_id in same table
    FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);

-- Find each employee with their manager's name
SELECT
    e.name        AS employee,
    m.name        AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

Result:
┌──────────┬─────────┐
│ employee │ manager │
├──────────┼─────────┤
│ Alice    │ NULL    │  ← Alice is CEO, no manager
│ Bob      │ Alice   │
│ Carol    │ Alice   │
│ Dave     │ Bob     │
└──────────┴─────────┘

CROSS JOIN

Returns the Cartesian product — every row from A combined with every row from B. Rarely used in production. A × B rows.

-- 5 users × 4 products = 20 rows
SELECT u.name, p.name AS product
FROM users u
CROSS JOIN products p;
-- Use case: generate all possible combinations (e.g. all seat × flight combinations)

JOIN Summary Table

Multiple JOINs (Real Interview Scenario)

-- "Get each user's name, their order ID, and each product name they ordered"
SELECT
    u.name          AS customer,
    o.order_id,
    p.name          AS product,
    oi.quantity,
    oi.price
FROM users u
INNER JOIN orders o     ON u.user_id    = o.user_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p   ON oi.product_id = p.product_id
ORDER BY u.name, o.order_id;

AGGREGATIONS

Aggregate functions perform calculations across a group of rows and return one value per group.

Core Aggregate Functions

SELECT
    COUNT(*)             AS total_orders,      -- count all rows
    COUNT(user_id)       AS non_null_users,    -- count non-null values
    SUM(amount)          AS total_revenue,     -- sum
    AVG(amount)          AS avg_order_value,   -- average
    MIN(amount)          AS smallest_order,    -- minimum
    MAX(amount)          AS largest_order      -- maximum
FROM orders;

Result:
┌──────────────┬────────────────┬───────────────┬─────────────────┬───────────────┬───────────────┐
│ total_orders │ non_null_users │ total_revenue │ avg_order_value │ smallest_order│ largest_order │
├──────────────┼────────────────┼───────────────┼─────────────────┼───────────────┼───────────────┤
│ 5            │ 5              │ 1850          │ 370.00          │ 100           │ 750           │
└──────────────┴────────────────┴───────────────┴─────────────────┴───────────────┴───────────────┘

GROUP BY — Aggregate Per Group

Without GROUP BY, aggregates work on the entire table. GROUP BY splits the table into groups and applies the aggregate to each group.

-- Total spent per user
SELECT
    u.name,
    COUNT(o.order_id)  AS total_orders,
    SUM(o.amount)      AS total_spent
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name
ORDER BY total_spent DESC;

Result:
┌───────┬──────────────┬─────────────┐
│ name  │ total_orders │ total_spent │
├───────┼──────────────┼─────────────┤
│ Bob   │ 2            │ 1050        │
│ Alice │ 2            │ 700         │
│ Carol │ 1            │ 100         │
│ Dave  │ 0            │ NULL        │
│ Eve   │ 0            │ NULL        │
└───────┴──────────────┴─────────────┘

Rule: Every column in SELECT that is NOT an aggregate must appear in GROUP BY.

HAVING — Filter After Grouping

WHERE filters rows BEFORE grouping. HAVING filters groups AFTER aggregation.

-- Find users who have spent more than ₹500 total
SELECT
    u.name,
    SUM(o.amount) AS total_spent
FROM users u
JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name
HAVING SUM(o.amount) > 500   -- filter on the aggregated result
ORDER BY total_spent DESC;

Result:
┌───────┬─────────────┐
│ name  │ total_spent │
├───────┼─────────────┤
│ Bob   │ 1050        │
│ Alice │ 700         │
└───────┴─────────────┘
-- Carol (100) and users with no orders are excluded

Execution order (important for interviews):
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

WHERE vs HAVING — The Classic Interview Trap

-- ❌ WRONG — cannot use aggregate in WHERE
SELECT user_id, SUM(amount)
FROM orders
WHERE SUM(amount) > 500     -- error: aggregates not allowed in WHERE
GROUP BY user_id;

-- ✅ CORRECT — use HAVING for aggregate filters
SELECT user_id, SUM(amount)
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 500;

-- ✅ WHERE filters individual rows BEFORE grouping
SELECT user_id, SUM(amount)
FROM orders
WHERE status != 'CANCELLED'   -- filter out cancelled orders first
GROUP BY user_id
HAVING SUM(amount) > 500;     -- then filter groups by total

SUBQUERIES

A subquery is a SELECT statement nested inside another query.

1. Subquery in WHERE Clause

-- Find users who have placed at least one order
SELECT name
FROM users
WHERE user_id IN (
    SELECT DISTINCT user_id FROM orders  -- subquery returns a list of user_ids
);
-- Returns: Alice, Bob, Carol

-- Find users who have NEVER placed an order
SELECT name
FROM users
WHERE user_id NOT IN (
    SELECT DISTINCT user_id FROM orders
);
-- Returns: Dave, Eve

-- Find orders above the average order value
SELECT order_id, amount
FROM orders
WHERE amount > (
    SELECT AVG(amount) FROM orders   -- subquery returns single value: 370
);
-- Returns orders with amount > 370: order 1001 (500), 1003 (750)

2. Subquery in FROM Clause (Derived Table)

-- Use a subquery as a temporary table
SELECT name, total_spent
FROM (
    SELECT u.name, SUM(o.amount) AS total_spent
    FROM users u
    JOIN orders o ON u.user_id = o.user_id
    GROUP BY u.user_id, u.name
) AS user_totals               -- derived table must have an alias
WHERE total_spent > 500;

3. Correlated Subquery

A subquery that references a column from the outer query. Runs once per row of the outer query — can be slow on large tables.

-- Find users whose latest order is more than their average
SELECT u.name, o.amount, o.order_id
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.amount > (
    SELECT AVG(o2.amount)
    FROM orders o2
    WHERE o2.user_id = u.user_id   -- references outer query's u.user_id
);
-- For each user, the subquery calculates THEIR average and compares

CTEs — Common Table Expressions (WITH Clause)

CTEs make complex queries readable by naming intermediate results. Think of them as temporary named subqueries at the top of your SQL.

-- Find the top spending user per city
WITH user_totals AS (
    SELECT
        u.user_id,
        u.name,
        u.city,
        SUM(o.amount) AS total_spent
    FROM users u
    JOIN orders o ON u.user_id = o.user_id
    GROUP BY u.user_id, u.name, u.city
),
city_max AS (
    SELECT city, MAX(total_spent) AS max_spent
    FROM user_totals
    GROUP BY city
)
SELECT ut.name, ut.city, ut.total_spent
FROM user_totals ut
JOIN city_max cm ON ut.city = cm.city AND ut.total_spent = cm.max_spent
ORDER BY ut.city;

Why CTEs over nested subqueries:

• Readable — each step has a name
• Reusable — reference the same CTE multiple times
• Debuggable — test each step independently

Classic Interview SQL Questions

1. Second Highest Salary / N-th Highest

-- Second highest order amount
SELECT MAX(amount) AS second_highest
FROM orders
WHERE amount < (SELECT MAX(amount) FROM orders);

-- Using DENSE_RANK (cleaner, works for N-th highest)
SELECT amount
FROM (
    SELECT amount, DENSE_RANK() OVER (ORDER BY amount DESC) AS rnk
    FROM orders
) ranked
WHERE rnk = 2;

2. Duplicate Detection

-- Find duplicate emails in users table
SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

3. Users Who Placed Orders on Consecutive Days

WITH ordered AS (
    SELECT user_id, DATE(created_at) AS order_date
    FROM orders
)
SELECT DISTINCT o1.user_id
FROM ordered o1
JOIN ordered o2 ON o1.user_id = o2.user_id
    AND o2.order_date = DATE_ADD(o1.order_date, INTERVAL 1 DAY);

4. Running Total (Window Function)

SELECT
    order_id,
    user_id,
    amount,
    SUM(amount) OVER (
        PARTITION BY user_id
        ORDER BY created_at
    ) AS running_total_per_user
FROM orders;

5. Most Recent Order Per User

-- Without window functions
SELECT o.*
FROM orders o
WHERE o.created_at = (
    SELECT MAX(o2.created_at)
    FROM orders o2
    WHERE o2.user_id = o.user_id
);

-- With window functions (cleaner)
SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
) ranked
WHERE rn = 1;

SQL Execution Order (Must Know for Interviews)

1. FROM           → identify the tables
2. JOIN           → combine tables
3. WHERE          → filter individual rows
4. GROUP BY       → group rows
5. HAVING         → filter groups
6. SELECT         → choose columns, apply aggregates
7. DISTINCT       → remove duplicates
8. ORDER BY       → sort results
9. LIMIT / OFFSET → paginate

Why this matters:
- You cannot use a SELECT alias in WHERE (WHERE runs before SELECT)
- You cannot use aggregates in WHERE (use HAVING)
- You cannot use window functions in WHERE or GROUP BY

System Design Context

SQL in System Design Interviews

When you say "I'll use MySQL for user and order data," an interviewer might ask:

• How would you query the top 10 customers by revenue this month?
• How do you get orders with product details in one query?

-- "Top 10 customers by revenue this month"
SELECT
    u.name,
    SUM(o.amount) AS monthly_revenue
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.created_at >= DATE_FORMAT(NOW(), '%Y-%m-01')   -- first day of current month
  AND o.status = 'DELIVERED'
GROUP BY u.user_id, u.name
ORDER BY monthly_revenue DESC
LIMIT 10;

When SQL Gets Slow (Leads to Indexing — Next Topic)

Heavy JOIN across 10M rows + GROUP BY + ORDER BY = slow query

This is exactly why:
- Indexes exist (Topic 04)
- Query optimization matters (Topic 09)
- Sometimes denormalization is chosen
- Sometimes NoSQL is chosen over SQL (Topic 10)

How This Connects

• Relational Model - Tables, Keys, Relationships — JOINs follow the foreign-key links; the model splits data, SQL rejoins it.
• Indexing - B-Tree & Hash Indexes — a JOIN or WHERE on an unindexed column is a full table scan; the join column on the "many" side almost always needs an index.
• Composite & Covering Indexes — GROUP BY + ORDER BY on the same columns can be served straight from a composite index with no sort step.
• Query Optimization & EXPLAIN Plans — EXPLAIN shows which join algorithm and index the optimiser picked; the classic slow-query patterns (functions on indexed columns, N+1, OFFSET pagination) all show up here.
• Normalization - 1NF to BCNF — normalization is why you need JOINs at all; denormalization trades JOINs for duplicated data.
• Module 4 — Data Architecture — once a table is sharded, cross-shard JOINs stop working, which pushes joins into application code or a denormalized read model.

In the Interview

When to say it. Whenever you claim "I'll store this in a relational DB", expect a follow-up asking you to write or explain a query — top-N by revenue, count per group, "users who never did X".

The ~20-second version:

"INNER JOIN returns only rows that match in both tables — users with no orders disappear. LEFT JOIN keeps every row from the left table and fills NULLs where the right side has no match, so 'all users and their order count' must be a LEFT JOIN or you silently drop users who never ordered and your totals are wrong. Filtering is split by the execution order: WHERE runs before GROUP BY so it filters individual rows; HAVING runs after, so aggregate conditions like SUM(amount) > 500 go there. For anything multi-step I use CTEs so each stage is named and testable."

Follow-ups to expect:

1. "Why can't I use a SELECT alias in WHERE?" — WHERE is evaluated before SELECT, so the alias doesn't exist yet. Repeat the expression, or wrap it in a subquery/CTE.
2. "INNER JOIN + a WHERE on the right table — what's the trap?" — Putting the right-table condition in WHERE (not the ON clause) on a LEFT JOIN turns it back into an INNER JOIN, because NULL rows fail the WHERE.
3. "Second-highest salary?" — DENSE_RANK() OVER (ORDER BY salary DESC) then filter = 2; or the correlated MAX(x) WHERE x < (SELECT MAX(x)).
4. "Correlated subquery vs JOIN?" — A correlated subquery re-runs per outer row (O(n·m)); the optimiser can often rewrite it, but a plain JOIN or window function is usually clearer and faster.
5. "NOT IN vs NOT EXISTS?" — NOT IN returns zero rows if the subquery yields a single NULL; NOT EXISTS / LEFT JOIN ... IS NULL is NULL-safe.

Mistake to avoid: writing WHERE SUM(amount) > 500. Aggregates don't exist at WHERE time — that condition belongs in HAVING (or filter the raw rows in WHERE first, then the groups in HAVING).

Quick Revision — 7 Lines

1. INNER JOIN = only matched rows. LEFT JOIN = all from left + NULLs where no match
2. SELF JOIN = table joined to itself — used for hierarchical data (org charts)
3. Execution order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
4. WHERE filters rows (before grouping). HAVING filters groups (after aggregation)
5. Subquery in WHERE → filter with results of another query
6. Correlated subquery → references outer query column, runs per row — can be slow
7. CTE (WITH clause) → named temporary query — readable, reusable, debuggable