# SQL Joins — INNER, LEFT, RIGHT, FULL, SELF and CROSS JOIN

Source: https://skills101.in/learn/dbms-sql/sql-joins — Skills101 free tutorial (DBMS & SQL). Updated 2026-11-09.

Data is split across tables (that's normalization). **Joins** put it back together: "show each employee **with their department name**".

```sql
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(30));
INSERT INTO departments VALUES (10, 'Engineering'), (20, 'Sales'), (30, 'HR'), (40, 'Marketing');
CREATE TABLE employees (emp_id INT PRIMARY KEY, name VARCHAR(30), dept_id INT, salary INT, city VARCHAR(20), manager_id INT, joined DATE);
INSERT INTO employees VALUES
 (1, 'Asha', 10, 95000, 'Jaipur', NULL, '2019-04-01'), (2, 'Ravi', 10, 72000, 'Pune', 1, '2021-07-15'),
 (3, 'Neha', 20, 58000, 'Jaipur', 1, '2020-01-10'), (4, 'Kabir', 20, 58000, 'Delhi', 3, '2022-03-01'),
 (5, 'Om', 30, 45000, 'Pune', 1, '2023-06-20'), (6, 'Meera', 10, 88000, 'Bengaluru', 1, '2018-11-05'),
 (7, 'Arjun', NULL, 40000, 'Delhi', 3, '2024-02-12');
```

Notice: **Arjun** has no department, and **Marketing (40)** has no employees. These two rows show the difference between join types.

![Venn diagrams: INNER JOIN keeps only the overlap; LEFT JOIN keeps all of the left table; RIGHT JOIN keeps all of the right table; FULL OUTER keeps everything](https://skills101.in/learn-img/dbms-sql/joins.svg)

## 1. INNER JOIN — only matches

```sql
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;
```

```text
name | dept_name
Asha | Engineering
Ravi | Engineering
Neha | Sales
Kabir | Sales
Om | HR
Meera | Engineering
```

Arjun (no dept) and Marketing (no staff) are **both missing**. `e` and `d` are **table aliases** — short names.

## 2. LEFT JOIN — all rows from the left table

```sql
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary < 50000
ORDER BY e.emp_id;
```

```text
name | dept_name
Om | HR
Arjun | NULL
```

Arjun appears, with **NULL** for the department.

### Find rows with NO match (very common question)

*Which departments have no employees?*

```sql
SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;
```

```text
dept_name
Marketing
```

## 3. RIGHT JOIN and FULL OUTER JOIN

- **RIGHT JOIN** = LEFT JOIN with the tables swapped (keeps all rows of the right table). Most people just write LEFT JOIN.
- **FULL OUTER JOIN** keeps unmatched rows from **both** sides.

```sql
SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL OR d.dept_id IS NULL;
```

```text
name | dept_name
Arjun | NULL
NULL | Marketing
```

(MySQL has no FULL OUTER JOIN — combine a LEFT JOIN and a RIGHT JOIN with `UNION`.)

## 4. SELF JOIN — a table joined to itself

*Show each employee with their manager's name.* The manager is also in the employees table.

```sql
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id
ORDER BY e.emp_id;
```

```text
employee | manager
Asha | NULL
Ravi | Asha
Neha | Asha
Kabir | Neha
Om | Asha
Meera | Asha
Arjun | Neha
```

### Employees who earn more than their manager

```sql
SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;
```

```text
name | salary | manager | manager_salary
```

(No rows — in this company, nobody earns more than their manager. An empty result is a valid answer!)

## 5. CROSS JOIN — every combination

`SELECT * FROM a CROSS JOIN b` pairs every row of a with every row of b: 7 employees × 4 departments = **28 rows**. Useful for generating combinations (sizes × colours), dangerous by accident.

## 6. Joining three tables

```sql
CREATE TABLE projects (project_id INT PRIMARY KEY, title VARCHAR(30), lead_id INT);
INSERT INTO projects VALUES (100, 'Payments App', 2), (101, 'CRM Revamp', 3);
SELECT p.title, e.name AS lead, d.dept_name
FROM projects p
JOIN employees e ON p.lead_id = e.emp_id
JOIN departments d ON e.dept_id = d.dept_id
ORDER BY p.project_id;
```

```text
title | lead | dept_name
Payments App | Ravi | Engineering
CRM Revamp | Neha | Sales
```

## Common mistakes

- Forgetting the ON condition → an accidental cross join (huge results).
- Putting a filter on the right table in WHERE after a LEFT JOIN, which turns it back into an inner join. Put such conditions in the ON clause.
- Ambiguous columns: write `e.dept_id`, not just `dept_id`, when both tables have it.

## Quick recap

- INNER = matches only; LEFT = all left + matches; RIGHT = all right + matches; FULL = everything.
- Unmatched sides become NULL. "No match" questions → LEFT JOIN … WHERE right.key IS NULL.
- SELF JOIN for hierarchies (employee → manager). CROSS JOIN = all combinations.


Practice quiz and free certificate: https://skills101.in/learn/dbms-sql/sql-joins