Definition
- INNER JOIN – Returns only rows that match in both tables; unmatched rows are dropped.
- LEFT (OUTER) JOIN – Returns all rows from the left table plus matching right rows, with NULLs where there is no match.
- RIGHT (OUTER) JOIN – The mirror of LEFT: keeps all right rows, NULL-fills the left.
- FULL (OUTER) JOIN – Returns all rows from both tables, matched where possible and NULL-filled otherwise.
- CROSS JOIN – Returns the Cartesian product of every left row with every right row, no join condition.
- SELF JOIN – Joins a table to itself using aliases, relating rows within the same table (e.g., employee to manager).
At National Bank of India, matching accounts to transactions: INNER JOIN shows only accounts that have transacted – dormant ones disappear. LEFT JOIN shows every account including dormant ones with blank transaction fields. RIGHT JOIN keeps every transaction even if its account record is missing. FULL JOIN shows everything from both sides. CROSS JOIN pairs every account with every promotional offer. SELF JOIN pairs each employee with their manager from the same employees table.
Understanding the Concepts
What is an INNER JOIN?
INNER JOIN is the default and most common join. It returns only the rows where the join condition is satisfied in both tables. Unmatched rows on either side are dropped.
-- INNER JOIN: only accounts that have transactions
SELECT a.account_id, a.holder_name, t.txn_id, t.amount
FROM accounts a
JOIN transactions t ON t.account_id = a.account_id
WHERE a.branch = 'Mumbai-Central'
ORDER BY t.txn_date DESC;
Expected output:
account_id | holder_name | txn_id | amount
------------+--------------+---------+----------
SAV2001 | Rajesh Kumar | TXN-042 | 50000.00
SAV2001 | Rajesh Kumar | TXN-038 | 25000.00
CUR3001 | Priya Sharma | TXN-035 | 75000.00
-- Dormant accounts with no transactions do NOT appear
What is a LEFT JOIN?
LEFT JOIN returns every row from the left table regardless of whether it matches, and fills right-side columns with NULL where there is no match. This is how you include entities that have no related rows.
-- LEFT JOIN: all accounts including dormant ones with no transactions
SELECT a.account_id, a.holder_name, t.txn_id, t.amount
FROM accounts a
LEFT JOIN transactions t ON t.account_id = a.account_id
WHERE a.branch = 'Mumbai-Central'
ORDER BY a.account_id;
Expected output: account_id | holder_name | txn_id | amount ------------+----------------+---------+---------- CUR3001 | Priya Sharma | TXN-035 | 75000.00 FD4001 | Pankaj Gupta | NULL | NULL ← dormant account SAV2001 | Rajesh Kumar | TXN-042 | 50000.00 SAV2001 | Rajesh Kumar | TXN-038 | 25000.00
What is the anti-join pattern with LEFT JOIN?
A LEFT JOIN with WHERE right.key IS NULL returns exactly the left rows that have no match. This is the anti-join.
-- Anti-join: accounts that have NEVER transacted
SELECT a.account_id, a.holder_name
FROM accounts a
LEFT JOIN transactions t ON t.account_id = a.account_id
WHERE t.txn_id IS NULL;
Expected output:
account_id | holder_name
------------+--------------
FD4001 | Pankaj Gupta
-- Only accounts with no transactions
What is a RIGHT JOIN?
RIGHT JOIN is the exact mirror of LEFT JOIN: it keeps all rows from the right table and NULL-fills the left. In practice, people rarely use RIGHT JOIN because you can always reorder the tables and write a LEFT JOIN, which reads more naturally.
-- RIGHT JOIN: keep every transaction even if account is missing
SELECT a.holder_name, t.txn_id, t.amount
FROM accounts a
RIGHT JOIN transactions t ON t.account_id = a.account_id;
-- Equivalent to: FROM transactions t LEFT JOIN accounts a ON ...
What is a FULL OUTER JOIN?
FULL OUTER JOIN returns all rows from both tables, matching them where the condition holds and NULL-filling wherever a row on one side has no partner. It is useful for reconciliation.
-- FULL JOIN: reconcile two systems' transaction records
SELECT COALESCE(a.txn_id, b.txn_id) AS txn_id,
a.amount AS system_a_amount,
b.amount AS system_b_amount,
CASE
WHEN a.txn_id IS NULL THEN 'Missing in System A'
WHEN b.txn_id IS NULL THEN 'Missing in System B'
WHEN a.amount <> b.amount THEN 'Amount mismatch'
ELSE 'Match'
END AS status
FROM system_a_txns a
FULL OUTER JOIN system_b_txns b ON a.txn_id = b.txn_id;
Expected output:
txn_id | system_a_amount | system_b_amount | status
---------+-----------------+-----------------+---------------------
TXN-001 | 50000.00 | 50000.00 | Match
TXN-002 | 30000.00 | NULL | Missing in System B
TXN-003 | NULL | 25000.00 | Missing in System A
TXN-004 | 75000.00 | 74999.00 | Amount mismatch
What is a CROSS JOIN?
CROSS JOIN produces the Cartesian product: every row of the left table paired with every row of the right. It has no join condition and is used deliberately to generate combinations.
-- CROSS JOIN: generate campaign matrix of accounts x offers
SELECT a.account_id, a.holder_name, o.offer_name, o.interest_rate
FROM accounts a
CROSS JOIN credit_card_offers o;
-- 100 accounts x 5 offers = 500 rows
What is a SELF JOIN?
A SELF JOIN joins a table to itself using aliases. The classic case is an employees table where each row has a manager_id pointing at another row in the same table.
-- SELF JOIN: each employee alongside their manager's name
SELECT e.emp_name AS employee, m.emp_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
Expected output: employee | manager ----------------+----------- Rajesh Kumar | Pankaj Gupta Priya Sharma | Pankaj Gupta Pankaj Gupta | NULL ← top of hierarchy
How are all join types different?
| Join Type | Unmatched Left | Unmatched Right | Use Case |
|---|---|---|---|
| INNER | Dropped | Dropped | Active accounts with transactions |
| LEFT | Kept (NULL-filled) | Dropped | All accounts including dormant |
| RIGHT | Dropped | Kept (NULL-filled) | Rarely used; rewrite as LEFT |
| FULL | Kept (NULL-filled) | Kept (NULL-filled) | Ledger reconciliation |
| CROSS | N/A | N/A | Campaign matrix, combinations |
| SELF | N/A | N/A | Employee-manager hierarchy |
package com.bank.service;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class DormancyReportService {
// LEFT JOIN anti-join: find accounts with no transactions
public List<String> findDormantAccounts(Connection conn) throws SQLException {
String sql = """
SELECT a.account_id, a.holder_name
FROM accounts a
LEFT JOIN transactions t ON t.account_id = a.account_id
WHERE t.txn_id IS NULL
ORDER BY a.account_id
""";
List<String> dormant = new ArrayList<>();
try (PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
dormant.add(rs.getString("account_id") + ": "
+ rs.getString("holder_name"));
}
}
return dormant;
}
}
What is the WHERE clause trap with LEFT JOIN?
A filter on an outer-joined table placed in WHERE silently turns a LEFT JOIN back into an INNER JOIN. The condition must go in ON instead.
-- WRONG: WHERE filter on right table turns LEFT JOIN into INNER JOIN
SELECT a.account_id, t.txn_id, t.amount
FROM accounts a
LEFT JOIN transactions t ON t.account_id = a.account_id
WHERE t.status = 'COMPLETED'; -- drops dormant accounts (NULL status fails)
-- CORRECT: move condition into ON clause
SELECT a.account_id, t.txn_id, t.amount
FROM accounts a
LEFT JOIN transactions t ON t.account_id = a.account_id AND t.status = 'COMPLETED';
-- Dormant accounts kept with NULL transaction columns
What should a senior engineer know?
A senior engineer knows that indexes on join columns are critical for performance, that the optimizer chooses among nested-loop, hash, and merge join strategies, that a WHERE filter on an outer-joined table converts it to an inner join unless moved to ON, and that CROSS JOINs are dangerous on large tables because the result size is multiplicative.
Visual Overview
┌─────────────────────────────────────────────────────┐
│ All SQL Join Types │
├─────────────────────────────────────────────────────┤
│ │
│ Table A Table B │
│ ┌─────┐ ┌─────┐ │
│ │ 1 │ │ 1 │ │
│ │ 2 │ │ 2 │ │
│ │ 3 │ │ 4 │ │
│ └─────┘ └─────┘ │
│ │
│ INNER: { (1,1), (2,2) } │
│ Only matching rows │
│ │
│ LEFT: { (1,1), (2,2), (3,NULL) } │
│ All from A + matches from B │
│ │
│ RIGHT: { (1,1), (2,2), (NULL,4) } │
│ All from B + matches from A │
│ │
│ FULL: { (1,1), (2,2), (3,NULL), (NULL,4) } │
│ All from both sides │
│ │
│ CROSS: { (1,1),(1,2),(1,4), │
│ (2,1),(2,2),(2,4), │
│ (3,1),(3,2),(3,4) } │
│ Every combination (3 x 3 = 9) │
│ │
│ SELF: A joined to A (e.g., emp → manager) │
│ │
└─────────────────────────────────────────────────────┘
Key Takeaway
SQL joins combine rows from two tables on a related column, and there are a handful of types that differ in which unmatched rows they keep. INNER JOIN returns only matching rows from both tables, so unmatched rows on either side disappear. LEFT JOIN returns every row from the left table and NULL-fills the right where there is no match, which is how you list all accounts including dormant ones. RIGHT JOIN is the mirror, though in practice people rewrite as LEFT JOIN.
FULL OUTER JOIN returns all rows from both tables, matched where possible and NULL-filled otherwise – ideal for reconciliation when comparing two systems' records. CROSS JOIN produces the Cartesian product with no condition, useful for generating combinations like every account paired with every offer, but dangerous on large tables. SELF JOIN joins a table to itself with aliases to relate rows within the same table, like matching employees to their managers.
Two refinements complete the picture: an equi-join uses an equality condition (the vast majority of joins), while a non-equi join uses ranges or inequalities. A NATURAL JOIN auto-joins on same-named columns, which is concise but risky because adding a same-named column later silently changes the join.
The senior pitfalls Pankaj always mentions at National Bank of India are that a filter on an outer-joined table placed in WHERE silently converts a LEFT JOIN to an INNER JOIN unless moved to ON, and that indexes on join columns are critical because the optimizer chooses among nested-loop, hash, and merge strategies.
Remember one sentence: INNER keeps matches only, LEFT and RIGHT keep all of one side, FULL keeps all of both, CROSS pairs everything with everything, and SELF joins a table to itself.
---