Definition
- JOIN – Combines columns from two tables side by side by matching related rows on a condition (typically a foreign key to primary key), widening each result row with data from both tables.
- UNION – Combines the rows of two result sets on top of each other vertically, requiring both to have the same columns (same count, same order, compatible types).
- Horizontal growth – JOIN adds columns from related rows, making each row wider.
- Vertical growth – UNION adds more rows, making the result set taller.
- Orthogonal purposes – They are not substitutes: no UNION can pull a related column onto a row, and no JOIN can simply stack rows from separate queries.
At National Bank of India, a JOIN is like taking the accounts ledger and the customers directory and stapling each account row to its owner's details so one row shows both the account and who owns it – the row gets wider. A UNION is like taking this year's transaction list and last year's list, which have the same columns, and stacking them into one long list – the list gets taller. One matches and merges across tables; the other piles up rows from similar queries.
Understanding the Concepts
What is a JOIN?
A JOIN answers questions that need data from more than one table in the same row. You specify a join condition, usually a foreign key matching a primary key, and the database pairs each row from one table with the matching rows from the other. The tables do not need the same columns – they usually have completely different columns, and the join is what relates them.
-- JOIN: show each transaction alongside the account holder's name
SELECT t.txn_id, t.amount, t.txn_date, a.holder_name, a.account_type
FROM transactions t
JOIN accounts a ON t.account_id = a.account_id
WHERE a.account_id = 'SAV2001'
ORDER BY t.txn_date DESC;
Expected output:
txn_id | amount | txn_date | holder_name | account_type
---------+-----------+------------+--------------+--------------
TXN-042 | 50000.00 | 2024-01-15 | Rajesh Kumar | Savings
TXN-038 | 25000.00 | 2024-01-10 | Rajesh Kumar | Savings
-- Each row has columns from BOTH tables
What is a UNION?
A UNION answers a different need: two queries that produce the same shape of result must be combined into one list. The requirement is structural compatibility – same number of columns, same order, compatible types – because the rows are stacked, not matched.
-- UNION ALL: combine current and archived transactions into one history
SELECT txn_id, account_id, amount, txn_date FROM transactions_current
UNION ALL
SELECT txn_id, account_id, amount, txn_date FROM transactions_archive
ORDER BY txn_date DESC;
Expected output:
txn_id | account_id | amount | txn_date
---------+------------+-----------+------------
TXN-042 | SAV2001 | 50000.00 | 2024-01-15
TXN-038 | SAV2001 | 25000.00 | 2024-01-10
...
TXN-001 | SAV2001 | 10000.00 | 2022-01-02
-- Rows from BOTH tables stacked vertically
How is JOIN different from UNION?
| Aspect | JOIN | UNION |
|---|---|---|
| Direction | Horizontal (adds columns) | Vertical (adds rows) |
| Requirement | Related rows (FK to PK) | Same column structure |
| Tables | Usually different columns | Must have same columns |
| Condition | ON clause matching rows | No match – just stack |
| Result width | Sum of selected columns | Same as each query |
| Result height | Depends on matching | Sum of both query rows |
| Deduplication | N/A | UNION deduplicates, UNION ALL does not |
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 StatementService {
// JOIN: transaction with account holder details
public List<String> getTransactionsWithHolder(Connection conn,
String accountId) throws SQLException {
String sql = """
SELECT t.txn_id, t.amount, a.holder_name
FROM transactions t
JOIN accounts a ON t.account_id = a.account_id
WHERE a.account_id = ?
ORDER BY t.txn_date DESC
""";
List<String> results = new ArrayList<>();
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, accountId);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
results.add(rs.getString("holder_name") + ": Rs."
+ rs.getBigDecimal("amount"));
}
}
}
return results;
}
// UNION ALL: combine current and archived transactions
public List<String> getFullHistory(Connection conn,
String accountId) throws SQLException {
String sql = """
SELECT txn_id, amount, txn_date FROM transactions_current
WHERE account_id = ?
UNION ALL
SELECT txn_id, amount, txn_date FROM transactions_archive
WHERE account_id = ?
ORDER BY txn_date DESC
""";
List<String> results = new ArrayList<>();
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, accountId);
ps.setString(2, accountId);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
results.add(rs.getString("txn_id") + ": Rs."
+ rs.getBigDecimal("amount"));
}
}
}
return results;
}
}
When should you use JOIN?
Use JOIN when you need data from multiple tables in the same row. This is the most common SQL operation: enriching a row with related data from another table.
-- JOIN: Rajesh's loan details with his customer profile
SELECT c.full_name, c.branch, l.loan_id, l.amount, l.status
FROM loans l
JOIN customers c ON l.customer_id = c.customer_id
WHERE c.customer_id = 42;
When should you use UNION?
Use UNION when you have two queries that produce the same shape of result and you want them in one list. Common cases include combining partitioned or archived tables, and merging lists from different sources.
-- UNION: combine domestic and international transfers into one report
SELECT txn_id, amount, 'DOMESTIC' AS txn_type FROM domestic_transfers
WHERE txn_date >= '2024-01-01'
UNION ALL
SELECT txn_id, amount, 'INTERNATIONAL' AS txn_type FROM international_transfers
WHERE txn_date >= '2024-01-01'
ORDER BY amount DESC;
Can JOIN and UNION be combined?
Yes. You can UNION the results of two different JOIN queries. This is common when merging reports that each require their own joins.
-- UNION of two JOINs: merge domestic and international transfer reports
SELECT t.txn_id, t.amount, a.holder_name, 'DOMESTIC' AS type
FROM domestic_transfers t
JOIN accounts a ON t.from_account = a.account_id
UNION ALL
SELECT t.txn_id, t.amount, a.holder_name, 'INTERNATIONAL' AS type
FROM international_transfers t
JOIN accounts a ON t.from_account = a.account_id
ORDER BY amount DESC;
What are common mistakes?
A common anti-pattern is using UNION of several filtered queries where a single query with OR or IN would be clearer and faster.
-- WRONG: unnecessary UNION for the same table
SELECT * FROM transactions WHERE status = 'COMPLETED'
UNION ALL
SELECT * FROM transactions WHERE status = 'PENDING';
-- CORRECT: single query with IN
SELECT * FROM transactions WHERE status IN ('COMPLETED', 'PENDING');
What should a senior engineer know?
A senior engineer knows that JOIN and UNION are orthogonal – one cannot replace the other. JOIN's cost is driven by matching and benefits from indexes on join columns. UNION's cost includes a deduplication sort unless you use UNION ALL. A self-join is a JOIN of a table to itself, which is different from a UNION of the table with itself.
Visual Overview
┌─────────────────────────────────────────────────────┐ │ JOIN vs UNION Visual │ ├─────────────────────────────────────────────────────┤ │ │ │ JOIN: Horizontal (adds columns) │ │ │ │ transactions accounts │ │ ┌──────────┐ ┌──────────────┐ │ │ │ txn_id │ │ account_id │ │ │ │ amount │──ON──│ holder_name │ │ │ │ acct_id │ │ account_type │ │ │ └──────────┘ └──────────────┘ │ │ │ │ │ │ └────────┬─────────┘ │ │ ▼ │ │ ┌──────────────────────────────────┐ │ │ │ txn_id │ amount │ holder │ type │ ← wider │ │ └──────────────────────────────────┘ │ │ │ │ UNION: Vertical (adds rows) │ │ │ │ txns_2024 txns_2023 │ │ ┌────────────────┐ ┌────────────────┐ │ │ │ txn_id │ amt │ │ txn_id │ amt │ │ │ │ T-042 │ 50000 │ │ T-001 │ 10000 │ │ │ └────────────────┘ └────────────────┘ │ │ │ │ │ │ └──────────┬──────────┘ │ │ ▼ │ │ ┌────────────────┐ │ │ │ txn_id │ amt │ ← taller │ │ │ T-042 │ 50000 │ │ │ │ T-001 │ 10000 │ │ │ └────────────────┘ │ │ │ └─────────────────────────────────────────────────────┘
Key Takeaway
JOIN and UNION serve orthogonal purposes and are not substitutes. A JOIN combines columns from two tables side by side by matching related rows on a condition – typically a foreign key to a primary key – so each result row is widened with data from both tables. The tables usually have completely different columns, and the join is what relates them. Use JOIN when you need data from more than one table in the same row, such as showing each transaction next to its holder's name and account type.
A UNION combines rows from two queries vertically, stacking one result below the other. It requires structural compatibility – same number of columns, same order, compatible types – because the rows are appended, not matched. UNION does not relate the queries by any condition; it just piles them up. Use UNION when you have two similarly shaped result sets you want in one list, like combining current and archived transaction tables.
The way to keep them straight is that JOIN grows the result horizontally by adding columns from related rows, while UNION grows it vertically by adding more rows. Neither can do the other's job: no UNION can pull a holder's name onto a transaction row because that needs a relationship match, and no JOIN can simply stack this year's and last year's transactions because there is no relationship to match on. They can be combined – you can UNION the results of two different JOIN queries, for example merging domestic and international transaction reports at National Bank of India that each needed their own joins.
At National Bank of India, Rajesh's statement service JOINs transactions to accounts to show holder details, and separately UNION ALLs current and archived transactions into one chronological history for Priya's full statement.
Remember one sentence: JOIN combines columns from related rows horizontally by a match condition, while UNION stacks rows from compatible queries vertically.
---