Explain INNER, LEFT, RIGHT, and FULL OUTER JOIN with examples
Reported in OVHcloud European engineering loops. SQL fundamentals question for filtering and combining relational data.
Interview scenario
Context for OVHcloud candidates:
Given tables employees and departments, describe which rows appear for each join type.
Model answer
Try answering aloud first
Cover trade-offs, structure, and a concrete example before revealing the baseline response.
How to frame this at OVHcloud: Connect your answer to measurable impact, clarity of thought, and trade-offs the team cares about. Below is a strong baseline response you can adapt with your own project examples.
INNER JOIN returns rows where join key matches in both tables—employees with valid department_id only.
LEFT JOIN keeps all left (employees) rows; unmatched right columns are NULL—find employees without departments.
RIGHT JOIN mirror of LEFT— all departments, NULL employee fields if no members. FULL OUTER JOIN union of both— all employees and all departments, NULLs where no match.
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;Performance: index foreign keys; avoid joining large unfiltered tables—filter early. Anti-join pattern (NOT EXISTS) often clearer than LEFT JOIN ... IS NULL for missing rows.
Discussion
Comments (0)
Share how this question came up in your loop, or add tips for others preparing.
Log in to comment on this question.