Joins in SQL (Quick Deep Dive)

Joins are used to combine data from two or more tables based on related columns. They allow you to query across multiple tables efficiently.

1. INNER JOIN

Returns rows that have matching values in both tables.

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d
    ON e.dept_id = d.id;

👉 Only employees that belong to a department.

2. LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table and matching rows from the right table. Non-matching rows → NULL.

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
    ON e.dept_id = d.id;

👉 Shows all employees, even those without a department.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

Opposite of LEFT JOIN. Returns all rows from the right table, and matching from the left.

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d
    ON e.dept_id = d.id;

👉 Shows all departments, even if no employee is assigned.

4. FULL OUTER JOIN

Returns all rows when there’s a match in either left or right table.
(Not supported in MySQL directly, but can be simulated with UNION.)

SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d
    ON e.dept_id = d.id;

5. CROSS JOIN

Produces the Cartesian product (all combinations).

SELECT e.name, d.department_name
FROM employees e
CROSS JOIN departments d;

👉 If 5 employees × 3 departments → 15 rows.