当我还是一名崭露头角的程序员并被要求查询涉及多个表的数据时,我的胃会掉下来,我想我自己,"作为软件开发人员,当它持续存在的时候,真是太好了。"
这是不必要的戏剧性。 但是说实话,知道如何在SQL中使用和区分联接可能会有些混乱。 但是,经过一些实践和关于它们如何工作的一些基本知识,它们真的一点都不好!
因此,让我们进入四个最常见的SQL连接。
假设我们有一群猫和宠物主人。
我们选择在两个SQL表中代表这些组的一些属性:cats和pet_owners。 宠物主人可以饲养许多宠物,因此我们将外键(FK)作为pet_owner_id放在cats表中。 此FK引用pet_owners的ID或主键(PK)。
现在我们已经设置了一些数据,让我们看一下四种最常见的联接类型。
内部联接
内部联接将返回两个表中都有PK和FK的行。 下面是一个示例查询,其中为那些拥有猫的人选择了pet_owners上的所有字段。
SELECT pet_owners.*
FROM pet_owners
INNER JOIN cats
ON pet_owners.id = cats.pet_owner_id
下面是运行上述查询所返回的内容的两种表示形式(我在较传统的表格旁显示"彩色"表格,以便更轻松地将值映射到上面的图片)。 由于没有pet_owner_id为4的猫,因此不会在结果中连续返回Hana。
那么,如果我们翻转查询中的表会怎样?
SELECT pet_owners.*
FROM cats
INNER JOIN pet_owners
ON pet_owners.id = cats.pet_owner_id
结果将与之前完全一样,因为内部联接在两个表的交集处都获取数据。
如果我们改为从cats表中选择所有行怎么办?
SELECT cats.*
FROM cats
INNER JOIN pet_owners
ON pet_owners.id = cats.pet_owner_id
现在我们将看到Bo由于没有宠物主人而从输出中丢失了。
左外连接
当您要跨两个表抓取数据并包括查询中指定的第一个表(即"最左"表)的所有行时,将使用左外部联接。 这是一个例子:
SELECT pet_owners.id, cats.name, cats.fur_color
FROM pet_owners
LEFT OUTER JOIN cats
ON pet_owners.id = cats.pet_owner_id
在输出中,显示所有宠物主人字段。 但是,如果宠物主人没有猫(例如ID为4的Hana),则为与猫相关的相应单元格返回NULL。
右外连接
右外部联接的操作与左外部联接的操作相同,只不过指定的第二个表(即"最右"表)中的所有行都包含在查询输出中。 让我们看一看与之前完全相同的示例,但是使用右外部联接而不是左外部联接:
SELECT pet_owners.id, cats.name, cats.fur_color
FROM pet_owners
RIGHT OUTER JOIN cats
ON pet_owners.id = cats.pet_owner_id
现在,我们可以看到所有猫的数据,但是在与所有者不相关的猫的宠物主人单元中看到NULL。
完全外部联接
SELECT pet_owners.id AS pet_owner_id,
pet_owners.name AS pet_owner_name,
cats.id AS cat_id,
cats.name AS cat_name
FROM pet_owners
FULL OUTER JOIN cats
ON pet_owners.id = cats.pet_owner_id
由于代表了cats和pet_owners中的所有条目,因此从该查询返回具有5行输出的表。
澄清说明
那"左联接"呢?
在某些SQL版本中,您可以省略语法的"外部"部分并执行完全相同的查询。 换句话说,"左连接"在语义上等效于"左外部连接"(对"右"和"完整"也相同)。
并非所有的SQL数据库都支持每种连接类型
例如,MySQL不支持"完全外部联接",而PostgreSQL支持。 查看特定的数据库API文档,以查看可用的命令。
还有哪些其他联接?
在某些SQL API中,支持交叉联接,这使您可以创建每个表中每一行的组合。 这些都不是超级常见的,我从来没有亲自处理过。
如果您想尝试使用这些查询来加深舒适度,请随时使用具有预定义架构和条目的DB Fiddle。
(本文翻译自Julianna Roen的文章《A Visual Guide to SQL Joins》,参考:https://medium.com/swlh/learn-sql-joins-once-and-for-all-d5d9078eee7c)