MySQL 中 JOIN 的使用实例详解

什么是 JOIN

在 MySQL 中，JOIN 操作是将两个或多个表连接起来，通过某些关联条件来获取数据的过程。JOIN 操作是所有 SQL 查询中最常用和最重要的操作之一。MySQL 支持不同类型的 JOIN，包括 INNER JOIN，LEFT JOIN，RIGHT JOIN 等等。

JOIN 的语法

MySQL JOIN 的语法如下：

SELECT column1, column2, ...
FROM table1
JOIN table2 ON condition

其中，condition 是一个逻辑表达式，用于将 table1 和 table2 之间的关联列进行匹配。可以在 condition 中使用 =，<>，<，<=，> 或 >= 等逻辑操作符。

JOIN 的类型

MySQL 中支持多种类型的 JOIN，包括 INNER JOIN，LEFT JOIN，RIGHT JOIN 和 FULL OUTER JOIN。下面分别对这些 JOIN 操作进行详细的介绍。

INNER JOIN

INNER JOIN 也被称为等值连接操作，它是通过一个条件表达式在两个或多个表之间进行连接。这个条件表达式可以是任意逻辑操作符，但通常使用等号操作符。

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON condition

LEFT JOIN

LEFT JOIN 操作返回左表中的所有记录，以及右表中符合条件的记录。如果右表中没有匹配的记录，则会填充 NULL 值。

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON condition

RIGHT JOIN

RIGHT JOIN 与 LEFT JOIN 相反，返回右表中的所有记录，以及左表中符合条件的记录。如果左表中没有匹配的记录，则会填充 NULL 值。

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON condition

FULL OUTER JOIN

FULL OUTER JOIN 返回两个表中的所有记录，如果有不匹配的记录，则会在另一个表中填充 NULL 值。

SELECT column1, column2, ...
FROM table1
FULL OUTER JOIN table2 ON condition

JOIN 的示例

示例一

假设有两个表 students 和 scores，其中 students 表包含学生的基本信息，scores 表包含学生的考试成绩。

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(25),
  gender VARCHAR(10),
  age INT
);

INSERT INTO students (name, gender, age)
VALUES
  ('Tom', 'male', 19),
  ('Lucy', 'female', 18),
  ('Lily', 'female', 20),
  ('John', 'male', 22);

CREATE TABLE scores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT,
  course_name VARCHAR(25),
  score INT
);

INSERT INTO scores (student_id, course_name, score)
VALUES
  (1, 'Math', 80),
  (1, 'English', 75),
  (2, 'Math', 90),
  (2, 'English', 85),
  (3, 'Math', 85),
  (3, 'English', 90),
  (4, 'Math', 75),
  (4, 'English', 70);

现在需要查询 students 表和 scores 表中的数据，并将它们组合在一起，以便于分析每个学生的成绩。具体的 SQL 如下：

SELECT students.name, students.gender, students.age, scores.course_name, scores.score
FROM students
LEFT JOIN scores
ON students.id = scores.student_id;

运行以上 SQL 语句，我们就可以看到每个学生的基本信息以及他们参加的考试和成绩。

+------+--------+-----+------------+-------+
| name | gender | age | course_name | score |
+------+--------+-----+------------+-------+
| Tom  | male   |  19 | Math       |    80 |
| Tom  | male   |  19 | English    |    75 |
| Lucy | female |  18 | Math       |    90 |
| Lucy | female |  18 | English    |    85 |
| Lily | female |  20 | Math       |    85 |
| Lily | female |  20 | English    |    90 |
| John | male   |  22 | Math       |    75 |
| John | male   |  22 | English    |    70 |
+------+--------+-----+------------+-------+

示例二

假设现在有两个表 orders 和 customers，其中 orders 表记录客户的订单信息，customers 表记录客户的基本信息。现在需要查询每个订单的信息，并将订单信息与客户信息进行关联，以便于分析客户的消费情况。具体的 SQL 如下：

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  total_amount DECIMAL(10, 2),
  purchase_date DATE
);

INSERT INTO orders (customer_id, total_amount, purchase_date)
VALUES
  (1, 100.00, '2021-01-01'),
  (2, 200.00, '2021-01-03'),
  (3, 300.00, '2021-01-05'),
  (4, 150.00, '2021-01-10'),
  (1, 50.00, '2021-01-15'),
  (3, 250.00, '2021-01-20'),
  (2, 300.00, '2021-01-25');

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(25),
  gender VARCHAR(10),
  age INT
);

INSERT INTO customers (name, gender, age)
VALUES
  ('Tom', 'male', 19),
  ('Lucy', 'female', 18),
  ('Lily', 'female', 20),
  ('John', 'male', 22);

现在我们可以使用以下 SQL 语句查询订单信息以及对应的客户信息：

SELECT orders.id, customers.name, customers.gender, customers.age, orders.total_amount, orders.purchase_date
FROM orders
JOIN customers
ON orders.customer_id = customers.id;

运行以上 SQL 语句，我们将得到以下结果：

+----+-------+--------+-----+--------------+---------------+
| id | name  | gender | age | total_amount | purchase_date |
+----+-------+--------+-----+--------------+---------------+
|  1 | Tom   | male   |  19 | 100.00       | 2021-01-01    |
|  2 | Lucy  | female |  18 | 200.00       | 2021-01-03    |
|  3 | Lily  | female |  20 | 300.00       | 2021-01-05    |
|  4 | John  | male   |  22 | 150.00       | 2021-01-10    |
|  1 | Tom   | male   |  19 | 50.00        | 2021-01-15    |
|  3 | Lily  | female |  20 | 250.00       | 2021-01-20    |
|  2 | Lucy  | female |  18 | 300.00       | 2021-01-25    |
+----+-------+--------+-----+--------------+---------------+

总结

MySQL 中的 JOIN 操作对于实现表之间的关联查询非常重要，可以在表之间进行复杂的关联查询，从而更好地进行数据分析和处理。在编写 JOIN 操作时，不同的 JOIN 类型有着不同的功能和语法，需要根据具体的查询需求进行选择。