SQL Joins

Relational databases store data in multiple tables. To answer questions that involve data from two or more tables, you need joins.

A join combines rows from two or more tables based on a related column (usually a foreign key).

Joins are the most important SQL concept for both daily work and interviews.

---

Learning Objectives

After completing this chapter, you will be able to:

• Understand the purpose of joins.
• Write INNER, LEFT, RIGHT, FULL OUTER, and CROSS joins.
• Understand the difference between joins.
• Write self-joins for recursive relationships.
• Use NATURAL joins and understand their risks.
• Choose the correct join for any query.
• Answer join-related interview questions.

---

Sample Data

All examples use these two tables.

CREATE TABLE Customers (
    Customer_ID INT PRIMARY KEY,
    Name VARCHAR(50),
    City VARCHAR(50)
);

CREATE TABLE Orders (
    Order_ID INT PRIMARY KEY,
    Customer_ID INT,
    Amount DECIMAL(10,2),
    Order_Date DATE
);

Customers

Customer_ID	Name	City
1	Rahul	Mumbai
2	Priya	Delhi
3	Amit	Mumbai
4	Sneha	Bangalore
5	Vikram	Pune

Orders

Order_ID	Customer_ID	Amount	Order_Date
101	1	50000	2024-01-15
102	2	15000	2024-01-16
103	1	25000	2024-02-01
104	3	30000	2024-02-05
105	6	10000	2024-03-01

Note:

• Customer_ID 5 (Vikram) has no orders.
• Order 105 has Customer_ID 6, which does not exist in Customers.

---

INNER JOIN

INNER JOIN returns only matching rows from both tables.

If a row in one table has no match in the other, it is excluded.

Syntax

SELECT columns
FROM TableA
INNER JOIN TableB ON TableA.key = TableB.key;

Example

SELECT c.Name, o.Order_ID, o.Amount
FROM Customers c
INNER JOIN Orders o ON c.Customer_ID = o.Customer_ID;

Name	Order_ID	Amount
Rahul	101	50000
Priya	102	15000
Rahul	103	25000
Amit	104	30000

Result: 4 rows.

• Vikram (no orders) is excluded.
• Order 105 (non-existent customer) is excluded.

---

LEFT JOIN (LEFT OUTER JOIN)

LEFT JOIN returns all rows from the left table, plus matching rows from the right table.

If there is no match in the right table, the result shows NULL for the right-table columns.

Syntax

SELECT columns
FROM TableA
LEFT JOIN TableB ON TableA.key = TableB.key;

Example

SELECT c.Name, c.City, o.Order_ID, o.Amount
FROM Customers c
LEFT JOIN Orders o ON c.Customer_ID = o.Customer_ID;

Name	City	Order_ID	Amount
Rahul	Mumbai	101	50000
Rahul	Mumbai	103	25000
Priya	Delhi	102	15000
Amit	Mumbai	104	30000
Sneha	Bangalore	NULL	NULL
Vikram	Pune	NULL	NULL

Result: 6 rows — all customers, with NULLs where orders don’t exist.

---

RIGHT JOIN (RIGHT OUTER JOIN)

RIGHT JOIN returns all rows from the right table, plus matching rows from the left table.

Important: RIGHT JOIN is rarely used because the same result can be achieved by swapping the tables in a LEFT JOIN.

Syntax

SELECT columns
FROM TableA
RIGHT JOIN TableB ON TableA.key = TableB.key;

Example

SELECT c.Name, o.Order_ID, o.Amount
FROM Customers c
RIGHT JOIN Orders o ON c.Customer_ID = o.Customer_ID;

Name	Order_ID	Amount
Rahul	101	50000
Priya	102	15000
Rahul	103	25000
Amit	104	30000
NULL	105	10000

Result: 5 rows — all orders, with NULLs where customer doesn’t exist.

---

FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables.

Where matches exist, rows are combined. Where no match exists, NULLs fill the opposite side.

Syntax

SELECT columns
FROM TableA
FULL OUTER JOIN TableB ON TableA.key = TableB.key;

Example

SELECT c.Name, o.Order_ID, o.Amount
FROM Customers c
FULL OUTER JOIN Orders o ON c.Customer_ID = o.Customer_ID;

Name	Order_ID	Amount
Rahul	101	50000
Rahul	103	25000
Priya	102	15000
Amit	104	30000
Sneha	NULL	NULL
Vikram	NULL	NULL
NULL	105	10000

Result: 7 rows — all customers and all orders, with NULLs where no match exists.

---

Visual Summary

INNER:            LEFT:             RIGHT:            FULL:
┌─────┬─────┐    ┌─────┬─────┐    ┌─────┬─────┐    ┌─────┬─────┐
│     │     │    │  A  │     │    │     │  B  │    │  A  │  B  │
│     │     │    │     │     │    │     │     │    │     │     │
│     │     │    │     │     │    │     │     │    │     │     │
│  A∩B│     │    │  A∩B│     │    │  A∩B│     │    │  A∩B│     │
│     │     │    │     │     │    │     │     │    │     │     │
└─────┴─────┘    └─────┴─────┘    └─────┴─────┘    └─────┴─────┘
 Only matches   All from left    All from right    All from both

---

CROSS JOIN

CROSS JOIN returns the Cartesian product — every row from Table A combined with every row from Table B.

Syntax

SELECT columns
FROM TableA
CROSS JOIN TableB;

Example

SELECT c.Name, o.Order_ID
FROM Customers c
CROSS JOIN Orders o;

For 5 customers and 5 orders, the result has 5 × 5 = 25 rows.

Name	Order_ID
Rahul	101
Rahul	102
Rahul	103
Rahul	104
Rahul	105
Priya	101
…	…

When to Use

• Generating all combinations (e.g., all products × all stores).
• Creating test data.
• Generating date ranges.

---

SELF JOIN

A SELF JOIN joins a table to itself.

Useful for hierarchical relationships (employee-manager, product categories).

Syntax

SELECT columns
FROM TableA t1
JOIN TableA t2 ON t1.column = t2.column;

Example

Emp_ID	Name	Manager_ID
1	Raj	NULL
2	Rahul	1
3	Priya	1
4	Amit	2

SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.Manager_ID = m.Emp_ID;

Employee	Manager
Raj	NULL
Rahul	Raj
Priya	Raj
Amit	Rahul

SELF JOIN requires table aliases (otherwise you cannot reference the same table twice).

---

NATURAL JOIN

NATURAL JOIN automatically joins tables on columns with the same name.

SELECT * FROM Customers NATURAL JOIN Orders;

If both tables have a column named Customer_ID, the join is applied automatically.

Danger

• You have no control over which columns are used for joining.
• If someone adds a new column with a matching name later, the join behavior changes silently.
• Never use NATURAL JOIN in production. Always use explicit JOIN with ON clause.

---

Join Performance Considerations

Join Type	Performance Note
INNER	Fastest when indexed correctly (primary key + foreign key indexes)
LEFT	Similar to INNER for matched rows; slower if most rows don’t match
FULL	Slowest — needs to scan both tables completely
CROSS	Very slow for large tables (produces M × N rows)
SELF	Same as INNER (but same table scanned twice)

Indexing for Joins

Always index foreign key columns:

CREATE INDEX idx_customer_id ON Orders(Customer_ID);

Without this index, every join will scan the entire Orders table.

---

Practical Join Patterns

Pattern 1: Customers with no orders

SELECT c.*
FROM Customers c
LEFT JOIN Orders o ON c.Customer_ID = o.Customer_ID
WHERE o.Order_ID IS NULL;

Pattern 2: Top customers by spend

SELECT c.Name, SUM(o.Amount) AS Total_Spent
FROM Customers c
JOIN Orders o ON c.Customer_ID = o.Customer_ID
GROUP BY c.Name
ORDER BY Total_Spent DESC;

Pattern 3: Active vs inactive customers

SELECT
    c.Name,
    CASE WHEN o.Order_ID IS NOT NULL THEN 'Active' ELSE 'Inactive' END AS Status
FROM Customers c
LEFT JOIN Orders o ON c.Customer_ID = o.Customer_ID;

---

Interview Deep Dive

---

Key Takeaways

• INNER JOIN returns only matching rows.
• LEFT JOIN returns all rows from the left table.
• RIGHT JOIN returns all rows from the right table (avoid — use LEFT JOIN instead).
• FULL OUTER JOIN returns all rows from both tables.
• CROSS JOIN returns the Cartesian product.
• SELF JOIN joins a table to itself (needs aliases).
• NATURAL JOIN is dangerous — always use explicit ON condition.
• Index foreign key columns for join performance.
• Use LEFT JOIN + WHERE IS NULL to find non-matching rows.