✏️ Explanatory Question
Level: Intermediate — One of the most heavily tested topics in any SQL interview.
A JOIN combines rows from two or more tables based on a related column between them (usually a primary key–foreign key relationship). Joins are what make relational databases so powerful.
FULL OUTER JOIN. You simulate it by combining a LEFT JOIN and a RIGHT JOIN with UNION (covered in the next question).
| JOIN Type | Returns | Unmatched Rows |
|---|---|---|
| INNER JOIN | Only matching rows | Excluded |
| LEFT JOIN | All left + matches | Right side = NULL |
| RIGHT JOIN | All right + matches | Left side = NULL |
| CROSS JOIN | All combinations | N/A |
| SELF JOIN | Table joined to itself | Depends on join type |
-- Sample tables
-- employees(emp_id, name, dept_id)
-- departments(dept_id, dept_name)
-- INNER JOIN: employees who belong to a department
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- LEFT JOIN: ALL employees, even those without a department
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
-- RIGHT JOIN: ALL departments, even those with no employees
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;
-- CROSS JOIN: every employee paired with every department
SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;