Joins
## Learning Objectives
- Understand table relationships
- Master INNER JOIN
- Use LEFT and RIGHT JOIN
- Work with multiple joins
## Why Join?
Combine data from multiple tables:
```sql
-- Without join: incomplete
SELECT first_name, department
FROM employees;
-- Result doesn't show department details, just names
-- With join: complete
SELECT
e.first_name,
d.department_name,
d.location
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
```
## Table Aliases
```sql
SELECT
e.first_name,
d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
```
## INNER JOIN
### Basic Syntax
```sql
SELECT
e.first_name,
e.last_name,
d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
```
### Matching Rows Only
Only rows with matches in BOTH tables:
```text
employees departments
+-----------+ +----+----------------+
| dept_id | | id | name |
+-----------+ +----+----------------+
| 1 | ---> | 1 | Engineering |
| 2 | ---> | 2 | Sales |
| 3 | | 3 | Marketing |
+-----------+ +----+----------------+
Result (INNER JOIN on dept_id = id):
+-------------+----------------+
| employee | department |
+-------------+----------------+
| Alice | Engineering |
| Bob | Sales |
+-------------+----------------+
(Employee with dept_id=3 excluded - no match)
```
### Using WHERE with JOIN
```sql
SELECT
e.first_name,
d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE d.location = 'New York';
```
## LEFT JOIN (LEFT OUTER JOIN)
### All Left + Matching Right
```sql
SELECT
e.first_name,
d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
```
### LEFT JOIN Result
```text
employees departments
+-----------+ +----+----------------+
| dept_id | | id | name |
+-----------+ +----+----------------+
| 1 | ---> | 1 | Engineering |
| 2 | ---> | 2 | Sales |
| NULL | | | (no match) |
+-----------+ +----+----------------+
Result: All employees, department is NULL for unmatched
```
### Practical Example
```sql
-- Employees with or without department
SELECT
e.first_name,
e.last_name,
d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
```
## RIGHT JOIN (RIGHT OUTER JOIN)
### All Right + Matching Left
```sql
SELECT
e.first_name,
d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;
```
### RIGHT JOIN Result
```text
-- All departments, employees shown if matched
-- Department without employees still appears
```
## FULL OUTER JOIN
### All Rows from Both
```sql
SELECT
e.first_name,
d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;
```
### FULL OUTER JOIN Result
All rows from both tables, NULLs where no match.
## CROSS JOIN
### Cartesian Product
Every row from first table joins with every row from second:
```sql
SELECT
e.first_name,
d.department_name
FROM employees e
CROSS JOIN departments d;
```
### When to Avoid
Usually produces huge result sets (m * n rows).
### Use Case: Generating Combinations
```sql
-- All color/size combinations
SELECT
c.color_name,
s.size_name
FROM colors c
CROSS JOIN sizes s;
```
## Multiple Joins
### Three Tables
```sql
SELECT
e.first_name,
d.department_name,
l.city
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN locations l ON d.location_id = l.id;
```
### Join Order Matters
```sql
-- Different join order, same result
SELECT ...
FROM employees e
JOIN departments d ON ...
JOIN locations l ON ...
```
## Self Join
### Joining Table to Itself
```sql
-- Find employees and their managers
SELECT
e.first_name AS employee,
m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
```
### Self Join Example Result
```text
employee | manager
----------+----------
Alice | NULL (top level)
Bob | Alice
Carol | Alice
Dave | Bob
```
## Implicit Join Syntax (Legacy)
```sql
-- Old style - avoid this
SELECT
e.first_name,
d.department_name
FROM employees e, departments d
WHERE e.department_id = d.id;
-- Modern style - use explicit JOIN
SELECT ...
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
```
## JOIN with NULL
```sql
-- Match on NULL (usually not what you want)
SELECT *
FROM employees e
JOIN departments d ON e.department_id = d.id
-- NULL = NULL doesn't match!
-- If you have NULL department_ids you want to match:
-- Use LEFT JOIN instead
```
## Summary
- **INNER JOIN**: Only matching rows from both tables
- **LEFT JOIN**: All rows from left table, matching from right
- **RIGHT JOIN**: All rows from right table, matching from left
- **FULL OUTER JOIN**: All rows from both tables
- **CROSS JOIN**: Every combination (avoid unless intentional)
- Use `ON` for join conditions, not `WHERE`
- Use table aliases for readability
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →