假设有两张表: 学生表students和课程表courses，现在要查询学生名和课程名。
-- students表
+----+---------+-----------+
| id | name | course_id |
+----+---------+-----------+
| 1 | Alice | 1 |
| 2 | Bob | 2 |
| 3 | Charlie | NULL |
+----+---------+-----------+
-- courses表
+----+----------+
| id | course |
+----+----------+
| 1 | Math |
| 2 | Science |
| 3 | History |
+----+----------+
直接连表查询，也就是隐式的INNER JOIN查询，返回两个表中满足条件的所有记录
-- 隐式INNER JOIN查询
SELECT s.name, c.course
FROM students s, courses c
WHERE s.course_id = c.id;
结果:
-----+-------+
name |course |
-----+-------+
Alice|Math |
Bob |Science|
-----+-------+
INNER JOIN
返回两个表中满足连接条件的所有记录。如果两个表中没有匹配的记录，结果集中将不会包含这些记录。
-- 显式INNER JOIN查询
SELECT s.name, c.course
FROM students s
INNER JOIN courses c ON s.course_id = c.id;
结果:
-----+-------+
name |course |
-----+-------+
Alice|Math |
Bob |Science|
-----+-------+
LEFT JOIN
返回左表中的所有记录以及右表中满足连接条件的记录。如果右表中没有匹配的记录，结果集中相应位置将包含NULL。
SELECT s.name, c.course
FROM students s
LEFT JOIN courses c ON s.course_id = c.id;
结果:
-------+-------+
name |course |
-------+-------+
Alice |Math |
Bob |Science|
Charlie| |
-------+-------+
RIGHT JOIN
返回右表中的所有记录以及左表中满足连接条件的记录。如果左表中没有匹配的记录，结果集中相应位置将包含NULL。
SELECT s.name, c.course
FROM students s
RIGHT JOIN courses c ON s.course_id = c.id;
结果:
-----+-------+
name |course |
-----+-------+
Alice|Math |
Bob |Science|
|History|
-----+-------+
FULL JOIN
回左表和右表中所有满足连接条件的记录，以及两表中不满足连接条件的所有记录。不匹配的记录相应位置将包含NULL。
SELECT s.name, c.course
FROM students s
FULL JOIN courses c ON s.course_id = c.id;
-- MySQL不支持FULL JOIN
-- 使用LEFT JOIN + UNION + RIGHT JOIN实现FULL JOIN
SELECT s.name, c.course
FROM students s
LEFT JOIN courses c ON s.course_id = c.id
UNION
SELECT s.name, c.course
FROM students s
RIGHT JOIN courses c ON s.course_id = c.id;
结果:
-------+-------+
name |course |
-------+-------+
Alice |Math |
Bob |Science|
Charlie| |
|History|
-------+-------+
CROSS JOIN
返回两个表的笛卡尔积，即将每个左表记录与每个右表记录进行组合。通常在需要生成所有可能的组合时使用CROSS JOIN。
SELECT s.name, c.course
FROM students s
CROSS JOIN courses c;
结果:
-------+-------+
name |course |
-------+-------+
Alice |Math |
Bob |Math |
Charlie|Math |
Alice |Science|
Bob |Science|
Charlie|Science|
Alice |History|
Bob |History|
Charlie|History|
-------+-------+
附上建表和测试数据的SQL语句
-- 创建courses表
CREATE TABLE courses (
id INT PRIMARY KEY,
course VARCHAR(255) NOT NULL
);
-- 插入预制数据到courses表
INSERT INTO courses (id, course) VALUES
(1, 'Math'),
(2, 'Science'),
(3, 'History');
-- 创建students表
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
course_id INT,
FOREIGN KEY (course_id) REFERENCES courses(id)
);
-- 插入预制数据到students表
INSERT INTO students (id, name, course_id) VALUES
(1, 'Alice', 1),
(2, 'Bob', 2),
(3, 'Charlie', NULL);