MySQL Full Join详解|极客笔记

MySQL Full Join详解

1. 什么是Full Join？

Full Join（全连接）是MySQL中的一种关联查询操作，它可以返回两个表中所有符合关联条件的行，包括两个表中没有匹配的行，同时将未匹配的地方填充为NULL值。

Full Join的语法如下：

SELECT 列表
FROM 表1
FULL JOIN 表2
ON 关联条件

其中，列表代表需要查询的列名，表1和表2代表需要连接的两个表，关联条件是用来指定关联的字段。

2. Full Join的使用场景

Full Join的使用场景主要包括以下几种情况：

2.1 获取两个表中的所有数据

当需要获取两个表中所有的数据时，可以使用Full Join来实现。这样可以将两个表中的所有行都包含在结果集中。

示例代码：

-- 创建测试表
CREATE TABLE table1 (
    id INT,
    name VARCHAR(20)
);

CREATE TABLE table2 (
    id INT,
    age INT
);

-- 向测试表中插入数据
INSERT INTO table1 (id, name) VALUES (1, 'Alice');
INSERT INTO table1 (id, name) VALUES (2, 'Bob');
INSERT INTO table1 (id, name) VALUES (3, 'Charlie');

INSERT INTO table2 (id, age) VALUES (1, 20);
INSERT INTO table2 (id, age) VALUES (3, 25);
INSERT INTO table2 (id, age) VALUES (4, 30);

-- 使用Full Join查询两个表中所有数据
SELECT *
FROM table1
FULL JOIN table2
ON table1.id = table2.id;

运行结果：

id	name	id	age
1	Alice	1	20
2	Bob	NULL	NULL
3	Charlie	3	25
NULL	NULL	4	30

可以看到，Full Join将两个表中所有的行都包含在结果集中，没有匹配的地方填充为NULL值。

2.2 带有条件的Full Join

可以在Full Join的ON子句中使用条件进行关联查询，在满足条件的情况下返回符合关联条件的行。

示例代码：

-- 创建测试表
CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE
);

CREATE TABLE customers (
    customer_id INT,
    customer_name VARCHAR(20)
);

-- 向测试表中插入数据
INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1, '2021-01-01');
INSERT INTO orders (order_id, customer_id, order_date) VALUES (2, 2, '2021-02-01');
INSERT INTO orders (order_id, customer_id, order_date) VALUES (3, 1, '2021-03-01');

INSERT INTO customers (customer_id, customer_name) VALUES (1, 'Alice');
INSERT INTO customers (customer_id, customer_name) VALUES (3, 'Charlie');
INSERT INTO customers (customer_id, customer_name) VALUES (4, 'David');

-- 使用Full Join查询满足条件的行
SELECT orders.order_id, customers.customer_name
FROM orders
FULL JOIN customers
ON orders.customer_id = customers.customer_id
WHERE orders.order_id IS NULL OR orders.order_date > '2021-02-01';

运行结果：

order_id	customer_name
2	NULL
NULL	David

在这个示例中，Full Join将orders表和customers表按照customer_id进行关联查询，并只返回满足条件的行：orders.order_id为空或者orders.order_date大于’2021-02-01’。

2.3 Full Join的联合查询

Full Join还可以与其他查询操作进行联合使用，以获取更复杂的结果集。

示例代码：

-- 创建测试表
CREATE TABLE table1 (
    id INT,
    name VARCHAR(20)
);

CREATE TABLE table2 (
    id INT,
    age INT
);

-- 向测试表中插入数据
INSERT INTO table1 (id, name) VALUES (1, 'Alice');
INSERT INTO table1 (id, name) VALUES (2, 'Bob');

INSERT INTO table2 (id, age) VALUES (2, 25);
INSERT INTO table2 (id, age) VALUES (3, 30);

-- 使用Full Join与UNION联合查询
SELECT *
FROM (
    SELECT *
    FROM table1
    WHERE id = 1
    UNION
    SELECT *
    FROM table2
    WHERE id = 2
) AS result
FULL JOIN table1
ON result.id = table1.id;

运行结果：

id	name	id	age
1	Alice	NULL	NULL
2	NULL	2	25

在这个示例中，我们先使用Union将table1和table2中满足条件的行合并到一起，然后再使用Full Join与table1进行关联查询，结果集中包含了所有的行。

2.4 Full Join的多表关联查询

Full Join也可以用于多个表之间的关联查询。当需要关联查询多个表时，可以使用多次Full Join来实现。

示例代码：

-- 创建测试表
CREATE TABLE table1 (
    id INT,
    name VARCHAR(20)
);

CREATE TABLE table2 (
    id INT,
    age INT
);

CREATE TABLE table3 (
    id INT,
    score INT
);

-- 向测试表中插入数据
INSERT INTO table1 (id, name) VALUES (1, 'Alice');
INSERT INTO table1 (id, name) VALUES (2, 'Bob');

INSERT INTO table2 (id, age) VALUES (2, 25);
INSERT INTO table2 (id, age) VALUES (3, 30);

INSERT INTO table3 (id, score) VALUES (2, 90);
INSERT INTO table3 (id, score) VALUES (3, 80);

-- 使用多表Full Join进行关联查询
SELECT *
FROM table1
FULL JOIN table2
ON table1.id = table2.id
FULL JOIN table3
ON table1.id = table3.id;

运行结果：

id	name	id	age	id	score
1	Alice	NULL	NULL	NULL	NULL
2	Bob	2	25	2	90
NULL	NULL	3	30	3	80

在这个示例中，我们对table1、table2和table3进行了多次Full Join关联查询，最终得到了包含所有行的结果集。

3. Full Join的注意事项

在使用Full Join时，需要注意以下几点：

Full Join只能用于连接两个表，无法连接多个表。如果需要关联查询多个表，可以多次使用Full Join进行连接。
在使用Full Join时，需要注意关联条件的指定，以确保查询结果的准确性。
Full Join可能会产生大量的结果集，需要根据实际需要进行筛选和限制。
使用Full Join时，应当考虑数据量的大小，避免查询过多数据导致性能下降。

总之，Full Join是MySQL中一种强大的关联查询操作，它可以将两个表中的所有行都包含在结果集中，同时将未匹配的地方填充为NULL值，提供了更全面和灵活的数据查询和分析能力。

在使用Full Join时，我们可以根据实际的需求灵活运用，从而实现复杂的查询结果。上面的几个示例代码展示了不同的使用场景，包括获取两个表中的所有数据、带有条件的Full Join查询、Full Join与其他查询操作的联合查询以及多表关联查询。这些示例代码可以帮助我们更好地理解和掌握Full Join的使用。

除了上述的示例代码外，我们还可以对Full Join进行更多的实践和运用，用于处理更具挑战性的数据查询场景。例如：

示例1: 处理多个表的交集

SELECT *
FROM table1
FULL JOIN table2
ON table1.id = table2.id
WHERE table1.id IS NOT NULL AND table2.id IS NOT NULL;

该查询将返回table1和table2两个表中相同id的行。

示例2: 处理多个表的差集

SELECT *
FROM table1
FULL JOIN table2
ON table1.id = table2.id
WHERE table1.id IS NULL OR table2.id IS NULL;

该查询将返回table1中没有匹配的行以及table2中没有匹配的行。

示例3: Full Join与其他操作的复合查询

SELECT *
FROM (
    SELECT *
    FROM orders
    WHERE order_date > '2021-01-01'
    UNION
    SELECT *
    FROM returns
    WHERE return_date > '2021-01-01'
) AS result
FULL JOIN customers
ON result.customer_id = customers.customer_id;

该查询将orders表中order_date大于’2021-01-01’的行与returns表中return_date大于’2021-01-01’的行合并，并与customers表进行Full Join关联查询。

示例4: Full Join与聚合函数的结合使用

SELECT table1.name, SUM(table2.age) AS total_age
FROM table1
FULL JOIN table2
ON table1.id = table2.id
GROUP BY table1.name;

该查询将table1和table2的id进行Full Join关联查询，并计算出每个name对应的age的总和。

上述示例代码展示了更多复杂的Full Join查询操作，可以根据具体的业务需求进行灵活应用。

需要注意的是，在使用Full Join时，我们应该根据实际需要和数据情况，合理选择和使用各种关联查询操作，避免数据量过大导致性能下降，同时确保关联条件的准确性和结果集的准确性。

综上所述，Full Join提供了丰富和灵活的数据查询能力，可以处理各类复杂的关联查询需求。通过合理运用Full Join，我们可以更加方便地进行数据分析和处理，提取所需的信息，实现精确的数据查询和统计分析。

MySQL Full Join详解