SQL Joins
SQLTypes of Joins
| Join Type | Description |
|---|---|
| (INNER) JOIN | Only matching rows |
| LEFT (OUTER) JOIN | All left, matching right |
| RIGHT (OUTER) JOIN | All right, matching left |
| FULL (OUTER) JOIN | All rows from both |
| CROSS JOIN | Every combination of rows |
| IMPLICIT JOIN | Equivalent to INNER JOIN |
INNER JOIN
Returns only the rows where there is a match in both tables.
SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;
LEFT JOIN (or LEFT OUTER JOIN)
Returns all rows from the left table and matched rows from the right table.
If no match, NULLs are returned for right table.
SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;
RIGHT JOIN (or RIGHT OUTER JOIN)
Returns all rows from the right table and matched rows from the left table.
SELECT employees.name, departments.name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;
FULL OUTER JOIN
Returns rows when there is a match in one of the tables. If there is no match, NULLs are used.
SELECT employees.name, departments.name
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.id;
CROSS JOIN
Returns the Cartesian product of the two tables.
Every row in the first table is paired with every row in the second.
SELECT employees.name, departments.name
FROM employees
CROSS JOIN departments;
IMPLICIT JOIN (Legacy)
This is equivalent to an INNER JOIN, but without using the JOIN keyword.
SELECT employees.name, departments.name
FROM employees, departments
WHERE employees.department_id = departments.id;
This works the same as:
SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;