Part 1 - Joins and Unions (第1部分 - 联接和联盟)
This answer covers: (这个答案包括：)
- Part 1 (第1部分)
- Part 2 (第2部分)
- Subqueries - what they are, where they can be used and what to watch out for (子查询 - 它们是什么，它们可以在哪里使用以及需要注意什么)
- Cartesian joins AKA - Oh, the misery! (笛卡尔加入了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. (在这个答案中，我将使用ANSI-92连接语法。) 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 much easier to understand when the queries start getting more complex. (这可能与许多使用旧版ANSI-89语法的其他教程不同（如果你习惯了89，可能看起来不那么直观 - 但我只能说是尝试它），因为它更容易了解查询何时开始变得更复杂。) Why use it? (为什么要用它？) Is there a performance gain? (是否有性能提升？) The short answer is no, but it is easier to read once you get used to it. (简短的回答是否定的，但是一旦你习惯它就会更容易阅读。) It is easier to read queries written by other folks using this syntax. (使用此语法更容易读取其他人编写的查询。)
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. (我还将使用一个小型caryard的概念，它有一个数据库来跟踪它有哪些可用的汽车。) 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 . (因此，老板想知道他拥有的所有跑车的身份证 。)
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 model column of the cars table relates to the models column of the cars table we have. (如您所见， cars表的model列中的数据与我们拥有的cars表的models列相关。) Now, we know that the models table has an ID of 1 for Sports so lets write the join. (现在，我们知道模型表的Sports ID为1 ，因此我们可以编写连接。)
<c
