第1部分-联接和联合

该答案包括：

1. 第1部分
   • 使用内部联接联接两个或多个表（有关更多信息，请参见Wikipedia条目）
   • 如何使用联合查询
   • 左右外部联接（此stackOverflow答案非常适合描述联接类型）
   • 交叉查询（如果数据库不支持查询，以及如何重现查询）-这是SQL-Server的功能（请参阅info），这也是我首先编写整个内容的部分原因。
2. 第2部分
   • 子查询-它们是什么，可以在哪里使用以及需要注意什么
   • 笛卡尔加入AKA-痛苦！

有多种方法可以从数据库的多个表中检索数据。在此答案中，我将使用ANSI-92连接语法。这可能与其他使用较旧的ANSI-89语法的其他教程有所不同（如果您习惯使用89，可能看起来不那么直观-
但我只能说尝试一下），因为它 更
容易了解查询何时开始变得更复杂。为什么要使用它？有性能提升吗？在简短的回答是否定的，但它 是
更易于阅读，一旦你习惯了它。使用此语法更容易读取其他人编写的查询。

我还将使用小型堆场的概念，该堆场具有一个数据库来跟踪其可用的汽车。所有者已将您雇用为他的IT计算机人员，并希望您能够一口气就把他要求的数据丢给他。

我制作了许多最终表将使用的查找表。这将为我们提供一个合理的工作模型。首先，我将对具有以下结构的示例数据库运行查询。我将尝试思考刚开始时所犯的常见错误，并解释错误的根源-
以及当然会显示如何纠正错误。

第一张桌子只是一个颜色列表，以便我们知道车场中的颜色。

mysql> create table colors(id int(3) not null auto_increment primary key,     -> color varchar(15), paint varchar(10));Query OK, 0 rows affected (0.01 sec)mysql> show columns from colors;+-------+-------------+------+-----+---------+----------------+| Field | Type        | Null | Key | Default | Extra          |+-------+-------------+------+-----+---------+----------------+| id    | int(3)      | NO   | PRI | NULL    | auto_increment || color | varchar(15) | YES  |     | NULL    |     || paint | varchar(10) | YES  |     | NULL    |     |+-------+-------------+------+-----+---------+----------------+3 rows in set (0.01 sec)mysql> insert into colors (color, paint) values ('Red', 'metallic'),     -> ('Green', 'Gloss'), ('Blue', 'metallic'),     -> ('White' 'Gloss'), ('Black' 'Gloss');Query OK, 5 rows affected (0.00 sec)Records: 5  Duplicates: 0  Warnings: 0mysql> select * from colors;+----+-------+----------+| id | color | paint    |+----+-------+----------+|  1 | Red   | metallic ||  2 | Green | Gloss    ||  3 | Blue  | metallic ||  4 | White | Gloss    ||  5 | Black | Gloss    |+----+-------+----------+5 rows in set (0.00 sec)

品牌表标识了车库外可能出售的汽车的不同品牌。

mysql> create table brands (id int(3) not null auto_increment primary key,     -> brand varchar(15));Query OK, 0 rows affected (0.01 sec)mysql> show columns from brands;+-------+-------------+------+-----+---------+----------------+| Field | Type        | Null | Key | Default | Extra          |+-------+-------------+------+-----+---------+----------------+| id    | int(3)      | NO   | PRI | NULL    | auto_increment || brand | varchar(15) | YES  |     | NULL    |     |+-------+-------------+------+-----+---------+----------------+2 rows in set (0.01 sec)mysql> insert into brands (brand) values ('Ford'), ('Toyota'),     -> ('Nissan'), ('Smart'), ('BMW');Query OK, 5 rows affected (0.00 sec)Records: 5  Duplicates: 0  Warnings: 0mysql> select * from brands;+----+--------+| id | brand  |+----+--------+|  1 | Ford   ||  2 | Toyota ||  3 | Nissan ||  4 | Smart  ||  5 | BMW    |+----+--------+5 rows in set (0.00 sec)

模型表将涵盖不同类型的汽车，使用不同类型的汽车而不是实际的汽车模型会更简单。

mysql> create table models (id int(3) not null auto_increment primary key,     -> model varchar(15));Query OK, 0 rows affected (0.01 sec)mysql> show columns from models;+-------+-------------+------+-----+---------+----------------+| Field | Type        | Null | Key | Default | Extra          |+-------+-------------+------+-----+---------+----------------+| id    | int(3)      | NO   | PRI | NULL    | auto_increment || model | varchar(15) | YES  |     | NULL    |     |+-------+-------------+------+-----+---------+----------------+2 rows in set (0.00 sec)mysql> insert into models (model) values ('Sports'), ('Sedan'), ('4WD'), ('Luxury');Query OK, 4 rows affected (0.00 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> select * from models;+----+--------+| id | model  |+----+--------+|  1 | Sports ||  2 | Sedan  ||  3 | 4WD    ||  4 | Luxury |+----+--------+4 rows in set (0.00 sec)

最后，要捆绑所有其他表，该表将所有内容捆绑在一起。ID字段实际上是用于识别汽车的唯一批号。

mysql> create table cars (id int(3) not null auto_increment primary key,     -> color int(3), brand int(3), model int(3));Query OK, 0 rows affected (0.01 sec)mysql> show columns from cars;+-------+--------+------+-----+---------+----------------+| Field | Type   | Null | Key | Default | Extra          |+-------+--------+------+-----+---------+----------------+| id    | int(3) | NO   | PRI | NULL    | auto_increment || color | int(3) | YES  |     | NULL    |     || brand | int(3) | YES  |     | NULL    |     || model | int(3) | YES  |     | NULL    |     |+-------+--------+------+-----+---------+----------------+4 rows in set (0.00 sec)mysql> insert into cars (color, brand, model) values (1,2,1), (3,1,2), (5,3,1),     -> (4,4,2), (2,2,3), (3,5,4), (4,1,3), (2,2,1), (5,2,3), (4,5,1);Query OK, 10 rows affected (0.00 sec)Records: 10  Duplicates: 0  Warnings: 0mysql> select * from cars;+----+-------+-------+-------+| id | color | brand | model |+----+-------+-------+-------+|  1 |     1 |     2 |     1 ||  2 |     3 |     1 |     2 ||  3 |     5 |     3 |     1 ||  4 |     4 |     4 |     2 ||  5 |     2 |     2 |     3 ||  6 |     3 |     5 |     4 ||  7 |     4 |     1 |     3 ||  8 |     2 |     2 |     1 ||  9 |     5 |     2 |     3 || 10 |     4 |     5 |     1 |+----+-------+-------+-------+10 rows in set (0.00 sec)

这将为我们提供足够的数据（我希望），以掩盖下面不同类型的联接的示例，并提供足够的数据以使它们值得。

因此，老板想了解 这个 问题，老板想知道 他拥有的所有跑车的ID 。

这是一个简单的两张表联接。我们有一个表，用于标识模型以及具有可用库存的表。正如你所看到的，在数据

model

的列

cars

表涉及

models

的列

cars

，我们有表。现在，我们知道models表的ID为

1

for，

Sports

因此让我们编写联接。

select    ID,    modelfrom    cars        join models on model=ID

所以这个查询看起来不错吧？我们已经识别了两个表并包含我们需要的信息，并使用一个联接来正确识别要联接的列。

ERROR 1052 (23000): Column 'ID' in field list is ambiguous

哦，不！我们的第一个查询有错误！是的，它是一个李子。您会看到，查询确实有正确的列，但是两个表中都存在一些列，因此数据库对于实际的含义和位置感到困惑。有两种解决方案可以解决此问题。第一个很简单，我们可以用来

tableName.columnName

准确地告诉数据库我们的意思，就像这样：

select    cars.ID,    models.modelfrom    cars        join models on cars.model=models.ID+----+--------+| ID | model  |+----+--------+|  1 | Sports ||  3 | Sports ||  8 | Sports || 10 | Sports ||  2 | Sedan  ||  4 | Sedan  ||  5 | 4WD    ||  7 | 4WD    ||  9 | 4WD    ||  6 | Luxury |+----+--------+10 rows in set (0.00 sec)

另一个可能更常用，称为表别名。该示例中的表具有简单易用的简单名称，但是键入类似的名称

KPI_DAILY_SALES_BY_DEPARTMENT

可能很快就会变老，因此一种简单的方法是对表进行昵称，如下所示：

select    a.ID,    b.modelfrom    cars a        join models b on a.model=b.ID

现在，返回到请求。如您所见，我们拥有所需的信息，但我们也有未要求提供的信息，因此我们需要在语句中包含where子句，以便仅按要求获取跑车。由于我更喜欢​​表别名方法，而不是一遍又一遍地使用表名，因此从现在开始，我将坚持使用它。

显然，我们需要在查询中添加where子句。我们可以通过

ID=1

或识别跑车

model='Sports'

。由于ID已被索引并且主键（而且恰好键入的次数较少），因此请在查询中使用它。

select    a.ID,    b.modelfrom    cars a        join models b on a.model=b.IDwhere    b.ID=1+----+--------+| ID | model  |+----+--------+|  1 | Sports ||  3 | Sports ||  8 | Sports || 10 | Sports |+----+--------+4 rows in set (0.00 sec)

答对了！老板很高兴。当然，作为老板，对自己的要求从不满意，他会查看信息，然后说 我也要颜色 。

好的，因此我们已经编写了很大一部分查询，但是我们需要使用第三个表颜色。现在，我们的主要信息表

cars

存储了汽车颜色ID，该链接返回到颜色ID列。因此，以与原始表类似的方式，我们可以连接第三个表：

select    a.ID,    b.modelfrom    cars a        join models b on a.model=b.ID        join colors c on a.color=c.IDwhere    b.ID=1+----+--------+| ID | model  |+----+--------+|  1 | Sports ||  3 | Sports ||  8 | Sports || 10 | Sports |+----+--------+4 rows in set (0.00 sec)

该死，尽管表已正确连接并且相关列已链接，但我们忘记从刚链接的新表中提取实际 信息 。

select    a.ID,    b.model,    c.colorfrom    cars a        join models b on a.model=b.ID        join colors c on a.color=c.IDwhere    b.ID=1+----+--------+-------+| ID | model  | color |+----+--------+-------+|  1 | Sports | Red   ||  8 | Sports | Green || 10 | Sports | White ||  3 | Sports | Black |+----+--------+-------+4 rows in set (0.00 sec)

是的，那是我们的老板暂时离开了。现在，更详细地解释其中的一些。如您所见，

from

语句中的子句链接了我们的主表（我经常使用一个包含信息的表，而不是查找表或维度表。该查询在所有被切换的表中也能正常工作，但是当我们会在几个月后回到此查询中进行阅读，因此通常最好尝试编写一个简单易懂的查询-
直观地进行排列，使用漂亮的缩进以使所有内容都清晰易懂如果您继续教别人，请尝试在他们的查询中灌输这些特征-尤其是要对它们进行故障排除时。

完全有可能以此方式链接越来越多的表。

select    a.ID,    b.model,    c.colorfrom    cars a        join models b on a.model=b.ID        join colors c on a.color=c.ID        join brands d on a.brand=d.IDwhere    b.ID=1

虽然我忘记在表中包含一个我们可能希望在其中联接多个列的

join

表，但这里有一个示例。如果该

models

表具有特定于品牌的模型，因此也有一个称为的列

brand

，该列链接回

brands

该

ID

字段中的表，则可以这样进行：

select    a.ID,    b.model,    c.colorfrom    cars a        join models b on a.model=b.ID        join colors c on a.color=c.ID        join brands d on a.brand=d.ID and b.brand=d.IDwhere    b.ID=1

您可以看到，上面的查询不仅将联接表链接到主

cars

表，而且还指定了已联接表之间的联接。如果不这样做，结果称为笛卡尔联接-
dba不好。笛卡尔联接是返回行的联接，因为该信息不会告诉数据库如何限制结果，因此查询将返回 所有 符合条件的行。

因此，举一个笛卡尔联接的例子，让我们运行以下查询：

select    a.ID,    b.modelfrom    cars a        join models b+----+--------+| ID | model  |+----+--------+|  1 | Sports ||  1 | Sedan  ||  1 | 4WD    ||  1 | Luxury ||  2 | Sports ||  2 | Sedan  ||  2 | 4WD    ||  2 | Luxury ||  3 | Sports ||  3 | Sedan  ||  3 | 4WD    ||  3 | Luxury ||  4 | Sports ||  4 | Sedan  ||  4 | 4WD    ||  4 | Luxury ||  5 | Sports ||  5 | Sedan  ||  5 | 4WD    ||  5 | Luxury ||  6 | Sports ||  6 | Sedan  ||  6 | 4WD    ||  6 | Luxury ||  7 | Sports ||  7 | Sedan  ||  7 | 4WD    ||  7 | Luxury ||  8 | Sports ||  8 | Sedan  ||  8 | 4WD    ||  8 | Luxury ||  9 | Sports ||  9 | Sedan  ||  9 | 4WD    ||  9 | Luxury || 10 | Sports || 10 | Sedan  || 10 | 4WD    || 10 | Luxury |+----+--------+40 rows in set (0.00 sec)

天哪，这很丑。但是，就数据库而言， 正是 所要求的。在查询中，我们要求

ID

from

cars

和

model

from

models

。但是，因为我们没有指定 如何 联接表，数据库匹配了 每一个 从第一表行与 每一 从第二表行。

好的，老板回来了，他希望再次提供更多信息。 我想要相同的列表，但还要包含4WD 。

但是，这为我们提供了一个很好的借口来研究实现此目的的两种不同方法。我们可以向where子句添加另一个条件，如下所示：

select    a.ID,    b.model,    c.colorfrom    cars a        join models b on a.model=b.ID        join colors c on a.color=c.ID        join brands d on a.brand=d.IDwhere    b.ID=1    or b.ID=3

尽管上面的方法可以很好地工作，但是让我们以不同的方式来看待它，这是一个很好的借口来说明

union

查询将如何工作。

我们知道以下将返回所有跑车：

select    a.ID,    b.model,    c.colorfrom    cars a        join models b on a.model=b.ID        join colors c on a.color=c.ID        join brands d on a.brand=d.IDwhere    b.ID=1

以下将返回所有的四轮驱动车：

select    a.ID,    b.model,    c.colorfrom    cars a        join models b on a.model=b.ID        join colors c on a.color=c.ID        join brands d on a.brand=d.IDwhere    b.ID=3

因此，通过

union all

在它们之间添加子句，第二个查询的结果将附加到第一个查询的结果。

select    a.ID,    b.model,    c.colorfrom    cars a        join models b on a.model=b.ID        join colors c on a.color=c.ID        join brands d on a.brand=d.IDwhere    b.ID=1union allselect    a.ID,    b.model,    c.colorfrom    cars a        join models b on a.model=b.ID        join colors c on a.color=c.ID        join brands d on a.brand=d.IDwhere    b.ID=3+----+--------+-------+| ID | model  | color |+----+--------+-------+|  1 | Sports | Red   ||  8 | Sports | Green || 10 | Sports | White ||  3 | Sports | Black ||  5 | 4WD    | Green ||  7 | 4WD    | White ||  9 | 4WD    | Black |+----+--------+-------+7 rows in set (0.00 sec)

如您所见，首先返回第一个查询的结果，然后返回第二个查询的结果。

在此示例中，简单地使用第一个查询当然会容易得多，但是

union

查询在特定情况下可能会很好。它们是从不容易连接在一起的表中返回表中特定结果的好方法-或
完全 无关的表。但是，有一些规则要遵循。

• 来自第一个查询的列类型必须与下面的每个其他查询的列类型匹配。
• 第一个查询中的列名称将用于标识整个结果集。
• 每个查询中的列数必须相同。

现在，您可能想知道使用

union

和之间有什么区别

union all

。一个

union

查询将删除重复，而

unionall

不会。这确实意味着使用

union

过度时性能会受到较小的影响，

union all

但结果可能是值得的-尽管我不会在这种情况下进行推测。

关于此注释，在这里可能需要注意一些其他注释。

• 如果要订购结果，可以使用an，

  order by

  但不能再使用别名。在上面的查询中，附加an

  order by a.ID

  将导致错误-就结果而言，该列将被调用，

  ID

  而不是

  a.ID

  -即使两个查询都使用了相同的别名。
• 我们只能有一个

  order by

  声明，并且必须作为最后一个声明。

对于下一个示例，我将向表中添加一些额外的行。

我已添加

Holden

到品牌表。我还添加了一行，到

cars

具有

color

的价值

12

-它在颜色表中没有提及。

好吧，老板又回来了，咆哮着请求-*我想统计一下我们经营的每个品牌以及其中的汽车数量！ 。

Rightyo，所以我们要做的第一件事就是完整列出所有可能的品牌。

select    a.brandfrom    brands a+--------+| brand  |+--------+| Ford   || Toyota || Nissan || Smart  || BMW    || Holden |+--------+6 rows in set (0.00 sec)

现在，当我们将其连接到汽车表时，将得到以下结果：

select    a.brandfrom    brands a        join cars b on a.ID=b.brandgroup by    a.brand+--------+| brand  |+--------+| BMW    || Ford   || Nissan || Smart  || Toyota |+--------+5 rows in set (0.00 sec)

这当然是个问题-我们没有提到

Holden

我添加的可爱品牌。

This is because a join looks for matching rows in both tables. As there is
no data in cars that is of type

Holden

it isn’t returned. This is where we
can use an

outer

join. This will return all the results from one table
whether they are matched in the other table or not:

select    a.brandfrom    brands a        left outer join cars b on a.ID=b.brandgroup by    a.brand+--------+| brand  |+--------+| BMW    || Ford   || Holden || Nissan || Smart  || Toyota |+--------+6 rows in set (0.00 sec)

Now that we have that, we can add a lovely aggregate function to get a count
and get the boss off our backs for a moment.

select    a.brand,    count(b.id) as countOfBrandfrom    brands a        left outer join cars b on a.ID=b.brandgroup by    a.brand+--------+--------------+| brand  | countOfBrand |+--------+--------------+| BMW    | 2 || Ford   | 2 || Holden | 0 || Nissan | 1 || Smart  | 1 || Toyota | 5 |+--------+--------------+6 rows in set (0.00 sec)

And with that, away the boss skulks.

Now, to explain this in some more detail, outer joins can be of the

left

or

right

type. The Left or Right defines which table is fully included. A

left outer join

will include all the rows from the table on the left, while
(you guessed it) a

right outer join

brings all the results from the table on
the right into the results.

Some databases will allow a

full outer join

which will bring back results
(whether matched or not) from both tables, but this isn’t supported in all
databases.

Now, I probably figure at this point in time, you are wondering whether or not
you can merge join types in a query - and the answer is yes, you absolutely
can.

select    b.brand,    c.color,    count(a.id) as countOfBrandfrom    cars a        right outer join brands b on b.ID=a.brand        join colors c on a.color=c.IDgroup by    a.brand,    c.color+--------+-------+--------------+| brand  | color | countOfBrand |+--------+-------+--------------+| Ford   | Blue  | 1 || Ford   | White | 1 || Toyota | Black | 1 || Toyota | Green | 2 || Toyota | Red   | 1 || Nissan | Black | 1 || Smart  | White | 1 || BMW    | Blue  | 1 || BMW    | White | 1 |+--------+-------+--------------+9 rows in set (0.00 sec)

So, why is that not the results that were expected? It is because although we
have selected the outer join from cars to brands, it wasn’t specified in the
join to colors - so that particular join will only bring back results that
match in both tables.

Here is the query that would work to get the results that we expected:

select    a.brand,    c.color,    count(b.id) as countOfBrandfrom    brands a        left outer join cars b on a.ID=b.brand        left outer join colors c on b.color=c.IDgroup by    a.brand,    c.color+--------+-------+--------------+| brand  | color | countOfBrand |+--------+-------+--------------+| BMW    | Blue  | 1 || BMW    | White | 1 || Ford   | Blue  | 1 || Ford   | White | 1 || Holden | NULL  | 0 || Nissan | Black | 1 || Smart  | White | 1 || Toyota | NULL  | 1 || Toyota | Black | 1 || Toyota | Green | 2 || Toyota | Red   | 1 |+--------+-------+--------------+11 rows in set (0.00 sec)

As we can see, we have two outer joins in the query and the results are coming
through as expected.

Now, how about those other types of joins you ask? What about Intersections?

Well, not all databases support the

intersection

but pretty much all
databases will allow you to create an intersection through a join (or a well
structured where statement at the least).

An Intersection is a type of join somewhat similar to a

union

as described
above - but the difference is that it only returns rows of data that are
identical (and I do mean identical) between the various individual queries
joined by the union. only rows that are identical in every regard will be
returned.

A simple example would be as such:

select    *from    colorswhere    ID>2intersectselect    *from    colorswhere    id<4

While a normal

union

query would return all the rows of the table (the first
query returning anything over

ID>2

and the second anything having

ID<4

)
which would result in a full set, an intersect query would only return the row
matching

id=3

as it meets both criteria.

Now, if your database doesn’t support an

intersect

query, the above can be
easily accomlished with the following query:

select    a.ID,    a.color,    a.paintfrom    colors a        join colors b on a.ID=b.IDwhere    a.ID>2    and b.ID<4+----+-------+----------+| ID | color | paint    |+----+-------+----------+|  3 | Blue  | metallic |+----+-------+----------+1 row in set (0.00 sec)

If you wish to perform an intersection across two different tables using a
database that doesn’t inherently support an intersection query, you will need
to create a join on every column of the tables.