SQL查询从多个表返回数据

有多种方法可以从数据库的多个表中检索数据。在此答案中，我将使用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: 0

mysql> 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: 0

mysql> 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: 0

mysql> 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: 0

mysql> 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为1for，Sports因此让我们编写联接。

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

ERROR 1052 (23000): Column 'ID' in field list is ambiguous 哦，不！我们的第一个查询有错误！是的，它是一个李子。您会看到，查询确实有正确的列，但是两个表中都存在一些列，因此数据库对于实际的含义和位置感到困惑。有两种解决方案可以解决此问题。第一个很简单，我们可以用来tableName.columnName准确地告诉数据库我们的意思，就像这样：

select cars.ID, models.model from 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.model from cars a join models b on a.model=b.ID 现在，返回到请求。如您所见，我们拥有所需的信息，但我们也有未要求提供的信息，因此我们需要在语句中包含where子句，以便仅按要求获取跑车。由于我更喜欢​​表别名方法，而不是一遍又一遍地使用表名，因此从现在开始，我将坚持使用它。

显然，我们需要在查询中添加where子句。我们可以通过ID=1或识别跑车model='Sports'。由于ID已被索引并且主键（而且恰好键入的次数较少），因此请在查询中使用它。

select a.ID, b.model from cars a join models b on a.model=b.ID where 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.model from cars a join models b on a.model=b.ID join colors c on a.color=c.ID where 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.color from cars a join models b on a.model=b.ID join colors c on a.color=c.ID where 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.color from 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 where b.ID=1 虽然我忘记在表中包含一个我们可能希望在其中联接多个列的join表，但这里有一个示例。如果该models表具有特定于品牌的模型，因此也有一个称为的列brand，该列链接回brands该ID字段中的表，则可以这样进行：

select a.ID, b.model, c.color from 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.ID where b.ID=1 您可以看到，上面的查询不仅将联接表链接到主cars表，而且还指定了已联接表之间的联接。如果不这样做，结果称为笛卡尔联接-dba不好。笛卡尔联接是返回行的联接，因为该信息不会告诉数据库如何限制结果，因此查询将返回所有符合条件的行。

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

select a.ID, b.model from 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) 天哪，这很丑。但是，就数据库而言，正是所要求的。在查询中，我们要求IDfrom cars和modelfrom models。但是，因为我们没有指定如何联接表，数据库匹配了每一个从第一表行与每一从第二表行。

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

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

select a.ID, b.model, c.color from 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 where b.ID=1 or b.ID=3 尽管上面的方法可以很好地工作，但是让我们以不同的方式来看待它，这是一个很好的借口来说明union查询将如何工作。

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

select a.ID, b.model, c.color from 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 where b.ID=1 以下将返回所有的四轮驱动车：

select a.ID, b.model, c.color from 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 where b.ID=3 因此，通过union all在它们之间添加子句，第二个查询的结果将附加到第一个查询的结果。

select a.ID, b.model, c.color from 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 where b.ID=1 union all select a.ID, b.model, c.color from 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 where 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查询将删除重复，而union all不会。这确实意味着使用union过度时性能会受到较小的影响，union all但结果可能是值得的-尽管我不会在这种情况下进行推测。

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

如果要订购结果，可以使用an，order by但不能再使用别名。在上面的查询中，附加an order by a.ID将导致错误-就结果而言，该列将被调用，ID而不是a.ID-即使两个查询都使用了相同的别名。 我们只能有一个order by声明，并且必须作为最后一个声明。 对于下一个示例，我将向表中添加一些额外的行。

我已添加Holden到品牌表。我还添加了一行，到cars具有color的价值12-它在颜色表中没有提及。

好的，老板又回来了，咆哮着请求-*我想统计一下我们经营的每个品牌以及其中的汽车数量！`-典型的，我们只是进入了一个有趣的讨论部分，老板希望做更多的工作。

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

select a.brand from brands a

+--------+ | brand | +--------+ | Ford | | Toyota | | Nissan | | Smart | | BMW | | Holden | +--------+ 6 rows in set (0.00 sec) 现在，当我们将其连接到汽车表时，将得到以下结果：

select a.brand from brands a join cars b on a.ID=b.brand group by a.brand

+--------+ | brand | +--------+ | BMW | | Ford | | Nissan | | Smart | | Toyota | +--------+ 5 rows in set (0.00 sec) 这当然是个问题-我们没有提到Holden我添加的可爱品牌。

这是因为联接在两个表中都查找匹配的行。由于汽车中没有任何类型的数据，Holden因此不会返回。这是我们可以使用outer联接的地方。这将返回一个表中的所有结果，无论它们是否与另一表中的结果匹配：

select a.brand from brands a left outer join cars b on a.ID=b.brand group by a.brand

+--------+ | brand | +--------+ | BMW | | Ford | | Holden | | Nissan | | Smart | | Toyota | +--------+ 6 rows in set (0.00 sec) 现在，有了这个功能，我们可以添加一个可爱的聚合函数来获得计数，并让老板暂时离开。

select a.brand, count(b.id) as countOfBrand from brands a left outer join cars b on a.ID=b.brand group by a.brand

+--------+--------------+ | brand | countOfBrand | +--------+--------------+ | BMW | 2 | | Ford | 2 | | Holden | 0 | | Nissan | 1 | | Smart | 1 | | Toyota | 5 | +--------+--------------+ 6 rows in set (0.00 sec) 这样一来，老板就走了。

现在，为了更详细地说明这一点，外部联接可以是left或right类型。左或右定义完全包含哪个表。A left outer join将包括左侧表中的所有行，而（您猜对了）a right outer join将右侧表中的所有结果带入结果中。

某些数据库允许使用a full outer join，这将从两个表中带回结果（无论是否匹配），但是并非所有数据库都支持。

现在，我可能想知道此时，您想知道是否可以在查询中合并联接类型-答案是肯定的，您绝对可以。

select b.brand, c.color, count(a.id) as countOfBrand from cars a right outer join brands b on b.ID=a.brand join colors c on a.color=c.ID group 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) 那么，为什么不是预期的结果呢？这是因为尽管我们选择了从汽车到品牌的外部联接，但未在颜色联接中指定-因此特定联接只会带回在两个表中都匹配的结果。

这是可以获取我们期望的结果的查询：

select a.brand, c.color, count(b.id) as countOfBrand from brands a left outer join cars b on a.ID=b.brand left outer join colors c on b.color=c.ID group 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) 如我们所见，查询中有两个外部联接，结果按预期进行。

现在，您问这些其他类型的联接如何？那路口呢？

好吧，并非所有数据库都支持，intersection但是几乎所有数据库都将允许您通过联接（或至少结构良好的where语句）创建交集。

Intersection是一种连接类型，与union上述的连接有点类似-但区别在于，它仅返回由联合连接的各个单个查询之间相同（并且我的意思是相同）的数据行。仅返回在各方面相同的行。

一个简单的例子是这样的：

select * from colors where ID>2 intersect select * from colors where id<4 虽然普通union查询将返回表的所有行（第一个查询返回，ID>2而第二个返回具有ID<4）将形成一个完整的集合，但相交查询将仅返回id=3符合两个条件的行。

现在，如果您的数据库不支持intersect查询，则可以通过以下查询轻松完成以上操作：

select a.ID, a.color, a.paint from colors a join colors b on a.ID=b.ID where a.ID>2 and b.ID<4

+----+-------+----------+ | ID | color | paint | +----+-------+----------+ | 3 | Blue | Metallic | +----+-------+----------+ 1 row in set (0.00 sec) 如果希望使用本身不支持交集查询的数据库在两个不同的表之间执行交集，则需要在表的每一列上创建一个联接。