SQL Joins Explained for Technical Interview Candidates

Concept in Simple Words: Joins combine columns from two tables based on a matching key. Think of INNER JOIN as the overlap in a Venn diagram; only rows that match on BOTH sides survive. LEFT JOIN keeps everything from the left table; if there is no match on the right, it fills the right columns with NULL. RIGHT JOIN does the opposite. FULL JOIN keeps all rows from both tables, filling mismatches on either side with NULL.

Deep Walkthrough & Code: Let's look at a LEFT JOIN comparing employee departments, and how a NULL key handles matches:

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

Step-by-Step Dry Run: Suppose we have Employees: [Alice (dept_id=1), Bob (dept_id=NULL)] and Departments: [1 (Engineering)].
- Trace Alice: dept_id=1 matches Department id=1. Alice, Engineering is returned.
- Trace Bob: dept_id=NULL. NULL never equals anything, not even another NULL. LEFT JOIN preserves Bob, but department details are filled with NULL: Bob, NULL is returned.
Bob would be excluded entirely in an INNER JOIN.

Production Level Issue & Fix: A common production trap is filtering the right table columns in the WHERE clause of a LEFT JOIN: `WHERE d.dept_name = 'Engineering'`. This implicitly converts the LEFT JOIN into an INNER JOIN because rows with NULL on the right are filtered out by the WHERE condition. The fix is to place the filter condition inside the ON clause instead: `LEFT JOIN departments d ON e.dept_id = d.id AND d.dept_name = 'Engineering'`.