How does INNER JOIN differ from OUTER JOIN?

INNER JOIN returns only those rows that have matches in both tables based on the join condition. If there is no corresponding record in one of the tables, that row will not be included in the result.

OUTER JOIN returns all rows from one table and the matching rows from the other. If there are no matches, NULLs will be shown in the places of missing data. OUTER JOIN has three types:

• LEFT OUTER JOIN — all rows from the left table + matching rows from the right;
• RIGHT OUTER JOIN — all rows from the right table + matching rows from the left;
• FULL OUTER JOIN — all rows from both tables, with NULLs in places where there are no matches.

Example:

SELECT * FROM employees INNER JOIN departments ON employees.dept_id = departments.id;
-- returns only employees who have a department

SELECT * FROM employees LEFT OUTER JOIN departments ON employees.dept_id = departments.id;
-- returns all employees, even if they do not have a department (columns from departments will be NULL)