SQL联接教程:交叉联接,完全外部联接,内部联接,左联接和右联接。

SQL联接使我们的关系数据库管理系统成为关系型的。

联接使我们能够将分离的数据库表重新构造为为应用程序提供动力的关系。

在本文中,我们将研究SQL中的每种不同的联接类型以及如何使用它们。

这是我们要介绍的内容:

提示:我们将介绍五种不同的类型,但您实际上只需要知道其中两种即可!)

什么是联接?

一个加盟是一个操作,结合两排连成一排。

这些行通常来自两个不同的表,但是不一定必须如此。

在介绍如何编写联接本身之前,让我们看一下联接的结果。

让我们以一个存储有关用户及其地址信息的系统为例。

表中存储用户信息的行可能如下所示:

 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);

我们的两个表lettersnumbers仅有一列:一个简单的文本字段。

现在让我们将它们与一起加入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

FULL OUTER JOIN与a有一些相似之处CROSS JOIN,但有几个关键区别。

第一个区别是a FULL OUTER JOIN需要联接条件。

连接条件指定两个表之间的行如何相互关联以及应根据什么条件将它们连接在一起。

在我们的示例中,我们的movies表通过该director_id列引用了director ,并且该列与iddirectors表的列匹配。这是我们将用作连接条件的两列。

这是我们在两个表之间编写此联接的方式:

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其他表没有匹配项的值。

注意:如果您不熟悉NULL值,请参阅此SQL操作员教程中的解释

我们还看到CROSS JOIN和之间的另一个区别FULL OUTER JOIN。A FULL OUTER JOIN从每个表中返回一个不同的行,与CROSS JOIN具有多个表的行不同。

INNER JOIN

下一个联接类型,INNER JOIN是最常用的联接类型之一。

内部联接仅返回联接条件为true的行。

在我们的示例中,我们的moviesdirectors表之间的内部联接只会返回已为电影指定导演的记录。

语法基本上与以前相同:

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

接下来的两种联接类型使用修饰符(LEFTRIGHT),该修饰符会影响结果集中包括哪个表的数据。

注:LEFT JOINRIGHT JOIN也可以被称为LEFT OUTER JOINRIGHT 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 JOINLEFT JOIN其余工作完全相同。

在右联接中,返回“右”表中的所有行。根据连接条件有条件地返回“ left”表。

让我们使用与上述相同的查询,但替换LEFT JOINRIGHT 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 JOINRIGHT JOIN在生产中的应用

在生产应用程序中,我只使用过,LEFT JOIN而从未使用过RIGHT JOIN

我这样做是因为,我认为a LEFT JOIN使查询更易于阅读和理解。

当我编写查询时,我想考虑从“基准”结果集开始,说出所有电影,然后从该基准中引入(或减去)事物。

因为我喜欢从基础开始,所以LEFT JOIN适合这种思路。我需要基本表(“左”表)中的所有行,并且有条件地希望“右”表中的行。

在实践中,我认为我从未RIGHT JOIN在生产应用程序中见过。—没什么错,RIGHT JOIN我只是认为这会使查询更难以理解。

改写 RIGHT JOIN

如果我们想将场景翻转过来,而是返回所有导演并有条件地返回他们的电影,则可以轻松地将改写RIGHT JOINLEFT JOIN

我们需要做的就是翻转查询中表的顺序,然后更改RIGHTLEFT

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出售的票。

现在,让我们加入directorsmovies再-and moviestickets

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 JOINLEFT JOIN

点击阅读原文

本文来自投稿,不代表微擎百科立场,如若转载,请注明出处:https://www.w7.wiki/develop/4264.html

发表评论

登录后才能评论