INNER JOIN / JOIN
Returns only rows where the join condition matches in both tables.
SELF JOIN
A table joins itself to compare rows within the same table.
LEFT JOIN / LEFT OUTER JOIN
Returns all rows from the left table and matching rows from the right table. If no match is found, NULL values are returned.
RIGHT JOIN / RIGHT OUTER JOIN
Returns all rows from the right table and matching rows from the left table.
FULL JOIN / FULL OUTER JOIN
Returns all rows from both tables. If no match is found, NULLs are returned.
CROSS JOIN
Produces the Cartesian product (all possible combinations of rows from both tables).
NATURAL
A modifier that can apply to INNER, LEFT, RIGHT, FULL or CROSS joins.
It automatically uses all columns with the same name in both tables as the join condition and keeps only one copy of those columns in the result.
SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
INNER JOIN employees e2
ON e1.manager_id = e2.id;
| id | name | manager_id |
|---|---|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Charlie | 1 |
| 4 | David | 2 |
| 5 | Eve | 2 |
| Employee | Manager |
|---|---|
| Bob | Alice |
| Charlie | Alice |
| David | Bob |
| Eve | Bob |
SELECT students.name,students.clubNumber,clubs.clubName
FROM students
LEFT JOIN clubs
on student.clubNumber = clubs.clubNumber
SELECT
employees.name,
departments.department_name
FROM employees
CROSS JOIN departments;
SELECT *
FROM table1, table2
WHERE table1.id = table2.id;
就是 SQL 里最早期的连接语法，叫 隐式连接 (implicit join)。
FROM 子句里用逗号 , 分隔多张表。