SQL Join

Joins and Keys
Joins 和 Keys

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:
当你在观察下面的举例时应该注意下面这几点： 

• The "Employee_ID" column is the primary key of the "Employees" table
  "Employee_ID"栏目是"Employees"表的主键
• The "Prod_ID" column is the primary key of the "Orders" table
  "Prod_ID"栏目是"Orders"表的主键
• The "Employee_ID" column in the "Orders" table is used to refer to the persons in the "Employees" table without using their names
  在"Orders"表中的"Employee_ID"栏目可以直接与"Employees"表中的个人信息挂钩而不需要用到他们的姓名

---

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

---

Referring to Two Tables
双表合用

We can select data from two tables by referring to two tables, like this:
我们可以从两个数据表选择关联到两个表的数据，可以是这样：

Example
举例

Who has ordered a product, and what did they order?
是谁下了产品定单，还有他们订购了什么？

SELECT Employees.Name, Orders.Product
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID

Result
结果

Name Product Hansen, Ola Printer Svendson, Stephen Table Svendson, Stephen Chair

Example
举例

Who ordered a printer?
谁订购了台打印机？

SELECT Employees.Name
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID
AND Orders.Product='Printer'

Result
结果

Name Hansen, Ola

---

Using Joins
使用Joins

OR we can select data from two tables with the JOIN keyword, like this:
或者我们可以通过使用JOIN关键来选择两个表的数据，像这样：

Example INNER JOIN
举例 INNER JOIN

Syntax
语法

SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

Who has ordered a product, and what did they order?
谁下了产品定单，还有他们订购了些什么？

SELECT Employees.Name, Orders.Product
FROM Employees
INNER JOIN Orders

ON Employees.Employee_ID=Orders.Employee_ID

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

Example LEFT JOIN
举例 LEFT JOIN

Syntax
语法

SELECT field1, field2, field3
FROM first_table
LEFT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

List all employees, and their orders - if any.
列出所有人员，以及他们所下的定单 - 如果有的话

SELECT Employees.Name, Orders.Product
FROM Employees
LEFT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

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  

Example RIGHT JOIN
举例 RIGHT JOIN

Syntax
语法

SELECT field1, field2, field3
FROM first_table
RIGHT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

List all orders, and who has ordered - if any.
列出所有的定单，还有下的定单的人 - 如果有的话

SELECT Employees.Name, Orders.Product
FROM Employees
RIGHT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

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

Example
举例

Who ordered a printer?
谁订购了打印机？

SELECT Employees.Name
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
WHERE Orders.Product = 'Printer'

Result
结果

Name Hansen, Ola