Definition
- 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.
-- 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');
-- 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.
-- 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;
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.
-- 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;
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 Type | Left Table Rows | Right Table Rows | Use Case |
|---|---|---|---|
| INNER JOIN | Only matching | Only matching | Customers with accounts |
| LEFT JOIN | All | Only matching (NULL if no match) | All customers, show accounts if any |
| RIGHT JOIN | Only matching (NULL if no match) | All | All accounts, show customer if linked |
| FULL OUTER JOIN | All | All (NULL on both sides if no match) | Complete picture of both tables |
| CROSS JOIN | Every row paired with every right row | Cartesian product | Rare; generating test combinations |
-- 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;
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.
| Function | Purpose | Example |
|---|---|---|
| COUNT(*) | Number of rows | Total transactions |
| SUM(column) | Total of values | Sum of all deposits |
| AVG(column) | Average value | Average account balance |
| MIN(column) | Smallest value | Minimum transaction amount |
| MAX(column) | Largest value | Maximum account balance |
-- 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;
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.
-- 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;
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.
-- 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;
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)
-- Accounts with balance above the average
SELECT
account_id,
balance
FROM accounts
WHERE balance > (
SELECT AVG(balance) FROM accounts
)
ORDER BY balance DESC;
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)
-- 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'
);
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.
-- 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;
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.
-- 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;
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).
-- 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!
-- 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.
-- 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;
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.
-- WRONG: WHERE cannot filter on COUNT()
SELECT account_id, COUNT(*) AS txn_count
FROM transactions
GROUP BY account_id
WHERE COUNT(*) > 2; -- ERROR!
ERROR: aggregate functions are not allowed in WHERE
CORRECT: Use HAVING for aggregate conditions.
-- 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.
-- 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;
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
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
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.
---