SQL Joins

-- employees
| id | name    | dept_id |
|----|---------|--------|
| 1  | Alice   | 10     |
| 2  | Bob     | 20     |
| 3  | Charlie | NULL   |

-- departments
| id | name        |
|----|-------------|
| 10 | Engineering |
| 20 | Marketing   |
| 30 | Finance     |

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

-- Result:
-- Alice   Engineering
-- Bob     Marketing
-- (Charlie excluded — NULL dept_id; Finance excluded — no employees)

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

-- Result:
-- Alice   Engineering
-- Bob     Marketing
-- Charlie NULL         ← Charlie kept, no dept match

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

-- Result:
-- Alice   Engineering
-- Bob     Marketing
-- NULL    Finance     ← Finance kept, no employees

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

-- Result:
-- Alice   Engineering
-- Bob     Marketing
-- Charlie NULL
-- NULL    Finance

SELECT e.name, d.name FROM employees e CROSS JOIN departments d;
-- 3 employees × 3 departments = 9 rows

-- employees table has manager_id referencing id in same table
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Employees without a department
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;

-- Alternative with NOT EXISTS
SELECT name FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM departments d WHERE d.id = e.dept_id
);