MySQL SQL查询从多个表返回数据

SQL query return data from multiple tables

提问by Fluffeh

I would like to know the following:

我想知道以下内容：

• how to get data from multiple tables in my database?
• what types of methods are there to do this?
• what are joins and unions and how are they different from one another?
• When should I use each one compared to the others?

• 如何从我的数据库中的多个表中获取数据？
• 有哪些类型的方法可以做到这一点？
• 什么是联接和联合？它们之间有何不同？
• 与其他的相比，我应该在什么时候使用每一个？

I am planning to use this in my (for example - PHP) application, but don't want to run multiple queries against the database, what options do I have to get data from multiple tables in a single query?

我打算在我的（例如 - PHP）应用程序中使用它，但不想对数据库运行多个查询，我有哪些选项可以在单个查询中从多个表中获取数据？

Note: I am writing this as I would like to be able to link to a well written guide on the numerous questions that I constantly come across in the PHP queue, so I can link to this for further detail when I post an answer.

注意：我写这篇文章是因为我希望能够链接到关于我在 PHP 队列中经常遇到的众多问题的写得很好的指南，所以当我发布答案时，我可以链接到这个以获取更多详细信息。

The answers cover off the following:

答案涵盖以下内容：

1. Part 1 - Joins and Unions
2. Part 2 - Subqueries
3. Part 3 - Tricks and Efficient Code
4. Part 4 - Subqueries in the From Clause
5. Part 5 - Mixed Bag of John's Tricks

1. 第 1 部分 - 联接和联合
2. 第 2 部分 - 子查询
3. 第 3 部分 - 技巧和高效代码
4. 第 4 部分 - From 子句中的子查询
5. 第 5 部分 - 约翰诡计的混合包

回答by Fluffeh

Part 1 - Joins and Unions

第 1 部分 - 联接和联合

This answer covers:

这个答案涵盖：

1. Part 1
   • Joining two or more tables using an inner join (See the wikipedia entryfor additional info)
   • How to use a union query
   • Left and Right Outer Joins (this stackOverflow answeris excellent to describe types of joins)
   • Intersect queries (and how to reproduce them if your database doesn't support them) - this is a function of SQL-Server (see info) and part of the reason I wrote this whole thingin the first place.
2. Part 2
   • Subqueries - what they are, where they can be used and what to watch out for
   • Cartesian joins AKA - Oh, the misery!

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

There are a number of ways to retrieve data from multiple tables in a database. In this answer, I will be using ANSI-92 join syntax. This may be different to a number of other tutorials out there which use the older ANSI-89 syntax (and if you are used to 89, may seem much less intuitive - but all I can say is to try it) as it is mucheasier to understand when the queries start getting more complex. Why use it? Is there a performance gain? The short answeris no, but it iseasier to read once you get used to it. It is easier to read queries written by other folks using this syntax.

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

I am also going to use the concept of a small caryard which has a database to keep track of what cars it has available. The owner has hired you as his IT Computer guy and expects you to be able to drop him the data that he asks for at the drop of a hat.

我还将使用一个小型车场的概念，它有一个数据库来跟踪它有哪些可用的汽车。所有者已聘请您作为他的 IT 计算机人员，并希望您能够毫不犹豫地将他要求的数据交给他。

I have made a number of lookup tables that will be used by the final table. This will give us a reasonable model to work from. To start off, I will be running my queries against an example database that has the following structure. I will try to think of common mistakes that are made when starting out and explain what goes wrong with them - as well as of course showing how to correct them.

我已经制作了一些最终表将使用的查找表。这将为我们提供一个合理的模型。首先，我将针对具有以下结构的示例数据库运行查询。我会试着想想在开始时犯的常见错误，并解释它们出了什么问题——当然也会展示如何纠正它们。

The first table is simply a color listing so that we know what colors we have in the car yard.

第一个表格只是一个颜色列表，以便我们知道我们在车场里有什么颜色。

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)

The brands table identifies the different brands of the cars out caryard could possibly sell.

品牌表标识了caryard 可能销售的不同品牌汽车。

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)

The model table will cover off different types of cars, it is going to be simpler for this to use different car types rather than actual car models.

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

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)

And finally, to tie up all these other tables, the table that ties everything together. The ID field is actually the unique lot number used to identify cars.

最后，将所有这些其他表捆绑在一起，将所有东西捆绑在一起的表。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)

This will give us enough data (I hope) to cover off the examples below of different types of joins and also give enough data to make them worthwhile.

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

So getting into the grit of it, the boss wants to know The IDs of all the sports cars he has.

因此，为了深入了解，老板想知道他拥有的所有跑车的 ID。

This is a simple two table join. We have a table that identifies the model and the table with the available stock in it. As you can see, the data in the modelcolumn of the carstable relates to the modelscolumn of the carstable we have. Now, we know that the models table has an ID of 1for Sportsso lets write the join.

这是一个简单的两表连接。我们有一个表来标识模型和包含可用库存的表。正如你所看到的，在数据model的列cars表涉及models的列cars，我们有表。现在，我们知道模型表的 ID 为1forSports所以让我们编写连接。

select
    ID,
    model
from
    cars
        join models
            on model=ID

So this query looks good right? We have identified the two tables and contain the information we need and use a join that correctly identifies what columns to join on.

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

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

Oh noes! An error in our first query! Yes, and it is a plum. You see, the query has indeed got the right columns, but some of them exist in both tables, so the database gets confused about what actual column we mean and where. There are two solutions to solve this. The first is nice and simple, we can use tableName.columnNameto tell the database exactly what we mean, like this:

哦不！我们的第一个查询出错了！是的，它是一个李子。你看，查询确实得到了正确的列，但其中一些列存在于两个表中，因此数据库对我们的实际列和位置感到困惑。有两种解决方案可以解决这个问题。第一个很好很简单，我们可以用它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)

The other is probably more often used and is called table aliasing. The tables in this example have nice and short simple names, but typing out something like KPI_DAILY_SALES_BY_DEPARTMENTwould probably get old quickly, so a simple way is to nickname the table like this:

另一种可能更常用，称为表别名。这个例子中的表格有漂亮而简短的名字，但输入类似的东西KPI_DAILY_SALES_BY_DEPARTMENT可能会很快变老，所以一个简单的方法是给表格起这样的昵称：

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

Now, back to the request. As you can see we have the information we need, but we also have information that wasn't asked for, so we need to include a where clause in the statement to only get the Sports cars as was asked. As I prefer the table alias method rather than using the table names over and over, I will stick to it from this point onwards.

现在，回到请求。正如你所看到的，我们有我们需要的信息，但我们也有没有被要求的信息，所以我们需要在语句中包含一个 where 子句，以只获取被要求的跑车。因为我更喜欢表别名方法而不是一遍又一遍地使用表名，所以从现在开始我将坚持使用它。

Clearly, we need to add a where clause to our query. We can identify Sports cars either by ID=1or model='Sports'. As the ID is indexed and the primary key (and it happens to be less typing), lets use that in our query.

显然，我们需要在查询中添加一个 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)

Bingo! The boss is happy. Of course, being a boss and never being happy with what he asked for, he looks at the information, then says I want the colors as well.

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

Okay, so we have a good part of our query already written, but we need to use a third table which is colors. Now, our main information table carsstores the car color ID and this links back to the colors ID column. So, in a similar manner to the original, we can join a third table:

好的，所以我们已经编写了查询的很大一部分，但是我们需要使用第三个表，即颜色。现在，我们的主信息表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)

Damn, although the table was correctly joined and the related columns were linked, we forgot to pull in the actual informationfrom the new table that we just linked.

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

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)

Right, that's the boss off our back for a moment. Now, to explain some of this in a little more detail. As you can see, the fromclause in our statement links our main table (I often use a table that contains information rather than a lookup or dimension table. The query would work just as well with the tables all switched around, but make less sense when we come back to this query to read it in a few months time, so it is often best to try to write a query that will be nice and easy to understand - lay it out intuitively, use nice indenting so that everything is as clear as it can be. If you go on to teach others, try to instill these characteristics in their queries - especially if you will be troubleshooting them.

是的，那是我们暂时离开的老板。现在，更详细地解释其中的一些内容。如您所见，from我们语句中的子句链接了我们的主表（我经常使用包含信息的表，而不是查找表或维度表。查询在所有表都切换时也能正常工作，但在以下情况下意义不大我们会在几个月后回到这个查询来阅读它，因此通常最好尝试编写一个很好且易于理解的查询 - 直观地布置它，使用漂亮的缩进，以便一切都像如果你继续教别人，试着在他们的查询中灌输这些特征 - 特别是如果你要解决他们的问题。

It is entirely possible to keep linking more and more tables in this manner.

以这种方式保持链接越来越多的表是完全可能的。

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

While I forgot to include a table where we might want to join more than one column in the joinstatement, here is an example. If the modelstable had brand-specific models and therefore also had a column called brandwhich linked back to the brandstable on the IDfield, it could be done as this:

虽然我忘记包含一个表，我们可能希望在该表中加入多于一列的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

You can see, the query above not only links the joined tables to the main carstable, but also specifies joins between the already joined tables. If this wasn't done, the result is called a cartesian join - which is dba speak for bad. A cartesian join is one where rows are returned because the information doesn't tell the database how to limit the results, so the query returns allthe rows that fit the criteria.

可以看到，上面的查询不仅将连接表链接到主cars表，还指定了已连接表之间的连接。如果没有这样做，结果被称为笛卡尔连接——这是 dba 的坏话。笛卡尔连接是一种返回行的连接，因为信息不会告诉数据库如何限制结果，因此查询返回所有符合条件的行。

So, to give an example of a cartesian join, lets run the following query:

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

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)

Good god, that's ugly. However, as far as the database is concerned, it is exactlywhat was asked for. In the query, we asked for for the IDfrom carsand the modelfrom models. However, because we didn't specify howto join the tables, the database has matched everyrow from the first table with everyrow from the second table.

天哪，太丑了 但是，就数据库而言，这正是所要求的。在查询中，我们要求提供IDfromcars和modelfrom models。但是，因为我们没有指定如何联接表，数据库匹配了每一个从第一表行与每一从第二表行。

Okay, so the boss is back, and he wants more information again. I want the same list, but also include 4WDs in it.

好的，所以老板回来了，他又想了解更多信息。我想要相同的列表，但也要在其中包含 4WD。

This however, gives us a great excuse to look at two different ways to accomplish this. We could add another condition to the where clause like this:

然而，这为我们提供了一个很好的借口来研究两种不同的方法来实现这一目标。我们可以在 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

While the above will work perfectly well, lets look at it differently, this is a great excuse to show how a unionquery will work.

虽然上面的方法工作得很好，但让我们换个角度看，这是展示union查询如何工作的一个很好的借口。

We know that the following will return all the Sports cars:

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

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

And the following would return all the 4WDs:

以下将返回所有 4WD：

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

So by adding a union allclause between them, the results of the second query will be appended to the results of the first query.

因此，通过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)

As you can see, the results of the first query are returned first, followed by the results of the second query.

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

In this example, it would of course have been much easier to simply use the first query, but unionqueries can be great for specific cases. They are a great way to return specific results from tables from tables that aren't easily joined together - or for that matter completelyunrelated tables. There are a few rules to follow however.

在这个例子中，简单地使用第一个查询当然会容易得多，但union查询对于特定情况可能非常有用。它们是从不容易连接在一起的表中的表返回特定结果的好方法 - 或者就此而言完全不相关的表。然而，有一些规则需要遵循。

• The column types from the first query must match the column types from every other query below.
• The names of the columns from the first query will be used to identify the entire set of results.
• The number of columns in each query must be the same.

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

Now, you might be wondering what thedifference is between using unionand union all. A unionquery will remove duplicates, while a union allwill not. This does mean that there is a small performance hit when using unionover union allbut the results may be worth it - I won't speculate on that sort of thing in this though.

现在，您可能想知道使用union和之间有什么区别union all。一个union查询将删除重复，而union all不会。这确实意味着使用unionover时性能会受到很小的影响，union all但结果可能是值得的 - 不过我不会在这种情况下推测这种事情。

On this note, it might be worth noting some additional notes here.

在此说明上，此处可能需要注意一些其他说明。

• If we wanted to order the results, we can use an order bybut you can't use the alias anymore. In the query above, appending an order by a.IDwould result in an error - as far as the results are concerned, the column is called IDrather than a.ID- even though the same alias has been used in both queries.
• We can only have one order bystatement, and it must be as the last statement.

• 如果我们想对结果进行排序，我们可以使用 anorder by但您不能再使用别名。在上面的查询中，附加 anorder by a.ID将导致错误 - 就结果而言，该列被调用ID而不是a.ID- 即使在两个查询中使用了相同的别名。
• 我们只能有一个order by语句，而且必须是最后一个语句。

For the next examples, I am adding a few extra rows to our tables.

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

I have added Holdento the brands table. I have also added a row into carsthat has the colorvalue of 12- which has no reference in the colors table.

我已添加Holden到品牌表中。我还在其中添加了一行cars，其color值为12- 在颜色表中没有引用。

Okay, the boss is back again, barking requests out - *I want a count of each brand we carry and the number of cars in it!` - Typical, we just get to an interesting section of our discussion and the boss wants more work.

好的，老板又回来了，大声要求 - *我想计算我们携带的每个品牌以及其中的汽车数量！` - 典型的，我们只是讨论了一个有趣的部分，老板想要更多的工作.

Rightyo, so the first thing we need to do is get a complete listing of possible brands.

Rightyo，所以我们需要做的第一件事就是获得可能品牌的完整列表。

select
    a.brand
from
    brands a

+--------+
| brand  |
+--------+
| Ford   |
| Toyota |
| Nissan |
| Smart  |
| BMW    |
| Holden |
+--------+
6 rows in set (0.00 sec)

Now, when we join this to our cars table we get the following result:

现在，当我们将其加入到我们的汽车表中时，我们得到以下结果：

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)

Which is of course a problem - we aren't seeing any mention of the lovely Holdenbrand I added.

这当然是一个问题——我们没有看到任何提到Holden我添加的可爱品牌。

This is because a join looks for matching rows in bothtables. As there is no data in cars that is of type Holdenit isn't returned. This is where we can use an outerjoin. This will return allthe results from one table whether they are matched in the other table or not:

这是因为连接在两个表中查找匹配的行。由于汽车中没有类型的数据，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)

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

And with that, away the boss skulks.

有了这个，老板就偷偷溜走了。

Now, to explain this in some more detail, outer joins can be of the leftor righttype. The Left or Right defines which table is fullyincluded. A left outer joinwill include all the rows from the table on the left, while (you guessed it) a right outer joinbrings all the results from the table on the right into the results.

现在，为了更详细地解释这一点，外部联接可以是leftorright类型。Left 或 Right 定义完全包含哪个表。Aleft outer join将包含左侧表中的所有行，而（您猜对了）aright outer join将右侧表中的所有结果带入结果中。

Some databases will allow a full outer joinwhich will bring back results (whether matched or not) from bothtables, but this isn't supported in all databases.

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

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

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

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 intersectionbut pretty much all databases will allow you to create an intersection through a join (or a well structured where statement at the least).

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

An Intersection is a type of join somewhat similar to a unionas described above - but the difference is that it onlyreturns 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.

Intersection 是一种类似于union上述的连接类型- 但不同之处在于它只返回由联合连接的各种单独查询之间相同（我的意思是相同）的数据行。只会返回在各方面都相同的行。

A simple example would be as such:

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

select
    *
from
    colors
where
    ID>2
intersect
select
    *
from
    colors
where
    id<4

While a normal unionquery would return all the rows of the table (the first query returning anything over ID>2and the second anything having ID<4) which would result in a full set, an intersect query would only return the row matching id=3as it meets both criteria.

虽然正常union查询将返回表的所有行（第一个查询返回任何内容ID>2，第二个查询返回任何内容ID<4），这将导致一个完整的集合，但相交查询只会返回匹配id=3两个条件的行。

Now, if your database doesn't support an intersectquery, the above can be easily accomlished with the following query:

现在，如果您的数据库不支持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)

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 columnof the tables.

如果您希望使用本身不支持交集查询的数据库跨两个不同的表执行交集，则需要在表的每一列上创建一个连接。

回答by John Woo

Ok, I found this post very interesting and I would like to share some of my knowledge on creating a query. Thanks for this Fluffeh. Others who may read this and may feel that I'm wrong are 101% free to edit and criticise my answer. (Honestly, I feel very thankful for correcting my mistake(s).)

好的，我发现这篇文章很有趣，我想分享一些关于创建查询的知识。感谢这个Fluffeh。其他可能会阅读本文并认为我错了的人可以 101% 自由地编辑和批评我的答案。（老实说，我非常感谢纠正我的错误。）

I'll be posting some of the frequently asked questions in MySQLtag.

我将在MySQL标签中发布一些常见问题。

---

---

Trick No. 1 (rows that matches to multiple conditions)

技巧一（匹配多个条件的行）

Given this schema

鉴于此架构

CREATE TABLE MovieList
(
    ID INT,
    MovieName VARCHAR(25),
    CONSTRAINT ml_pk PRIMARY KEY (ID),
    CONSTRAINT ml_uq UNIQUE (MovieName)
);

INSERT INTO MovieList VALUES (1, 'American Pie');
INSERT INTO MovieList VALUES (2, 'The Notebook');
INSERT INTO MovieList VALUES (3, 'Discovery Channel: Africa');
INSERT INTO MovieList VALUES (4, 'Mr. Bean');
INSERT INTO MovieList VALUES (5, 'Expendables 2');

CREATE TABLE CategoryList
(
    MovieID INT,
    CategoryName VARCHAR(25),
    CONSTRAINT cl_uq UNIQUE(MovieID, CategoryName),
    CONSTRAINT cl_fk FOREIGN KEY (MovieID) REFERENCES MovieList(ID)
);

INSERT INTO CategoryList VALUES (1, 'Comedy');
INSERT INTO CategoryList VALUES (1, 'Romance');
INSERT INTO CategoryList VALUES (2, 'Romance');
INSERT INTO CategoryList VALUES (2, 'Drama');
INSERT INTO CategoryList VALUES (3, 'Documentary');
INSERT INTO CategoryList VALUES (4, 'Comedy');
INSERT INTO CategoryList VALUES (5, 'Comedy');
INSERT INTO CategoryList VALUES (5, 'Action');

QUESTION

题

Find all moviesthat belong to at least bothComedyand Romancecategories.

查找至少同时属于和类别的所有电影。ComedyRomance

Solution

解决方案

This question can be very tricky sometimes. It may seem that a query like this will be the answer:-

这个问题有时可能非常棘手。看起来像这样的查询将是答案：-

SELECT  DISTINCT a.MovieName
FROM    MovieList a
        INNER JOIN CategoryList b
            ON a.ID = b.MovieID
WHERE   b.CategoryName = 'Comedy' AND
        b.CategoryName = 'Romance'

SQLFiddle Demo

SQLFiddle 演示

which is definitely very wrong because it produces no result. The explanation of this is that there is only one valid value of CategoryNameon each row. For instance, the first condition returns true, the second condition is always false. Thus, by using ANDoperator, both condition should be true; otherwise, it will be false. Another query is like this,

这绝对是非常错误的，因为它不会产生任何结果。这样的解释是，只有一个有效的价值CategoryName在每行。例如，第一个条件返回true，第二个条件始终为 false 。因此，通过使用AND运算符，两个条件都应该为真；否则，它将是假的。另一个查询是这样的，

SELECT  DISTINCT a.MovieName
FROM    MovieList a
        INNER JOIN CategoryList b
            ON a.ID = b.MovieID
WHERE   b.CategoryName IN ('Comedy','Romance')

SQLFiddle Demo

SQLFiddle 演示

and the result is still incorrect because it matches to record that has at leastone match on the categoryName. The real solutionwould be by counting the number of record instances per movie. The number of instance should match to the total number of the values supplied in the condition.

其结果是仍然不正确，因为它匹配录制带有至少在一个比赛categoryName。在真正的解决方案是通过计算每部电影的记录实例的数量。实例数应与条件中提供的值总数相匹配。

SELECT  a.MovieName
FROM    MovieList a
        INNER JOIN CategoryList b
            ON a.ID = b.MovieID
WHERE   b.CategoryName IN ('Comedy','Romance')
GROUP BY a.MovieName
HAVING COUNT(*) = 2

SQLFiddle Demo (the answer)

SQLFiddle 演示（答案）

• SQL of Relational Division

• 关系除法SQL

---

---

Trick No. 2 (maximum record for each entry)

技巧二（每个条目的最大记录）

Given schema,

给定模式，

CREATE TABLE Software
(
    ID INT,
    SoftwareName VARCHAR(25),
    Descriptions VARCHAR(150),
    CONSTRAINT sw_pk PRIMARY KEY (ID),
    CONSTRAINT sw_uq UNIQUE (SoftwareName)  
);

INSERT INTO Software VALUES (1,'PaintMe','used for photo editing');
INSERT INTO Software VALUES (2,'World Map','contains map of different places of the world');
INSERT INTO Software VALUES (3,'Dictionary','contains description, synonym, antonym of the words');

CREATE TABLE VersionList
(
    SoftwareID INT,
    VersionNo INT,
    DateReleased DATE,
    CONSTRAINT sw_uq UNIQUE (SoftwareID, VersionNo),
    CONSTRAINT sw_fk FOREIGN KEY (SOftwareID) REFERENCES Software(ID)
);

INSERT INTO VersionList VALUES (3, 2, '2009-12-01');
INSERT INTO VersionList VALUES (3, 1, '2009-11-01');
INSERT INTO VersionList VALUES (3, 3, '2010-01-01');
INSERT INTO VersionList VALUES (2, 2, '2010-12-01');
INSERT INTO VersionList VALUES (2, 1, '2009-12-01');
INSERT INTO VersionList VALUES (1, 3, '2011-12-01');
INSERT INTO VersionList VALUES (1, 2, '2010-12-01');
INSERT INTO VersionList VALUES (1, 1, '2009-12-01');
INSERT INTO VersionList VALUES (1, 4, '2012-12-01');

QUESTION

题

Find the latest version on each software. Display the following columns: SoftwareName,Descriptions,LatestVersion(from VersionNo column),DateReleased

查找每个软件的最新版本。显示以下列：SoftwareName, Descriptions, LatestVersion( from VersionNo column),DateReleased

Solution

解决方案

Some SQL developers mistakenly use MAX()aggregate function. They tend to create like this,

一些 SQL 开发人员错误地使用了MAX()聚合函数。他们倾向于像这样创造，

SELECT  a.SoftwareName, a.Descriptions,
        MAX(b.VersionNo) AS LatestVersion, b.DateReleased
FROM    Software a
        INNER JOIN VersionList b
            ON a.ID = b.SoftwareID
GROUP BY a.ID
ORDER BY a.ID

SQLFiddle Demo

SQLFiddle 演示

(most RDBMS generates a syntax error on this because of not specifying some of the non-aggregated columns on the group byclause) the result produces the correct LatestVersionon each software but obviously the DateReleasedare incorrect. MySQLdoesn't support Window Functionsand Common Table Expressionyet as some RDBMS do already. The workaround on this problem is to create a subquerywhich gets the individual maximum versionNoon each software and later on be joined on the other tables.

（大多数 RDBMS 会在此生成语法错误，因为没有在group by子句中指定一些非聚合列）结果LatestVersion在每个软件上产生正确但显然DateReleased不正确。MySQL不支持Window Functions，Common Table Expression但正如一些 RDBMS 已经做的那样。解决此问题的方法是创建一个在每个软件上subquery获得单个最大值的方法versionNo，然后在其他表上加入。

SELECT  a.SoftwareName, a.Descriptions,
        b.LatestVersion, c.DateReleased
FROM    Software a
        INNER JOIN
        (
            SELECT  SoftwareID, MAX(VersionNO) LatestVersion
            FROM    VersionList
            GROUP BY SoftwareID
        ) b ON a.ID = b.SoftwareID
        INNER JOIN VersionList c
            ON  c.SoftwareID = b.SoftwareID AND
                c.VersionNO = b.LatestVersion
GROUP BY a.ID
ORDER BY a.ID

SQLFiddle Demo (the answer)

SQLFiddle 演示（答案）

---

---

So that was it. I'll be posting another soon as I recall any other FAQon MySQLtag. Thank you for reading this little article. I hope that you have atleast get even a little knowledge from this.

就是这样。当我想起任何其他有关标签的常见问题解答时，我会尽快发布另一个MySQL。感谢您阅读这篇小文章。我希望你至少从中得到一点点知识。

UPDATE 1

更新 1

---

---

Trick No. 3 (Finding the latest record between two IDs)

技巧三（查找两个 ID 之间的最新记录）

Given Schema

给定模式

CREATE TABLE userList
(
    ID INT,
    NAME VARCHAR(20),
    CONSTRAINT us_pk PRIMARY KEY (ID),
    CONSTRAINT us_uq UNIQUE (NAME)  
);

INSERT INTO userList VALUES (1, 'Fluffeh');
INSERT INTO userList VALUES (2, 'John Woo');
INSERT INTO userList VALUES (3, 'hims056');

CREATE TABLE CONVERSATION
(
    ID INT,
    FROM_ID INT,
    TO_ID INT,
    MESSAGE VARCHAR(250),
    DeliveryDate DATE
);

INSERT INTO CONVERSATION VALUES (1, 1, 2, 'hi john', '2012-01-01');
INSERT INTO CONVERSATION VALUES (2, 2, 1, 'hello fluff', '2012-01-02');
INSERT INTO CONVERSATION VALUES (3, 1, 3, 'hey hims', '2012-01-03');
INSERT INTO CONVERSATION VALUES (4, 1, 3, 'please reply', '2012-01-04');
INSERT INTO CONVERSATION VALUES (5, 3, 1, 'how are you?', '2012-01-05');
INSERT INTO CONVERSATION VALUES (6, 3, 2, 'sample message!', '2012-01-05');

QUESTION

题

Find the latest conversation between two users.

查找两个用户之间的最新对话。

Solution

解决方案

SELECT    b.Name SenderName,
          c.Name RecipientName,
          a.Message,
          a.DeliveryDate
FROM      Conversation a
          INNER JOIN userList b
            ON a.From_ID = b.ID
          INNER JOIN userList c
            ON a.To_ID = c.ID
WHERE     (LEAST(a.FROM_ID, a.TO_ID), GREATEST(a.FROM_ID, a.TO_ID), DeliveryDate)
IN
(
    SELECT  LEAST(FROM_ID, TO_ID) minFROM,
            GREATEST(FROM_ID, TO_ID) maxTo,
            MAX(DeliveryDate) maxDate
    FROM    Conversation
    GROUP BY minFROM, maxTo
)

SQLFiddle Demo

SQLFiddle 演示

回答by Fluffeh

Part 2 - Subqueries

第 2 部分 - 子查询

Okay, now the boss has burst in again - I want a list of all of our cars with the brand and a total of how many of that brand we have!

好的，现在老板又闯进来了——我想要一份我们所有带有该品牌的汽车的清单，以及我们拥有该品牌的总数！

This is a great opportunity to use the next trick in our bag of SQL goodies - the subquery. If you are unfamiliar with the term, a subquery is a query that runs inside another query. There are many different ways to use them.

这是使用 SQL 好东西中的下一个技巧 - 子查询的绝佳机会。如果您不熟悉该术语，则子查询是在另一个查询中运行的查询。有许多不同的方法可以使用它们。

For our request, lets first put a simple query together that will list each car and the brand:

对于我们的请求，让我们首先将一个简单的查询放在一起，列出每辆车和品牌：

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

Now, if we wanted to simply get a count of cars sorted by brand, we could of course write this:

现在，如果我们想简单地获得按品牌分类的汽车数量，我们当然可以这样写：

select
    b.brand,
    count(a.ID) as countCars
from
    cars a
        join brands b
            on a.brand=b.ID
group by
    b.brand

+--------+-----------+
| brand  | countCars |
+--------+-----------+
| BMW    |         2 |
| Ford   |         2 |
| Nissan |         1 |
| Smart  |         1 |
| Toyota |         5 |
+--------+-----------+

So, we should be able to simply add in the count function to our original query right?

那么，我们应该能够简单地将 count 函数添加到我们的原始查询中，对吗？

select
    a.ID,
    b.brand,
    count(a.ID) as countCars
from
    cars a
        join brands b
            on a.brand=b.ID
group by
    a.ID,
    b.brand

+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  1 | Toyota |         1 |
|  2 | Ford   |         1 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  5 | Toyota |         1 |
|  6 | BMW    |         1 |
|  7 | Ford   |         1 |
|  8 | Toyota |         1 |
|  9 | Toyota |         1 |
| 10 | BMW    |         1 |
| 11 | Toyota |         1 |
+----+--------+-----------+
11 rows in set (0.00 sec)

Sadly, no, we can't do that. The reason is that when we add in the car ID (column a.ID) we have to add it into the group by - so now, when the count function works, there is only one ID matched per ID.

可悲的是，不，我们不能那样做。原因是当我们添加汽车 ID（a.ID 列）时，我们必须将其添加到组中 - 所以现在，当计数功能起作用时，每个 ID 只有一个 ID 匹配。

This is where we can however use a subquery - in fact we can do two completely different types of subquery that will return the same results that we need for this. The first is to simply put the subquery in the selectclause. This means each time we get a row of data, the subquery will run off, get a column of data and then pop it into our row of data.

然而，这是我们可以使用子查询的地方——事实上，我们可以执行两种完全不同类型的子查询，它们将返回我们需要的相同结果。第一种是简单地将子查询放在select子句中。这意味着每次我们获取一行数据时，子查询都会运行，获取一列数据，然后将其弹出到我们的数据行中。

select
    a.ID,
    b.brand,
    (
    select
        count(c.ID)
    from
        cars c
    where
        a.brand=c.brand
    ) as countCars
from
    cars a
        join brands b
            on a.brand=b.ID

+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  2 | Ford   |         2 |
|  7 | Ford   |         2 |
|  1 | Toyota |         5 |
|  5 | Toyota |         5 |
|  8 | Toyota |         5 |
|  9 | Toyota |         5 |
| 11 | Toyota |         5 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  6 | BMW    |         2 |
| 10 | BMW    |         2 |
+----+--------+-----------+
11 rows in set (0.00 sec)

And Bam!, this would do us. If you noticed though, this sub query will have to run for each and every single row of data we return. Even in this little example, we only have five different Brands of car, but the subquery ran eleven times as we have eleven rows of data that we are returning. So, in this case, it doesn't seem like the most efficient way to write code.

和 Bam！，这对我们有用。如果你注意到了，这个子查询必须为我们返回的每一行数据运行。即使在这个小例子中，我们也只有五个不同品牌的汽车，但是子查询运行了 11 次，因为我们有 11 行数据要返回。因此，在这种情况下，它似乎不是编写代码的最有效方式。

For a different approach, lets run a subquery and pretend it is a table:

对于不同的方法，让我们运行一个子查询并假装它是一个表：

select
    a.ID,
    b.brand,
    d.countCars
from
    cars a
        join brands b
            on a.brand=b.ID
        join
            (
            select
                c.brand,
                count(c.ID) as countCars
            from
                cars c
            group by
                c.brand
            ) d
            on a.brand=d.brand

+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  1 | Toyota |         5 |
|  2 | Ford   |         2 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  5 | Toyota |         5 |
|  6 | BMW    |         2 |
|  7 | Ford   |         2 |
|  8 | Toyota |         5 |
|  9 | Toyota |         5 |
| 10 | BMW    |         2 |
| 11 | Toyota |         5 |
+----+--------+-----------+
11 rows in set (0.00 sec)

Okay, so we have the same results (ordered slightly different - it seems the database wanted to return results ordered by the first column we picked this time) - but the same right numbers.

好的，所以我们有相同的结果（排序略有不同 - 似乎数据库想要返回按我们这次选择的第一列排序的结果） - 但正确的数字相同。

So, what's the difference between the two - and when should we use each type of subquery? First, lets make sure we understand how that second query works. We selected two tables in the fromclause of our query, and then wrote a query and told the database that it was in fact a table instead - which the database is perfectly happy with. There canbe some benefits to using this method (as well as some limitations). Foremost is that this subquery ran once. If our database contained a large volume of data, there could well be a massive improvement over the first method. However, as we are using this as a table, we have to bring in extra rows of data - so that they can actually be joined back to our rows of data. We also have to be sure that there are enoughrows of data if we are going to use a simple join like in the query above. If you recall, the join will only pull back rows that have matching data on bothsides of the join. If we aren't careful, this could result in valid data not being returned from our cars table if there wasn't a matching row in this subquery.

那么，两者之间有什么区别——我们应该什么时候使用每种类型的子查询？首先，让我们确保我们了解第二个查询是如何工作的。我们在from查询的子句中选择了两个表，然后编写了一个查询并告诉数据库它实际上是一个表 - 数据库对此非常满意。有可能是使用这种方法一些好处（以及一些限制）。最重要的是这个子查询运行了一次。如果我们的数据库包含大量数据，则很可能比第一种方法有很大的改进。但是，由于我们将其用作表，因此必须引入额外的数据行 - 以便它们实际上可以连接回我们的数据行。我们还必须确保有足够的如果我们要使用像上面查询中的简单连接，则为数据行。如果你还记得，连接只会拉回到那个有匹配数据行两方的加入。如果我们不小心，如果此子查询中没有匹配的行，这可能会导致无法从我们的汽车表中返回有效数据。

Now, looking back at the first subquery, there are some limitations as well. because we are pulling data back into a single row, we can ONLYpull back one row of data. Subqueries used in the selectclause of a query very often use only an aggregate function such as sum, count, maxor another similar aggregate function. They don't haveto, but that is often how they are written.

现在，回顾第一个子查询，也有一些限制。因为我们将数据拉回单行，所以我们只能拉回一行数据。中所使用的子查询select的查询的子句常常只使用一个聚合函数如sum，count，max或其他类似的聚合函数。他们不具备，但是这往往是他们是如何写的。

So, before we move on, lets have a quick look at where else we can use a subquery. We can use it in the whereclause - now, this example is a little contrived as in our database, there are better ways of getting the following data, but seeing as it is only for an example, lets have a look:

所以，在我们继续之前，让我们快速看看我们还可以在哪里使用子查询。我们可以在where子句中使用它- 现在，这个例子在我们的数据库中有点人为，有更好的方法来获取以下数据，但它只是一个例子，让我们看看：

select
    ID,
    brand
from
    brands
where
    brand like '%o%'

+----+--------+
| ID | brand  |
+----+--------+
|  1 | Ford   |
|  2 | Toyota |
|  6 | Holden |
+----+--------+
3 rows in set (0.00 sec)

This returns us a list of brand IDs and Brand names (the second column is only added to show us the brands) that contain the letter oin the name.

这会返回一个品牌 ID 和品牌名称列表（添加第二列只是为了向我们显示品牌），其中包含o名称中的字母。

Now, we could use the results of this query in a where clause this:

现在，我们可以在 where 子句中使用此查询的结果：

select
    a.ID,
    b.brand
from
    cars a
        join brands b
            on a.brand=b.ID
where
    a.brand in
        (
        select
            ID
        from
            brands
        where
            brand like '%o%'
        )

+----+--------+
| ID | brand  |
+----+--------+
|  2 | Ford   |
|  7 | Ford   |
|  1 | Toyota |
|  5 | Toyota |
|  8 | Toyota |
|  9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)

As you can see, even though the subquery was returning the three brand IDs, our cars table only had entries for two of them.

如您所见，即使子查询返回了三个品牌 ID，我们的汽车表也只有其中两个的条目。

In this case, for further detail, the subquery is working as if we wrote the following code:

在这种情况下，为了进一步了解，子查询就像我们编写了以下代码一样工作：

select
    a.ID,
    b.brand
from
    cars a
        join brands b
            on a.brand=b.ID
where
    a.brand in (1,2,6)

+----+--------+
| ID | brand  |
+----+--------+
|  1 | Toyota |
|  2 | Ford   |
|  5 | Toyota |
|  7 | Ford   |
|  8 | Toyota |
|  9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)

Again, you can see how a subquery vs manual inputs has changed the order of the rows when returning from the database.

同样，您可以看到子查询与手动输入在从数据库返回时如何更改行的顺序。

While we are discussing subqueries, lets see what else we can do with a subquery:

在我们讨论子查询时，让我们看看我们还可以用子查询做什么：

• You can place a subquery within another subquery, and so on and so on. There is a limit which depends on your database, but short of recursive functions of some insane and maniacal programmer, most folks will never hit that limit.
• You can place a number of subqueries into a single query, a few in the selectclause, some in the fromclause and a couple more in the whereclause - just remember that each one you put in is making your query more complex and likely to take longer to execute.

• 您可以将子查询放在另一个子查询中，依此类推。有一个限制取决于您的数据库，但缺少一些疯狂和疯狂的程序员的递归函数，大多数人永远不会达到这个限制。
• 您可以将一些子查询到一个查询，几个在select条款，一些在from子句和一对夫妇更多的where条款-只要记住你把每一个都让你的查询更复杂，可能需要较长的时间执行。

If you need to write some efficient code, it can be beneficial to write the query a number of ways and see (either by timing it or by using an explain plan) which is the optimal query to get your results. The first way that works may not always be the best way of doing it.

如果您需要编写一些高效的代码，则可以通过多种方式编写查询并查看（通过计时或使用解释计划）哪个是获得结果的最佳查询。第一种有效的方法可能并不总是最好的方法。

回答by Fluffeh

Part 3 - Tricks and Efficient Code

第 3 部分 - 技巧和高效代码

MySQL in() efficiency

MySQL in() 效率

I thought I would add some extra bits, for tips and tricks that have come up.

我想我会添加一些额外的内容，以获取出现的提示和技巧。

One question I see come up a fair bit, is How do I get non-matching rows from two tablesand I see the answer most commonly accepted as something like the following (based on our cars and brands table - which has Holdenlisted as a brand, but does not appear in the cars table):

我看到的一个问题是，如何从两个表中获取不匹配的行，我看到最常接受的答案如下（基于我们的汽车和品牌表 -霍尔顿列为品牌，但未出现在汽车表中）：

select
    a.ID,
    a.brand
from
    brands a
where
    a.ID not in(select brand from cars)

And yesit will work.

而且是它的工作。

+----+--------+
| ID | brand  |
+----+--------+
|  6 | Holden |
+----+--------+
1 row in set (0.00 sec)

However it is notefficient in some database. Here is a link to a Stack Overflow questionasking about it, and here is an excellent in depth articleif you want to get into the nitty gritty.

然而，它在某些数据库中效率不高。这是一个关于Stack Overflow 问题的链接，如果你想深入了解它，这里是一篇优秀的深度文章。

The short answer is, if the optimiser doesn't handle it efficiently, it may be much better to use a query like the following to get non matched rows:

简短的回答是，如果优化器不能有效地处理它，使用如下查询来获取不匹配的行可能会好得多：

select
    a.brand
from
    brands a
        left join cars b
            on a.id=b.brand
where
    b.brand is null

+--------+
| brand  |
+--------+
| Holden |
+--------+
1 row in set (0.00 sec)

Update Table with same table in subquery

在子查询中使用相同的表更新表

Ahhh, another oldie but goodie - the old You can't specify target table 'brands' for update in FROM clause.

啊哈，又一个老掉牙的好东西 - 旧的你不能在 FROM 子句中为更新指定目标表 'brands'。

MySQL will not allow you to run an update...query with a subselect on the same table. Now, you might be thinking, why not just slap it into the where clause right? But what if you want to update only the row with the max()date amoung a bunch of other rows? You can't exactly do that in a where clause.

MySQL 不允许您update...在同一个表上使用子选择运行查询。现在，您可能会想，为什么不直接将其放入 where 子句中呢？但是，如果您只想更新max()日期在一堆其他行中的行怎么办？你不能在 where 子句中完全做到这一点。

update 
    brands 
set 
    brand='Holden' 
where 
    id=
        (select 
            id 
        from 
            brands 
        where 
            id=6);
ERROR 1093 (HY000): You can't specify target table 'brands' 
for update in FROM clause

So, we can't do that eh? Well, not exactly. There is a sneaky workaround that a surprisingly large number of users don't know about - though it does include some hackery that you will need to pay attention to.

所以，我们不能这样做吧？嗯，不完全是。有一个令人惊讶的大量用户不知道的偷偷摸摸的解决方法 - 尽管它确实包含一些您需要注意的hackery。

You can stick the subquery within another subquery, which puts enough of a gap between the two queries so that it will work. However, note that it might be safest to stick the query within a transaction - this will prevent any other changes being made to the tables while the query is running.

您可以将子查询放在另一个子查询中，这在两个查询之间放置了足够的间隙，以便它可以工作。但是，请注意，将查询保留在事务中可能是最安全的 - 这将防止在查询运行时对表进行任何其他更改。

update 
    brands 
set 
    brand='Holden' 
where id=
    (select 
        id 
    from 
        (select 
            id 
        from 
            brands 
        where 
            id=6
        ) 
    as updateTable);

Query OK, 0 rows affected (0.02 sec)
Rows matched: 1  Changed: 0  Warnings: 0

回答by prashant1988

You can use the concept of multiple queries in the FROM keyword. Let me show you one example:

您可以在 FROM 关键字中使用多个查询的概念。让我给你看一个例子：

SELECT DISTINCT e.id,e.name,d.name,lap.lappy LAPTOP_MAKE,c_loc.cnty COUNTY    
FROM  (
          SELECT c.id cnty,l.name
          FROM   county c, location l
          WHERE  c.id=l.county_id AND l.end_Date IS NOT NULL
      ) c_loc, emp e 
      INNER JOIN dept d ON e.deptno =d.id
      LEFT JOIN 
      ( 
         SELECT l.id lappy, c.name cmpy
         FROM   laptop l, company c
         WHERE l.make = c.name
      ) lap ON e.cmpy_id=lap.cmpy

You can use as many tables as you want to. Use outer joins and union where ever it's necessary, even inside table subqueries.

您可以根据需要使用任意数量的表。在任何需要的地方使用外连接和联合，即使在表子查询中也是如此。

That's a very easy method to involve as many as tables and fields.

这是一种非常简单的方法，可以涉及与表和字段一样多的内容。

回答by Anton Chan

Hopes this makes it find the tables as you're reading through the thing:

希望这使它在您阅读该内容时找到表格：

jsfiddle

提琴手

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    |                |
+-------+-------------+------+-----+---------+----------------+