MySQL - 完全连接

你好,有抱负的数据库爱好者们!今天,我们将深入MySQL的精彩世界,探索我们SQL工具包中的一个强大工具:完全连接(Full Join)。如果你是编程新手,不用担心;我会一步一步地引导你了解这个概念,就像我多年来教导无数学生一样。所以,拿起一杯咖啡(或者如果你喜欢,一杯茶),让我们一起踏上这个学习冒险之旅!

MySQL - Full Join

什么是完全连接?

在我们跳入MySQL中完全连接的细节之前,让我们从一个简单的类比开始。想象你正在计划一个派对,你有两个宾客名单:一个来自你最好的朋友,另一个来自你的兄弟姐妹。完全连接就像是将这两个名单合并在一起,包括即使只在其中一个名单上出现的所有人。

在数据库术语中,完全连接基于两个或多个表之间的相关列合并行,包括所有表中的所有行,即使在其他表( tables)中没有匹配的行。

语法

以下是SQL中完全连接的基本语法:

SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

现在,我几乎可以听到你在说:“等等!MySQL没有FULL JOIN关键字!”你说得完全正确!MySQL直接支持FULL JOIN,但别担心——我们有一个使用UNION的巧妙替代方法。让我们详细探讨一下。

MySQL 完全连接(使用UNION)

由于MySQL没有内置的FULL JOIN,我们可以使用LEFT JOIN和RIGHT JOIN的组合与UNION来模拟它。下面是如何操作的:

SELECT t1.column1, t1.column2, t2.column1, t2.column2
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
UNION
SELECT t1.column1, t1.column2, t2.column1, t2.column2
FROM table1 t1
RIGHT JOIN table2 t2 ON t1.id = t2.id;

让我们分解一下:

  1. 我们执行一个LEFT JOIN来获取table1的所有行,以及与table2匹配的行。
  2. 然后,我们使用UNION将这个结果与一个RIGHT JOIN组合,RIGHT JOIN获取table2的所有行,以及与table1匹配的行。
  3. UNION结合这些结果,有效地给我们一个完全连接。

实际示例

为了使这个更具体,让我们使用一个现实世界的例子。想象我们正在管理一个小型图书馆,我们有两个表:booksborrowers

首先,让我们创建这些表:

CREATE TABLE books (
book_id INT PRIMARY KEY,
title VARCHAR(100),
author VARCHAR(100)
);

CREATE TABLE borrowers (
borrower_id INT PRIMARY KEY,
book_id INT,
borrower_name VARCHAR(100)
);

INSERT INTO books VALUES (1, '杀死一只知更鸟', '哈珀·李');
INSERT INTO books VALUES (2, '1984', '乔治·奥威尔');
INSERT INTO books VALUES (3, '傲慢与偏见', '简·奥斯汀');

INSERT INTO borrowers VALUES (1, 1, '爱丽丝');
INSERT INTO borrowers VALUES (2, NULL, '鲍勃');
INSERT INTO borrowers VALUES (3, 4, '查理');

现在,让我们执行我们的完全连接:

SELECT b.book_id, b.title, br.borrower_id, br.borrower_name
FROM books b
LEFT JOIN borrowers br ON b.book_id = br.book_id
UNION
SELECT b.book_id, b.title, br.borrower_id, br.borrower_name
FROM books b
RIGHT JOIN borrowers br ON b.book_id = br.book_id;

这个查询将给我们以下结果:

book_id title borrower_id borrower_name
1 杀死一只知更鸟 1 爱丽丝
2 1984 NULL NULL
3 傲慢与偏见 NULL NULL
NULL NULL 2 鲍勃
NULL NULL 3 查理

让我们分析一下这个结果:

  • 我们看到了所有的书,即使是那些没有被借阅的(比如'1984'和'傲慢与偏见')。
  • 我们看到了所有的借阅者,即使是那些目前没有借阅书籍的人(比如鲍勃)。
  • 我们甚至看到了查理,他正在借阅我们books表中没有的书籍(book_id 4)。

这就是完全连接的力量——它给了我们数据的完整画面,突出了我们表之间的匹配和不匹配。

带WHERE子句的完全连接

有时,我们可能想要过滤我们的完全连接结果。我们可以通过在查询中添加WHERE子句来实现这一点。例如,假设我们想要找到所有当前没有被借阅的书籍:

SELECT b.book_id, b.title, br.borrower_id, br.borrower_name
FROM books b
LEFT JOIN borrowers br ON b.book_id = br.book_id
WHERE br.borrower_id IS NULL
UNION
SELECT b.book_id, b.title, br.borrower_id, br.borrower_name
FROM books b
RIGHT JOIN borrowers br ON b.book_id = br.book_id
WHERE b.book_id IS NULL;

这个查询将返回:

book_id title borrower_id borrower_name
2 1984 NULL NULL
3 傲慢与偏见 NULL NULL
NULL NULL 2 鲍勃
NULL NULL 3 查理

这个结果向我们展示了:

  • 没有被借阅的书籍('1984'和'傲慢与偏见')
  • 目前没有借阅任何书籍的借阅者(鲍勃)
  • 借阅了我们books表中没有的书籍的借阅者(查理)

结论

亲爱的学生们,以上就是我们在MySQL中完全连接的旅程,从理解概念到使用现实世界示例实现它。记住,虽然MySQL没有内置的FULL JOIN,但我们可以使用LEFT JOIN、RIGHT JOIN和UNION的组合来实现相同的结果。

完全连接在我们需要查看多个表中的所有数据,无论它们之间是否有匹配的值时非常有用。它们就像派对上的包容朋友,确保每个人都参与其中,无论他们是否认识彼此!

在你继续MySQL冒险的过程中,请用不同的场景进行练习。你越多地玩转这些概念,就会越感到舒适。谁知道呢?你可能发现自己成为朋友中的数据库大师!

快乐编码,愿你的查询总是返回你所期望的结果!

Credits: Image by storyset