SQL联接使我们的关系数据库管理系统成为关系型的。
联接使我们能够将分离的数据库表重新构造为为应用程序提供动力的关系。
在本文中,我们将研究SQL中的每种不同的联接类型以及如何使用它们。
这是我们要介绍的内容:
- 什么是联接?
- 设置数据库
CROSS JOIN
- 设置示例数据(导演和电影)
FULL OUTER JOIN
INNER JOIN
LEFT JOIN
/RIGHT JOIN
- 使用过滤
LEFT JOIN
- 多个联接
- 有额外条件加入
- 关于使用联接编写查询的现实
(提示:我们将介绍五种不同的类型,但您实际上只需要知道其中两种即可!)
什么是联接?
一个加盟是一个操作,结合两排连成一排。
这些行通常来自两个不同的表,但是不一定必须如此。
在介绍如何编写联接本身之前,让我们看一下联接的结果。
让我们以一个存储有关用户及其地址信息的系统为例。
表中存储用户信息的行可能如下所示:
id | name | email | age ----+--------------+---------------------+----- 1 | John Smith | johnsmith@gmail.com | 25 2 | Jane Doe | janedoe@Gmail.com | 28 3 | Xavier Wills | xavier@wills.io | 3 ... (7 rows)
表中存储地址信息的行可能看起来像这样:
id | street | city | state | user_id ----+-------------------+---------------+-------+--------- 1 | 1234 Main Street | Oklahoma City | OK | 1 2 | 4444 Broadway Ave | Oklahoma City | OK | 2 3 | 5678 Party Ln | Tulsa | OK | 3 (3 rows)
我们可以编写单独的查询来检索用户信息和地址信息,但理想情况下,我们可以编写一个查询并在同一结果集中接收所有用户及其地址。
这正是联接使我们能够做到的!
我们将很快研究如何编写这些联接,但是如果将用户信息联接到地址信息中,我们将得到如下结果:
id | name | email | age | id | street | city | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
1 | John Smith | johnsmith@gmail.com | 25 | 1 | 1234 Main Street | Oklahoma City | OK | 1
2 | Jane Doe | janedoe@Gmail.com | 28 | 2 | 4444 Broadway Ave | Oklahoma City | OK | 2
3 | Xavier Wills | xavier@wills.io | 35 | 3 | 5678 Party Ln | Tulsa | OK | 3
(3 rows)
在这里,我们在一个不错的结果集中看到了所有用户及其地址。
除了产生组合的结果集之外,联接的另一个重要用途是将额外的信息提取到我们可以过滤的查询中。
例如,如果我们想向居住在俄克拉何马城的所有用户发送一些物理邮件,则可以使用此合并在一起的结果集并根据该city
列进行过滤。
现在我们知道了联接的目的,让我们开始写一些吧!
设置数据库
在编写查询之前,我们需要设置数据库。
对于这些示例,我们将使用PostgreSQL,但是此处显示的查询和概念将轻松转换为任何其他现代数据库系统(例如MySQL,SQL Server等)。
要使用我们的PostgreSQL数据库,我们可以使用psql
—交互式PostgreSQL命令行程序。如果您有另一个喜欢的数据库客户端,那也很好。
首先,让我们创建数据库。在已经安装 PostgreSQL的情况下,我们可以createdb <database-name>
在终端上运行命令以创建新数据库。我打电话给我fcc
:
$ createdb fcc
接下来,让我们使用命令启动交互式控制台,并使用以下命令psql
连接到我们刚建立的数据库\c <database-name>
:
$ psql psql (11.5) Type "help" for help. john=# \c fcc You are now connected to database "fcc" as user "john". fcc=#
注意:我已经整理了
psql
这些示例中的输出,以使其更易于阅读,所以请不要担心此处显示的输出与您在终端中看到的输出不完全相同。
我鼓励您遵循这些示例并亲自运行这些查询。通过研究这些示例,而不是仅仅阅读它们,您将学到更多并记住很多东西。
现在加入联接!
CROSS JOIN
最简单的一种加入我们可以做的是一个CROSS JOIN
或“笛卡尔积”。
此联接从一个表中获取每一行,并将其与另一表的每一行联接。
如果我们有两个列表(一个包含一个1, 2, 3
,另一个包含)A, B, C
,则这两个列表的笛卡尔积将为:
1A, 1B, 1C 2A, 2B, 2C 3A, 3B, 3C
第一个列表中的每个值与第二个列表中的每个值配对。
让我们将此示例写为SQL查询。
首先,我们创建两个非常简单的表并将一些数据插入其中:
CREATE TABLE letters( letter TEXT ); INSERT INTO letters(letter) VALUES ('A'), ('B'), ('C'); CREATE TABLE numbers( number TEXT ); INSERT INTO numbers(number) VALUES (1), (2), (3);
我们的两个表letters
和numbers
仅有一列:一个简单的文本字段。
现在让我们将它们与一起加入CROSS JOIN
:
SELECT * FROM letters CROSS JOIN numbers;
letter | number --------+-------- A | 1 A | 2 A | 3 B | 1 B | 2 B | 3 C | 1 C | 2 C | 3 (9 rows)
这是我们可以执行的最简单的联接类型,但是即使在这个简单的示例中,我们也可以看到联接的工作原理:将两个单独的行(from中的一行letters
和from中的一行numbers
)连接在一起形成一行。
虽然这种类型的联接通常仅作为学术示例进行讨论,但它确实至少有一个很好的用例:涵盖日期范围。
CROSS JOIN
有日期范围
一个很好的用例CROSS JOIN
是从表中获取每一行并将其应用于日期范围内的每一天。
例如,假设您正在构建一个用于跟踪日常任务的应用程序,例如刷牙,吃早餐或洗澡之类的事情。
如果您想为每个任务以及过去一周的每一天生成记录,则可以CROSS JOIN
针对日期范围使用a 。
要设置此日期范围,我们可以使用generate_series
函数:
SELECT generate_series( (CURRENT_DATE - INTERVAL '5 day'), CURRENT_DATE, INTERVAL '1 day' )::DATE AS day;
该generate_series
函数具有三个参数。
第一个参数是起始值。在此示例中,我们使用CURRENT_DATE - INTERVAL '5 day'
。这将返回当前日期减去五天(或“五天前”)。
第二个参数是当前日期(CURRENT_DATE
)。
第三个参数是“步长间隔”,即每次我们要增加多少值。由于这些是日常任务,我们将以一天(INTERVAL '1 day'
)为间隔。
放在一起,这会产生一系列日期,从五天前开始,到今天结束,一次只发生一天。
最后,通过使用将这些值的输出转换为日期来删除时间部分::DATE
,并使用对该列进行别名化AS day
以使输出更好。
该查询的输出是过去五天加上今天:
day ------------ 2020-08-19 2020-08-20 2020-08-21 2020-08-22 2020-08-23 2020-08-24 (6 rows)
回到我们的每日任务示例,让我们创建一个简单的表来保存我们要完成的任务,并插入一些任务:
CREATE TABLE tasks( name TEXT ); INSERT INTO tasks(name) VALUES ('Brush teeth'), ('Eat breakfast'), ('Shower'), ('Get dressed');
我们的tasks
表只有一列,name
我们在该表中插入了四个任务。
现在,让CROSS JOIN
我们通过查询来生成日期的任务:
SELECT tasks.name, dates.day FROM tasks CROSS JOIN ( SELECT generate_series( (CURRENT_DATE - INTERVAL '5 day'), CURRENT_DATE, INTERVAL '1 day' )::DATE AS day ) AS dates
(由于我们的日期生成查询不是实际的表,因此我们将其写为子查询。)
从此查询中,我们返回任务名称和日期,结果集如下所示:
name | day ---------------+------------ Brush teeth | 2020-08-19 Brush teeth | 2020-08-20 Brush teeth | 2020-08-21 Brush teeth | 2020-08-22 Brush teeth | 2020-08-23 Brush teeth | 2020-08-24 Eat breakfast | 2020-08-19 Eat breakfast | 2020-08-20 Eat breakfast | 2020-08-21 Eat breakfast | 2020-08-22 ... (24 rows)
如我们预期的那样,我们在日期范围内每天为每个任务获得一行。
这CROSS JOIN
是我们可以做的最简单的联接,但是要查看接下来的几种类型,我们需要一个更现实的表设置。
创作导演和电影
为了说明以下联接类型,我们将使用电影和电影导演的示例。
在这种情况下,一部电影只有一名导演,但并不需要电影中有导演-想象要宣布有一部新电影,但尚未确定导演的选择。
我们的directors
表将存储每个导演的姓名,该movies
表将存储电影的名称以及对电影导演的引用(如果有的话)。
让我们创建这两个表并将一些数据插入其中:
CREATE TABLE directors( id SERIAL PRIMARY KEY, name TEXT NOT NULL ); INSERT INTO directors(name) VALUES ('John Smith'), ('Jane Doe'), ('Xavier Wills') ('Bev Scott'), ('Bree Jensen'); CREATE TABLE movies( id SERIAL PRIMARY KEY, name TEXT NOT NULL, director_id INTEGER REFERENCES directors ); INSERT INTO movies(name, director_id) VALUES ('Movie 1', 1), ('Movie 2', 1), ('Movie 3', 2), ('Movie 4', NULL), ('Movie 5', NULL);
我们有五位导演,五部电影,其中三部电影已分配了导演。导演ID 1有两部电影,导演ID 2有一部。
FULL OUTER JOIN
现在,我们有一些数据可以处理,让我们来看一下FULL OUTER JOIN
。
A FULL OUTER JOIN
与a有一些相似之处CROSS JOIN
,但有几个关键区别。
第一个区别是a FULL OUTER JOIN
需要联接条件。
连接条件指定两个表之间的行如何相互关联以及应根据什么条件将它们连接在一起。
在我们的示例中,我们的movies
表通过该director_id
列引用了director ,并且该列与id
该directors
表的列匹配。这是我们将用作连接条件的两列。
这是我们在两个表之间编写此联接的方式:
SELECT * FROM movies FULL OUTER JOIN directors ON directors.id = movies.director_id;
请注意,我们指定的加入条件使电影与其导演匹配:ON movies.director_id = directors.id
。
我们的结果集看起来像是一种奇怪的笛卡尔积:
id | name | director_id | id | name ------+---------+-------------+------+-------------- 1 | Movie 1 | 1 | 1 | John Smith 2 | Movie 2 | 1 | 1 | John Smith 3 | Movie 3 | 2 | 2 | Jane Doe 4 | Movie 4 | NULL | NULL | NULL 5 | Movie 5 | NULL | NULL | NULL NULL | NULL | NULL | 5 | Bree Jensen NULL | NULL | NULL | 4 | Bev Scott NULL | NULL | NULL | 3 | Xavier Wills (8 rows)
我们看到的第一行是电影中有导演的行,我们的加入条件评估为true。
但是,在这些行之后,我们可以看到每个表中剩余的每个行,但是具有NULL
其他表没有匹配项的值。
我们还看到CROSS JOIN
和之间的另一个区别FULL OUTER JOIN
。A FULL OUTER JOIN
从每个表中返回一个不同的行,与CROSS JOIN
具有多个表的行不同。
INNER JOIN
下一个联接类型,INNER JOIN
是最常用的联接类型之一。
内部联接仅返回联接条件为true的行。
在我们的示例中,我们的movies
和directors
表之间的内部联接只会返回已为电影指定导演的记录。
语法基本上与以前相同:
SELECT * FROM movies INNER JOIN directors ON directors.id = movies.director_id;
我们的结果显示了三部具有导演的电影:
id | name | director_id | id | name ----+---------+-------------+----+------------ 1 | Movie 1 | 1 | 1 | John Smith 2 | Movie 2 | 1 | 1 | John Smith 3 | Movie 3 | 2 | 2 | Jane Doe (3 rows)
由于内部联接仅包含与联接条件匹配的行,因此联接中两个表的顺序无关紧要。
如果我们反转查询中表的顺序,则会得到相同的结果:
SELECT * FROM directors INNER JOIN movies ON movies.director_id = directors.id;
id | name | id | name | director_id ----+------------+----+---------+------------- 1 | John Smith | 1 | Movie 1 | 1 1 | John Smith | 2 | Movie 2 | 1 2 | Jane Doe | 3 | Movie 3 | 2 (3 rows)
由于我们directors
在此查询中首先列出了表,并且选择了所有列(SELECT *
),因此我们首先看到directors
列数据,然后再看到来自的列,movies
但是结果数据是相同的。
这是内部联接的有用属性,但并非对所有联接类型(例如我们的下一个类型)都是正确的。
LEFT JOIN
/ RIGHT JOIN
接下来的两种联接类型使用修饰符(LEFT
或RIGHT
),该修饰符会影响结果集中包括哪个表的数据。
注:该
LEFT JOIN
和RIGHT JOIN
也可以被称为LEFT OUTER JOIN
和RIGHT OUTER JOIN
。
这些联接用于查询中,我们要返回所有特定表的数据,如果有的话,还应返回关联表的数据。
如果关联的数据不存在,我们仍将取回所有“主”表的数据。
这是一个有关特定事物的信息的查询,如果存在该奖金信息,则该奖金信息。
用一个例子很容易理解。让我们找到所有电影及其导演,但我们不在乎他们是否有导演,这是一个好处:
SELECT * FROM movies LEFT JOIN directors ON directors.id = movies.director_id;
该查询遵循与以前相同的模式-我们刚刚将联接指定为LEFT JOIN
。
在此示例中,该movies
表是“左”表。
如果我们将查询写在一行上,这将使它更容易看到:
... FROM movies LEFT JOIN directors ...
左联接返回“左”表中的所有记录。
左联接返回“右”表中与联接条件匹配的所有行。
来自“右”表的与联接条件不匹配的行将作为返回NULL
。
id | name | director_id | id | name ----+---------+-------------+------+------------ 1 | Movie 1 | 1 | 1 | John Smith 2 | Movie 2 | 1 | 1 | John Smith 3 | Movie 3 | 2 | 2 | Jane Doe 4 | Movie 4 | NULL | NULL | NULL 5 | Movie 5 | NULL | NULL | NULL (5 rows)
查看该结果集,我们可以看到为什么这种类型的联接对于“所有这些,如果存在的话,其中一些”类型的查询有用。
RIGHT JOIN
除了与两个表有关的规则相反外RIGHT JOIN
,LEFT JOIN
其余工作完全相同。
在右联接中,返回“右”表中的所有行。根据连接条件有条件地返回“ left”表。
让我们使用与上述相同的查询,但替换LEFT JOIN
为RIGHT JOIN
:
SELECT * FROM movies RIGHT JOIN directors ON directors.id = movies.director_id;
id | name | director_id | id | name ------+---------+-------------+----+-------------- 1 | Movie 1 | 1 | 1 | John Smith 2 | Movie 2 | 1 | 1 | John Smith 3 | Movie 3 | 2 | 2 | Jane Doe NULL | NULL | NULL | 5 | Bree Jensen NULL | NULL | NULL | 4 | Bev Scott NULL | NULL | NULL | 3 | Xavier Wills (6 rows)
现在,我们的结果集将返回每一directors
行以及数据(如果存在)movies
。
我们要做的就是切换我们正在考虑使用哪个表的“主”表,即我们希望从中查看所有数据的表,而不管其关联数据是否存在。
LEFT JOIN
/ RIGHT JOIN
在生产中的应用
在生产应用程序中,我只使用过,LEFT JOIN
而从未使用过RIGHT JOIN
。
我这样做是因为,我认为a LEFT JOIN
使查询更易于阅读和理解。
当我编写查询时,我想考虑从“基准”结果集开始,说出所有电影,然后从该基准中引入(或减去)事物。
因为我喜欢从基础开始,所以LEFT JOIN
适合这种思路。我需要基本表(“左”表)中的所有行,并且有条件地希望“右”表中的行。
在实践中,我认为我从未RIGHT JOIN
在生产应用程序中见过。—没什么错,RIGHT JOIN
我只是认为这会使查询更难以理解。
改写 RIGHT JOIN
如果我们想将场景翻转过来,而是返回所有导演并有条件地返回他们的电影,则可以轻松地将改写RIGHT JOIN
为LEFT JOIN
。
我们需要做的就是翻转查询中表的顺序,然后更改RIGHT
为LEFT
:
SELECT * FROM directors LEFT JOIN movies ON movies.director_id = directors.id;
注意:我希望将要连接的表(在上面的示例中为“正确的”表—在上面的示例中
movies
)放在连接条件(ON movies.director_id = ...
)的前面,但这只是我个人的喜好。
使用过滤 LEFT JOIN
使用LEFT JOIN
(或RIGHT JOIN
)有两个用例。
我们已经介绍了第一个用例:从一个表返回所有行,并有条件地从另一个表返回。
第二个用例是返回第一个表中没有第二个表中数据的行。
场景如下所示:查找不属于电影的导演。
为此,我们将从a开始,LEFT JOIN
我们的directors
表将成为主表或“左”表:
SELECT * FROM directors LEFT JOIN movies ON movies.director_id = directors.id;
对于不属于电影的导演,movies
表中的列为NULL
:
id | name | id | name | director_id ----+--------------+------+---------+------------- 1 | John Smith | 1 | Movie 1 | 1 1 | John Smith | 2 | Movie 2 | 1 2 | Jane Doe | 3 | Movie 3 | 2 5 | Bree Jensen | NULL | NULL | NULL 4 | Bev Scott | NULL | NULL | NULL 3 | Xavier Wills | NULL | NULL | NULL (6 rows)
在我们的示例中,导演ID 3、4和5不属于电影。
要仅将结果集过滤到这些行,我们可以添加一个WHERE
子句以仅返回电影数据为的行NULL
:
SELECT * FROM directors LEFT JOIN movies ON movies.director_id = directors.id WHERE movies.id IS NULL;
id | name | id | name | director_id ----+--------------+------+------+------------- 5 | Bree Jensen | NULL | NULL | NULL 4 | Bev Scott | NULL | NULL | NULL 3 | Xavier Wills | NULL | NULL | NULL (3 rows)
还有我们的三位无电影导演!
通常使用id
表格的列来过滤(WHERE movies.id IS NULL
),但是movies
表格中的所有列都是NULL
—因此它们中的任何一个都可以使用。
(由于我们知道表中的所有列movies
都是NULL
,因此在上面的查询中,我们可以编写SELECT directors.*
而不是SELECT *
只返回导演的所有信息。)
使用LEFT JOIN
以查找匹配
在先前的查询中,我们发现不属于电影的导演。
使用相同的结构,通过更改条件以查找电影数据不存在的行,我们可以找到确实属于电影的导演:WHERE
NULL
SELECT * FROM directors LEFT JOIN movies ON movies.director_id = directors.id WHERE movies.id IS NOT NULL;
id | name | id | name | director_id ----+------------+----+---------+------------- 1 | John Smith | 1 | Movie 1 | 1 1 | John Smith | 2 | Movie 2 | 1 2 | Jane Doe | 3 | Movie 3 | 2 (3 rows)
这似乎很方便,但是实际上我们已经重新实现了INNER JOIN
!
多个联接
我们已经看到了如何将两个表联接在一起,但是连续多个联接又如何呢?
它实际上很简单,但是为了说明这一点,我们需要第三张表:tickets
。
该表将代表电影的售票:
CREATE TABLE tickets( id SERIAL PRIMARY KEY, movie_id INTEGER REFERENCES movies NOT NULL ); INSERT INTO tickets(movie_id) VALUES (1), (1), (3);
该tickets
表仅包含id
和对电影的引用:movie_id
。
我们还插入了两张以电影ID 1出售的票和一张以电影ID 3出售的票。
现在,让我们加入directors
到movies
再-and movies
来tickets
!
SELECT * FROM directors INNER JOIN movies ON movies.director_id = directors.id INNER JOIN tickets ON tickets.movie_id = movies.id;
由于这些是内部联接,因此编写联接的顺序无关紧要。我们可以先开始tickets
,然后加入movies
,然后再加入directors
。
再次取决于您要查询的内容以及使查询最容易理解的原因。
在结果集中,我们将注意到我们进一步缩小了返回的行的范围:
id | name | id | name | director_id | id | movie_id ----+------------+----+---------+-------------+----+---------- 1 | John Smith | 1 | Movie 1 | 1 | 1 | 1 1 | John Smith | 1 | Movie 1 | 1 | 2 | 1 2 | Jane Doe | 3 | Movie 3 | 2 | 3 | 3 (3 rows)
这是有道理的,因为我们添加了另一个INNER JOIN
。实际上,这会向我们的查询添加另一个“ AND”条件。
我们的查询从本质上说:“退回所有也有售票电影的导演。”
相反,如果我们想查找属于尚未售票电影的导演,则可以用我们的最后INNER JOIN
一个代替LEFT JOIN
:
SELECT * FROM directors JOIN movies ON movies.director_id = directors.id LEFT JOIN tickets ON tickets.movie_id = movies.id;
我们可以看到Movie 2
现在返回到结果集中:
id | name | id | name | director_id | id | movie_id ----+------------+----+---------+-------------+------+---------- 1 | John Smith | 1 | Movie 1 | 1 | 1 | 1 1 | John Smith | 1 | Movie 1 | 1 | 2 | 1 2 | Jane Doe | 3 | Movie 3 | 2 | 3 | 3 1 | John Smith | 2 | Movie 2 | 1 | NULL | NULL (4 rows)
这部电影没有任何门票销售,因此,由于,该电影先前未包含在结果集中INNER JOIN
。
我将把它留给读者练习,但是您如何找到那些没有票房电影的导演呢?
加入执行顺序
最后,我们实际上并不关心连接的执行顺序。
SQL与其他现代编程语言之间的主要区别之一是SQL是一种声明性语言。
这意味着我们指定了所需的结果,但没有指定执行详细信息,这些详细信息留给数据库查询计划程序使用。我们指定所需的联接及其条件,然后由查询计划者处理其余的联接。
但是,实际上,数据库不是同时将三个表连接在一起。相反,它可能会将前两个表连接在一起成为一个中间结果,然后将该中间结果集连接到第三个表。
(注意:这是一个简化的说明。)
因此,当我们在查询中使用多个联接时,我们可以将它们视为两个表之间的一系列联接,尽管其中一个表可能会变得很大。
有额外条件加入
我们将讨论的最后一个主题是具有附加条件的联接。
与WHERE
子句类似,我们可以在联接条件中添加任意数量的条件。
例如,如果我们要查找导演不 为 “ John Smith”的电影,则可以通过以下方式将附加条件添加到联接中AND
:
SELECT * FROM movies INNER JOIN directors ON directors.id = movies.director_id AND directors.name <> 'John Smith';
我们可以使用WHERE
在此连接条件中放入子句的任何运算符。
如果将条件放在WHERE
子句中,我们也会从该查询中获得相同的结果:
SELECT * FROM movies INNER JOIN directors ON directors.id = movies.director_id WHERE directors.name <> 'John Smith';
在这里,有一些细微的差异发生,但是就本文而言,结果集是相同的。
(如果您不熟悉用于过滤SQL查询的所有方法,请在此处查看前面提到的文章。)
关于使用联接编写查询的现实
实际上,我发现自己仅以三种不同方式使用联接:
INNER JOIN
第一个用例是两个表之间确实存在关系的记录。这是通过实现的INNER JOIN
。
这些情况类似于查找“ 有导演的电影”或“有帖子的用户”。
LEFT JOIN
第二个用例是来自一个表的记录,如果存在关系,则来自第二个表的记录。这是通过实现的LEFT JOIN
。
这些情况是“有导演的电影,如果有导演的电影”或“有职位的用户,如果有导演的电影” 。
LEFT JOIN
排除
第三最常见的用例是我们的第二个用例LEFT JOIN
:在一个表中查找与第二个表中没有关系的记录。
例如“没有导演的电影”或“没有职位的用户”。
两种非常有用的联接类型
我认为我从未在生产应用程序中使用过a FULL OUTER JOIN
或a RIGHT JOIN
。用例很少出现,或者可以用更清晰的方式(在的情况下RIGHT JOIN
)编写查询。
我偶尔使用过a之类CROSS JOIN
的功能,例如在整个日期范围内分布记录(就像我们在开始时一样),但是这种情况也不会经常出现。
所以,好消息!对于您遇到的99.9%的用例,实际上只需要了解两种类型的联接:INNER JOIN
和LEFT JOIN
!
点击阅读原文
本文来自投稿,不代表微擎百科立场,如若转载,请注明出处:https://www.w7.wiki/develop/4264.html