# 🔗 SQL JOINs — Complete Guide

> A beginner-friendly breakdown of all SQL JOIN types with real examples, tables, and query syntax.

* * *

## 📌 What is a JOIN?

A **JOIN** in SQL is used to **combine rows from two or more tables** based on a related column between them.

Think of it like this — you have two lists (tables), and JOIN tells SQL *how* to connect them.

* * *

## 🗂️ Sample Tables Used in This Guide

These two tables are used across all the examples below.

**Customer (Left Table)**

| ID | Name | Number | Address |
| --- | --- | --- | --- |
| 1 | Rahul | 9876 | Delhi |
| 2 | Priya | 8765 | Mumbai |
| 3 | Amit | 7654 | Jaipur |

**Company (Right Table)**

| ID | Name | Salary | PF |
| --- | --- | --- | --- |
| 1 | ABC Ltd | ₹30,000 | ₹2,000 |
| 2 | XYZ Pvt | ₹40,000 | ₹3,000 |
| 4 | PQR Ltd | ₹50,000 | ₹4,000 |

> 📌 Notice: Customer has IDs 1, 2, 3 — Company has IDs 1, 2, 4.  
> ID 3 exists **only in Customer**. ID 4 exists **only in Company**. This is what makes JOIN behavior interesting!

* * *

## 1️⃣ INNER JOIN — Only Matching Rows

### What it does

Returns **only the rows where a matching value exists in both tables**. Unmatched rows from either side are excluded completely.

```plaintext
INNER JOIN = Intersection of both tables
```

### Diagram

![](https://cdn.hashnode.com/uploads/covers/69155a0bdfd652c55316fa57/9bd7c162-79d2-48b8-a774-47cfa34d4aa9.jpg align="center")

### Example — Students & Marks

**Students Table**

| ID | Name |
| --- | --- |
| 1 | Rahul |
| 2 | Priya |
| 3 | Amit |
| 5 | Neha |

**Marks Table**

| ID | Marks |
| --- | --- |
| 1 | 85 |
| 2 | 90 |
| 4 | 75 |
| 5 | 88 |

**Result after INNER JOIN**

| ID | Name | Marks |
| --- | --- | --- |
| 1 | Rahul | 85 |
| 2 | Priya | 90 |
| 5 | Neha | 88 |

> ❌ **Amit (ID 3)** is excluded — no mark for ID 3 in Marks table.  
> ❌ **ID 4** is excluded — no student for ID 4 in Students table.  
> ✅ Only IDs **1, 2, 5** matched in both tables.

### SQL Query

```sql
SELECT s.ID, s.Name, m.Marks
FROM   Students s
INNER JOIN Marks m
  ON s.ID = m.ID;
```

* * *

## 2️⃣ LEFT JOIN — All from Left + Matching from Right

### What it does

Returns **all rows from the left (first) table**, and only the matching rows from the right table. If no match exists on the right, those columns show `NULL`.

```plaintext
LEFT JOIN = Everything from LEFT + Matching from RIGHT
```

## ***Diagram***

![](https://cdn.hashnode.com/uploads/covers/69155a0bdfd652c55316fa57/db01c381-ff63-4e3c-9521-800ca999326c.jpg align="center")

### Result after LEFT JOIN (Customer + Company)

| ID | Name (Customer) | Number | Address | Company | Salary | PF |
| --- | --- | --- | --- | --- | --- | --- |
| 1 | Rahul | 9876 | Delhi | ABC Ltd | ₹30,000 | ₹2,000 |
| 2 | Priya | 8765 | Mumbai | XYZ Pvt | ₹40,000 | ₹3,000 |
| 3 | Amit | 7654 | Jaipur | **NULL** | **NULL** | **NULL** |

> ✅ All **3 Customer rows** are included — even Amit who has no company match.  
> 🔴 Amit's company columns are `NULL` — no matching company record for ID 3.  
> ❌ **Company ID 4** is not shown — it's a right-only row, LEFT JOIN excludes it.

### SQL Query

```sql
SELECT c.ID, c.Name, c.Number, c.Address,
       co.Name, co.Salary, co.PF
FROM   Customer c
LEFT JOIN Company co
  ON c.ID = co.ID;
```

### Key Points

*   LEFT JOIN keeps **all rows** from the left table.
    
*   Only **matching rows** from the right table are added.
    
*   If there is no match → right table columns will be **NULL**.
    
*   Rows in the right table **without a match** are **NOT included**.
    

* * *

## 3️⃣ RIGHT JOIN — All from Right + Matching from Left

### What it does

The exact mirror of LEFT JOIN. Returns **all rows from the right (second) table**, and only the matching rows from the left. If no match exists on the left, those columns show `NULL`.

```plaintext
RIGHT JOIN = Everything from RIGHT + Matching from LEFT
```

### Diagram

![](https://cdn.hashnode.com/uploads/covers/69155a0bdfd652c55316fa57/10bb46fc-ce89-4d08-b6cb-4283c1d4ead0.jpg align="center")

### Result after RIGHT JOIN (Customer + Company)

| Name (Customer) | Number | ID (Company) | Salary | PF |
| --- | --- | --- | --- | --- |
| Rahul | 9876 | 1 | 30,000 | 2,000 |
| Priya | 8765 | 2 | 40,000 | 3,000 |
| **NULL** | **NULL** | 4 | 50,000 | 4,000 |

> ✅ All **3 Company rows** are included — including Sneha (ID 4) with no customer.  
> 🔴 Sneha's customer columns are `NULL` — no Customer record for ID 4.  
> ❌ **Amit (ID 3)** is not shown — he's a left-only row, RIGHT JOIN excludes him.

### SQL Query

```sql
SELECT c.Name, c.Number,
       co.ID, co.Salary, co.PF
FROM   Customer c
RIGHT JOIN Company co
  ON c.ID = co.ID;
```

### Key Points

*   RIGHT JOIN keeps **all rows** from the right table.
    
*   Only **matching rows** from the left table are added.
    
*   If there is no match → left table columns will be **NULL**.
    
*   Rows in the left table **without a match** are **NOT included**.
    

* * *

## 4️⃣ FULL OUTER JOIN — Everything from Both

### What it does

Returns **all rows from both tables**. Where there is no match on either side, the missing columns are filled with `NULL`. It's essentially LEFT JOIN + RIGHT JOIN combined.

```plaintext
FULL OUTER JOIN = LEFT JOIN ∪ RIGHT JOIN
```

## Diagram

![](https://cdn.hashnode.com/uploads/covers/69155a0bdfd652c55316fa57/e328cc6e-7a23-44b9-9a98-e18343b12d4f.png align="center")

### Result after FULL OUTER JOIN (Customer + Company)

| ID | Customer Name | Number | Address | Company | Salary |
| --- | --- | --- | --- | --- | --- |
| 1 | Rahul | 9876 | Delhi | ABC Ltd | ₹30,000 |
| 2 | Priya | 8765 | Mumbai | XYZ Pvt | ₹40,000 |
| 3 | Amit | 7654 | Jaipur | **NULL** | **NULL** |
| 4 | **NULL** | **NULL** | **NULL** | PQR Ltd | ₹50,000 |

> ✅ Amit (ID 3) — has no company, right side is `NULL`.  
> ✅ ID 4 — has no customer, left side is `NULL`.  
> ✅ **Nobody is left behind!**

### SQL Query

```sql
SELECT c.ID, c.Name, c.Address,
       co.Name, co.Salary
FROM   Customer c
FULL OUTER JOIN Company co
  ON c.ID = co.ID;
```

> ⚠️ **MySQL doesn't support** `FULL OUTER JOIN` **directly.**  
> Use this workaround instead:

```sql
-- MySQL workaround
SELECT * FROM Customer c
LEFT JOIN Company co ON c.ID = co.ID

UNION

SELECT * FROM Customer c
RIGHT JOIN Company co ON c.ID = co.ID;
```

* * *

## 📊 Quick Comparison — All JOINs at a Glance

| What happens to... | INNER JOIN | LEFT JOIN | RIGHT JOIN | FULL OUTER JOIN |
| --- | --- | --- | --- | --- |
| Left-only rows | ❌ Excluded | ✅ Included | ❌ Excluded | ✅ Included |
| Right-only rows | ❌ Excluded | ❌ Excluded | ✅ Included | ✅ Included |
| Matched rows (in both) | ✅ Included | ✅ Included | ✅ Included | ✅ Included |
| NULLs appear? | Never | Right columns | Left columns | Both sides |

* * *

## 🧠 Memory Tricks

| JOIN Type | Think of it as... |
| --- | --- |
| INNER JOIN | 🎯 "Give me **only what's common** in both" |
| LEFT JOIN | ⬅️ "Keep **everyone from the left**, grab matches from right" |
| RIGHT JOIN | ➡️ "Keep **everyone from the right**, grab matches from left" |
| FULL OUTER | 🔄 "Keep **absolutely everyone**, NULL where there's no match" |

* * *

## 📝 Direction Tip

```plaintext
LEFT JOIN  →  Keep LEFT table  (+ matching from right)
RIGHT JOIN →  Keep RIGHT table (+ matching from left)
```