当前位置: 首页 > 网络学院 > 服务端脚本教程 > SQL > SQL Join
Sometimes we have to select data from two or more tables to make our result complete. We have to perform a join.
有些时候为了让结果更加完整我们需要对两个或多个数据表进行同时的查询。这样我们就必须执行一次联合。
Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.
在数据库中的数据表们可以通过他们的keys来与其他的表进行关联。主键是指每记录行带有唯一值的栏目。在同一表中的主键值必须是唯一的。这样做的意图是让多个交错在不同数据表中的数据绑定到一起成为可能,而不用在每个表中重复所有数据。
In the "Employees" table below, the "Employee_ID" column is the primary key, meaning that no two rows can have the same Employee_ID. The Employee_ID distinguishes two persons even if they have the same name.
在下的"Employees"表中,"Employee_ID"栏目就是一个主键,这就意味着该表中没有两行记录中的Employee_ID值是相同的。这样即便是有两个同名的人存在也可以将他们区分开来。
When you look at the example tables below, notice that:
当你在观察下面的举例时应该注意下面这几点:
Employees表:
Employee_ID | Name |
---|---|
01 | Hansen, Ola |
02 | Svendson, Tove |
03 | Svendson, Stephen |
04 | Pettersen, Kari |
Orders表:
Prod_ID | Product | Employee_ID |
---|---|---|
234 | Printer | 01 |
657 | Table | 03 |
865 | Chair | 03 |
We can select data from two tables by referring to two tables, like this:
我们可以从两个数据表选择关联到两个表的数据,可以是这样:
Who has ordered a product, and what did they order?
是谁下了产品定单,还有他们订购了什么?
SELECT Employees.Name, Orders.Product |
Result
结果
Name | Product |
---|---|
Hansen, Ola | Printer |
Svendson, Stephen | Table |
Svendson, Stephen | Chair |
Who ordered a printer?
谁订购了台打印机?
SELECT Employees.Name |
Result
结果
Name |
---|
Hansen, Ola |
OR we can select data from two tables with the JOIN keyword, like this:
或者我们可以通过使用JOIN关键来选择两个表的数据,像这样:
Syntax
语法
SELECT field1, field2, field3 |
Who has ordered a product, and what did they order?
谁下了产品定单,还有他们订购了些什么?
SELECT Employees.Name, Orders.Product |
The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.
使用INNER JOIN可以返回两个表准所有符合条件的数据。任意一个条件不符合都不会列举出来
Result
结果
Name | Product |
---|---|
Hansen, Ola | Printer |
Svendson, Stephen | Table |
Svendson, Stephen | Chair |
Syntax
语法
SELECT field1, field2, field3 |
List all employees, and their orders - if any.
列出所有人员,以及他们所下的定单 - 如果有的话
SELECT Employees.Name, Orders.Product |
The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.
使用LEFT JOIN即使第二个表(Order)没有相符合的数据也会返回所有第一张表(Employees)里的数据行。反过历来也是一样的。
Result
结果
Name | Product |
---|---|
Hansen, Ola | Printer |
Svendson, Tove | |
Svendson, Stephen | Table |
Svendson, Stephen | Chair |
Pettersen, Kari |
Syntax
语法
SELECT field1, field2, field3 |
List all orders, and who has ordered - if any.
列出所有的定单,还有下的定单的人 - 如果有的话
SELECT Employees.Name, Orders.Product |
The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.
RIGHT JOIN会将所有第二张表(Orders)里的的数据行返回出来,即便第一张表(Employees)没有相符合的数据。反过来也一样
Result
Name | Product |
---|---|
Hansen, Ola | Printer |
Svendson, Stephen | Table |
Svendson, Stephen | Chair |
Who ordered a printer?
谁订购了打印机?
SELECT Employees.Name |
Result
结果
Name |
---|
Hansen, Ola |