Back to Joins & Subqueries
Q1Question 1 of 1

How do SQL joins, aggregation, and subqueries work

Answer: SQL joins combine rows from multiple tables based on related columns, aggregation functions compute summary values from groups of rows, and subqueries embed one SELECT inside another to build complex queries.

Key Concepts
  • INNER JOIN returns rows that have matching values in both tables, excluding non-matching rows from either side.
  • LEFT JOIN returns all rows from the left table and matched rows from the right table, with NULLs where no match exists.
  • Aggregation functions (COUNT, SUM, AVG, MIN, MAX) compute a single value from a group of rows.
  • GROUP BY divides rows into groups and applies aggregation functions to each group independently.
  • HAVING filters groups after aggregation, while WHERE filters individual rows before aggregation.
  • A subquery is a SELECT inside another SQL statement, used for filtering, computing values, or creating derived tables.
  • A correlated subquery references columns from the outer query and executes once per outer row.

Definition

SQL joins combine rows from multiple tables based on related columns, aggregation functions compute summary values from groups of rows, and subqueries embed one SELECT inside another to build complex queries.
  • JOIN combines rows from two or more tables based on a condition, typically matching a foreign key to a primary key.
  • INNER JOIN returns only rows that match in both tables. Rows without a match in either table are excluded.
  • LEFT JOIN returns all rows from the left table and matched rows from the right. Unmatched right-side rows appear as NULLs.
  • Aggregation functions (COUNT, SUM, AVG, MIN, MAX) compute a single result from a set of rows.
  • Subquery is a SELECT statement nested inside another SQL statement, used for filtering, comparison, or as a derived table.

Think of preparing a monthly report at National Bank of India. Joining is like placing the customer register next to the account ledger and matching rows by customer ID. If a customer has no account, the inner join skips them, while the left join keeps them with blank account columns. Aggregation is like the branch manager counting total deposits for each account. Grouping is like organizing the report by branch or by account type. A subquery is like looking up the average balance in a separate calculation, then using that number to filter the main report.

Understanding the Concepts

What is a JOIN?

A JOIN combines rows from two or more tables based on a related column, typically a foreign key referencing a primary key. Without joins, relational data stored in normalized tables would be inaccessible as connected information.

SQL
-- Setup: sample data for examples
INSERT INTO customers (customer_id, full_name, email, pan_number, date_of_birth) VALUES
(1, 'Rajesh Kumar', 'rajesh@email.com', 'ABCPK1234A', '1990-05-15'),
(2, 'Priya Sharma', 'priya@email.com', 'DEFPS5678B', '1992-08-22'),
(3, 'Pankaj Gupta', 'pankaj@email.com', 'GHIPG9012C', '1985-03-10');

INSERT INTO accounts (account_id, customer_id, branch_id, account_type, balance) VALUES
('SAV2001', 1, 1, 'SAVINGS', 75000.00),
('SAV2002', 2, 1, 'SAVINGS', 120000.00),
('CUR3001', 1, 1, 'CURRENT', 250000.00);
-- Note: Pankaj (customer_id=3) has no account.

INSERT INTO transactions (account_id, txn_type, amount, description, created_at) VALUES
('SAV2001', 'CREDIT', 50000.00, 'Salary deposit', '2025-01-05'),
('SAV2001', 'DEBIT',  5000.00,  'ATM withdrawal', '2025-01-10'),
('SAV2001', 'CREDIT', 30000.00, 'Freelance payment', '2025-01-15'),
('SAV2002', 'CREDIT', 100000.00, 'Salary deposit', '2025-01-05'),
('SAV2002', 'DEBIT',  20000.00,  'Online shopping', '2025-01-12'),
('CUR3001', 'CREDIT', 200000.00, 'Business revenue', '2025-01-03'),
('CUR3001', 'DEBIT',  50000.00,  'Vendor payment', '2025-01-08');
TEXT
-- Sample data loaded successfully.

How does INNER JOIN work?

INNER JOIN returns only the rows that have a matching value in both tables. If a customer has no account, that customer is excluded from the result.

SQL
-- INNER JOIN: Customers with their accounts
SELECT
    c.full_name,
    a.account_id,
    a.account_type,
    a.balance
FROM customers c
INNER JOIN accounts a ON a.customer_id = c.customer_id
ORDER BY c.full_name, a.account_id;
TEXT
 full_name      | account_id | account_type | balance
----------------+------------+--------------+-----------
 Priya Sharma   | SAV2002    | SAVINGS      | 120000.00
 Rajesh Kumar   | CUR3001    | CURRENT      | 250000.00
 Rajesh Kumar   | SAV2001    | SAVINGS      |  75000.00

Notice that Pankaj Gupta (customer_id=3) does not appear because he has no account.

How does LEFT JOIN work?

LEFT JOIN returns all rows from the left table. If there is no matching row in the right table, the right-side columns are NULL.

SQL
-- LEFT JOIN: All customers, even those without accounts
SELECT
    c.full_name,
    a.account_id,
    a.balance
FROM customers c
LEFT JOIN accounts a ON a.customer_id = c.customer_id
ORDER BY c.full_name;
TEXT
 full_name      | account_id | balance
----------------+------------+-----------
 Pankaj Gupta   | NULL       |      NULL
 Priya Sharma   | SAV2002    | 120000.00
 Rajesh Kumar   | CUR3001    | 250000.00
 Rajesh Kumar   | SAV2001    |  75000.00

Pankaj appears with NULL account columns because the LEFT JOIN keeps all left-side rows.

How do you compare INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?

Join TypeLeft Table RowsRight Table RowsUse Case
INNER JOINOnly matchingOnly matchingCustomers with accounts
LEFT JOINAllOnly matching (NULL if no match)All customers, show accounts if any
RIGHT JOINOnly matching (NULL if no match)AllAll accounts, show customer if linked
FULL OUTER JOINAllAll (NULL on both sides if no match)Complete picture of both tables
CROSS JOINEvery row paired with every right rowCartesian productRare; generating test combinations
SQL
-- FULL OUTER JOIN: Shows everything from both tables
SELECT
    c.full_name,
    a.account_id
FROM customers c
FULL OUTER JOIN accounts a ON a.customer_id = c.customer_id
ORDER BY c.full_name;
TEXT
 full_name      | account_id
----------------+-----------
 Pankaj Gupta   | NULL
 Priya Sharma   | SAV2002
 Rajesh Kumar   | CUR3001
 Rajesh Kumar   | SAV2001

How do aggregation functions work?

Aggregation functions compute a single value from a set of rows.

FunctionPurposeExample
COUNT(*)Number of rowsTotal transactions
SUM(column)Total of valuesSum of all deposits
AVG(column)Average valueAverage account balance
MIN(column)Smallest valueMinimum transaction amount
MAX(column)Largest valueMaximum account balance
SQL
-- Aggregation: Summary of all transactions
SELECT
    COUNT(*)           AS total_transactions,
    SUM(amount)        AS total_amount,
    AVG(amount)        AS avg_amount,
    MIN(amount)        AS min_amount,
    MAX(amount)        AS max_amount
FROM transactions;
TEXT
 total_transactions | total_amount | avg_amount   | min_amount | max_amount
--------------------+--------------+--------------+------------+-----------
                  7 |    455000.00 |  65000.000000|    5000.00 | 200000.00

How does GROUP BY work?

GROUP BY divides rows into groups based on one or more columns. Aggregation functions then compute results for each group independently.

SQL
-- Total deposits and withdrawals per account
SELECT
    a.account_id,
    c.full_name,
    COUNT(t.txn_id)    AS transaction_count,
    SUM(CASE WHEN t.txn_type = 'CREDIT' THEN t.amount ELSE 0 END) AS total_credits,
    SUM(CASE WHEN t.txn_type = 'DEBIT'  THEN t.amount ELSE 0 END) AS total_debits
FROM accounts a
JOIN customers c    ON c.customer_id = a.customer_id
JOIN transactions t ON t.account_id = a.account_id
GROUP BY a.account_id, c.full_name
ORDER BY total_credits DESC;
TEXT
 account_id | full_name     | transaction_count | total_credits | total_debits
------------+---------------+-------------------+---------------+-------------
 CUR3001    | Rajesh Kumar  |                 2 |     200000.00 |     50000.00
 SAV2002    | Priya Sharma  |                 2 |     100000.00 |     20000.00
 SAV2001    | Rajesh Kumar  |                 3 |      80000.00 |      5000.00

How is HAVING different from WHERE?

WHERE filters individual rows before grouping. HAVING filters groups after aggregation.

SQL
-- Accounts with more than 2 transactions (HAVING)
SELECT
    t.account_id,
    COUNT(*)    AS txn_count,
    SUM(amount) AS total_amount
FROM transactions t
WHERE t.created_at >= '2025-01-01'   -- WHERE: filter rows first
GROUP BY t.account_id
HAVING COUNT(*) > 2                   -- HAVING: filter groups after
ORDER BY txn_count DESC;
TEXT
 account_id | txn_count | total_amount
------------+-----------+-------------
 SAV2001    |         3 |     85000.00

Only SAV2001 appears because it has 3 transactions. SAV2002 and CUR3001 have only 2 transactions each and are filtered out by HAVING.

What is a subquery?

A subquery is a SELECT statement nested inside another SQL statement. It can appear in the WHERE clause, FROM clause, or SELECT clause.

Scalar subquery (returns one value)

SQL
-- Accounts with balance above the average
SELECT
    account_id,
    balance
FROM accounts
WHERE balance > (
    SELECT AVG(balance) FROM accounts
)
ORDER BY balance DESC;
TEXT
 account_id | balance
------------+-----------
 CUR3001    | 250000.00

The average balance is (75000 + 120000 + 250000) / 3 = 148333.33. Only CUR3001 exceeds this.

IN subquery (returns a list)

SQL
-- Customers who made at least one debit transaction
SELECT full_name
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT a.customer_id
    FROM accounts a
    JOIN transactions t ON t.account_id = a.account_id
    WHERE t.txn_type = 'DEBIT'
);
TEXT
 full_name
--------------
 Rajesh Kumar
 Priya Sharma

Correlated subquery (references the outer query)

A correlated subquery executes once for each row of the outer query. It references a column from the outer query.

SQL
-- Each account with its most recent transaction date
SELECT
    a.account_id,
    a.balance,
    (
        SELECT MAX(t.created_at)
        FROM transactions t
        WHERE t.account_id = a.account_id  -- references outer query
    ) AS last_txn_date
FROM accounts a
ORDER BY a.account_id;
TEXT
 account_id | balance    | last_txn_date
------------+------------+---------------------
 CUR3001    | 250000.00  | 2025-01-08 00:00:00
 SAV2001    |  75000.00  | 2025-01-15 00:00:00
 SAV2002    | 120000.00  | 2025-01-12 00:00:00

How do you write a monthly banking statement query?

This is a real-world banking report that combines joins, aggregation, and subqueries.

SQL
-- Monthly statement for account SAV2001 in January 2025
SELECT
    t.created_at::date   AS txn_date,
    t.txn_type,
    t.amount,
    t.description,
    SUM(
        CASE WHEN t.txn_type = 'CREDIT' THEN t.amount
             ELSE -t.amount END
    ) OVER (ORDER BY t.created_at) AS running_balance
FROM transactions t
WHERE t.account_id = 'SAV2001'
  AND t.created_at >= '2025-01-01'
  AND t.created_at <  '2025-02-01'
ORDER BY t.created_at;
TEXT
 txn_date   | txn_type | amount   | description       | running_balance
------------+----------+----------+-------------------+----------------
 2025-01-05 | CREDIT   | 50000.00 | Salary deposit    |       50000.00
 2025-01-10 | DEBIT    |  5000.00 | ATM withdrawal    |       45000.00
 2025-01-15 | CREDIT   | 30000.00 | Freelance payment |       75000.00

The SUM(...) OVER (ORDER BY ...) is a window function that computes a running total without collapsing rows.

What are common mistakes with joins and aggregation?

Mistake 1: Forgetting the ON clause in a JOIN

WRONG: Missing ON clause creates a CROSS JOIN (cartesian product).

SQL
-- WRONG: Missing ON clause. Every customer paired with every account.
SELECT c.full_name, a.account_id
FROM customers c
JOIN accounts a;  -- No ON clause!
TEXT
-- Result: 3 customers x 3 accounts = 9 rows (cartesian product)
 full_name      | account_id
----------------+-----------
 Rajesh Kumar   | SAV2001
 Rajesh Kumar   | SAV2002
 Rajesh Kumar   | CUR3001
 Priya Sharma   | SAV2001
 Priya Sharma   | SAV2002
 ... 9 rows total

CORRECT: Always specify the ON clause.

SQL
-- CORRECT: ON clause matches customers to their accounts
SELECT c.full_name, a.account_id
FROM customers c
JOIN accounts a ON a.customer_id = c.customer_id;
TEXT
 full_name      | account_id
----------------+-----------
 Rajesh Kumar   | SAV2001
 Rajesh Kumar   | CUR3001
 Priya Sharma   | SAV2002

Mistake 2: Using WHERE instead of HAVING for aggregate filters

WRONG: Filtering on an aggregate in WHERE.

SQL
-- WRONG: WHERE cannot filter on COUNT()
SELECT account_id, COUNT(*) AS txn_count
FROM transactions
GROUP BY account_id
WHERE COUNT(*) > 2;  -- ERROR!
TEXT
ERROR: aggregate functions are not allowed in WHERE

CORRECT: Use HAVING for aggregate conditions.

SQL
-- CORRECT: HAVING filters after GROUP BY
SELECT account_id, COUNT(*) AS txn_count
FROM transactions
GROUP BY account_id
HAVING COUNT(*) > 2;

What should a senior or principal engineer know?

- Query execution order. SQL executes in this order: FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. Understanding this order explains why you cannot use a column alias in WHERE but can in ORDER BY.

- Correlated subqueries vs joins. A correlated subquery runs once per outer row and can be slow on large tables. Rewrite it as a JOIN or a CTE (Common Table Expression) for better performance.

- CTEs for readability. Common Table Expressions (WITH clause) let you name intermediate result sets. They make complex queries readable and maintainable.

- Window functions. ROW_NUMBER(), RANK(), SUM() OVER(), and LAG()/LEAD() are essential for banking reports. They compute values across rows without collapsing them like GROUP BY does.

- Lateral joins (PostgreSQL). LATERAL allows a subquery in the FROM clause to reference columns from preceding tables. Use it for "top N per group" queries.

SQL
-- CTE: Top depositor per branch
WITH account_totals AS (
    SELECT
        a.account_id,
        a.customer_id,
        a.branch_id,
        SUM(t.amount) FILTER (WHERE t.txn_type = 'CREDIT') AS total_deposits
    FROM accounts a
    JOIN transactions t ON t.account_id = a.account_id
    GROUP BY a.account_id, a.customer_id, a.branch_id
),
ranked AS (
    SELECT
        at.*,
        c.full_name,
        ROW_NUMBER() OVER (
            PARTITION BY at.branch_id
            ORDER BY at.total_deposits DESC
        ) AS rank
    FROM account_totals at
    JOIN customers c ON c.customer_id = at.customer_id
)
SELECT full_name, account_id, total_deposits
FROM ranked
WHERE rank = 1;
TEXT
 full_name     | account_id | total_deposits
---------------+------------+---------------
 Rajesh Kumar  | CUR3001    |     200000.00

What are common interview follow-up questions?

  • What is the difference between INNER JOIN and LEFT JOIN?
  • When would you use a FULL OUTER JOIN?
  • Can you have a WHERE and HAVING in the same query?
  • What is the difference between a correlated and a non-correlated subquery?
  • How do window functions differ from GROUP BY?
  • What is a CTE and when do you use it?
  • How do you find the top 3 accounts by balance per branch?
  • What is the execution order of a SQL query?

Visual Overview

TEXT
  JOIN Types Visual
  =================

  Table A (customers)        Table B (accounts)
  +----+----------+          +------+------+
  | id | name     |          | id   | cust |
  +----+----------+          +------+------+
  |  1 | Rajesh   |          | SAV1 |    1 |
  |  2 | Priya    |          | SAV2 |    2 |
  |  3 | Pankaj   |          | CUR1 |    1 |
  +----+----------+          +------+------+

  INNER JOIN (matching only)
  Rajesh  - SAV1, CUR1
  Priya   - SAV2
  (Pankaj excluded: no account)

  LEFT JOIN (all from A)
  Rajesh  - SAV1, CUR1
  Priya   - SAV2
  Pankaj  - NULL

  RIGHT JOIN (all from B)
  Rajesh  - SAV1, CUR1
  Priya   - SAV2
TEXT
  SQL Execution Order
  ====================

  1. FROM + JOIN   (build the table)
  2. WHERE         (filter rows)
  3. GROUP BY      (create groups)
  4. HAVING        (filter groups)
  5. SELECT        (pick columns)
  6. ORDER BY      (sort results)
  7. LIMIT/OFFSET  (paginate)

Key Takeaway

Joins are the bridge between normalized tables. When customer data lives in one table and account data lives in another, an INNER JOIN on customer_id brings them together. A LEFT JOIN keeps all rows from the left table even when there is no match on the right, which is essential for reports that must include every customer whether they have an account or not.

Aggregation functions collapse rows into summary values. COUNT counts, SUM totals, and AVG averages. GROUP BY splits the data into groups (one group per account, per branch, per month) so that each aggregation runs independently. HAVING filters the groups after aggregation, while WHERE filters individual rows before grouping.

Subqueries let you build queries step by step. A scalar subquery computes a single value (average balance) to use as a filter. An IN subquery finds a list of IDs to match against. A correlated subquery runs once per outer row and is powerful but can be slow. For performance, rewrite correlated subqueries as joins or CTEs.

In banking, these tools combine to produce monthly statements, branch-level reports, top-depositor leaderboards, and fraud detection queries. A senior engineer uses CTEs for readability, window functions for running totals, and EXPLAIN ANALYZE to verify that the query plan is efficient.

Remember one sentence: joins connect tables, GROUP BY creates groups, aggregation summarizes them, and subqueries build queries from the inside out.

---

Share