SQL多表查询-JOIN的用法

假设有两张表: 学生表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);