How does SQL JOIN work?

SQL JOIN is used to combine rows from two or more tables based on a related column between them.

Main types of JOIN:

• INNER JOIN — returns only the rows where there are matches in both tables.
• LEFT JOIN (LEFT OUTER JOIN) — returns all rows from the left table and the matching rows from the right table; if there is no match, the result is NULL on the right side.
• RIGHT JOIN (RIGHT OUTER JOIN) — the opposite, returns all from the right and matching from the left.
• FULL JOIN (FULL OUTER JOIN) — returns all rows from both tables, filling with NULL where there are no matches.

Example:

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

Here, we get employee names and their department names, linking tables by department_id.