Difference between INNER JOIN and OUTER JOIN?

Question

Accepted Answer

INNER JOIN and OUTER JOIN both combine data from two tables, but they differ in how unmatched rows are handled -- INNER JOIN returns only rows with a match in both tables, while OUTER JOIN also includes unmatched rows from one or both tables, filling in NULLs for the missing side.

Key Points:
• INNER JOIN excludes any row that doesn't have a corresponding match in the other table.
• LEFT (OUTER) JOIN keeps all rows from the left table, with NULLs for unmatched right-table columns.
• RIGHT (OUTER) JOIN keeps all rows from the right table, with NULLs for unmatched left-table columns.
• FULL OUTER JOIN keeps all rows from both tables, matched or not; MySQL doesn't support it natively and it's usually emulated with a UNION of LEFT and RIGHT joins.

Example:
Joining employees to departments with INNER JOIN excludes any employee not yet assigned a department, while a LEFT JOIN would still include that employee with a NULL department, which is useful when auditing for unassigned employees.

Code Example:
```
-- INNER JOIN: only employees with a department
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- LEFT OUTER JOIN: all employees, department NULL if unassigned
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
```

Interview Tip:
A concise interview answer is:

"INNER JOIN only returns rows that match in both tables, while OUTER JOIN -- LEFT, RIGHT, or FULL -- also includes unmatched rows from one or both sides, filling in NULLs for the missing columns. I'd use a LEFT JOIN, for example, to find employees with no assigned department, since an INNER JOIN would silently exclude them."