Sql 简明教程
SQL - Inner Join
SQL Join 子句用于基于公共字段/列合并数据库中多个相关的表。
An SQL Join clause is used to combine multiple related tables in a database, based on common fields/columns.
有两种主要的连接类型： Inner Join 和 Outer Join 。左连接、右连接、全连接等其他连接只是这两种主要连接的子类型。在本教程中，我们将仅学习内部连接。
There are two major types of joins: Inner Join and Outer Join. Other joins like Left Join, Right Join, Full Join etc. Are just subtypes of these two major joins. In this tutorial, we will only learn about the Inner Join.
The SQL Inner Join
SQL Inner Join 是一种通过检索两表中具有匹配值（公共列中）的记录来合并多个表的连接类型。
The SQL Inner Join is a type of join that combines multiple tables by retrieving records that have matching values in both tables (in the common column).
它将第一张表的每一行与第二张表的每一行进行比较，以查找满足连接谓词的所有行对。当满足连接谓词时，两个表中的列值会被合并到一张新表中。
It compares each row of the first table with each row of the second table, to find all pairs of rows that satisfy the join-predicate. When the join-predicate is satisfied, the column values from both tables are combined into a new table.
Explanation
我们来看一个示例场景以便更好地理解。
Let us look at an example scenario to have a better understanding.
假设我们有一家公司的员工信息，该信息分散在 EmpDetails 和 Marital status 这两张表中，其中：
Suppose we have the information of employees in a company divided between two tables namely EmpDetails and Marital status. Where,
-
EmpDetails table holds details like Employee ID, Name and Salary.
-
MaritalStatus table holds the details Employee ID, Age, and Marital Status.
当我们基于连接谓词 EmpDetails.EmpID = MaritalStatus.EmpID 在这两张表上执行内部连接操作时，最终记录将包含匹配记录的 ID、姓名、薪水、年龄和状态。
When we perform the Inner Join operation on these two tables based on the join-predicate EmpDetails.EmpID = MaritalStatus.EmpID, the resultant records hold the following info: ID, Name, Salary, Age and, Status of the matched records.
Syntax
以下是 SQL 里的内部连接的基本语法：
Following is the basic syntax of SQL Inner Join −
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;Example
假设我们使用以下查询创建了一张名为 CUSTOMERS 的表，其中包含客户的个人详细信息，包括他们的姓名、年龄、地址和薪水等：
Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc., using the following query −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);现在使用 INSERT 语句向该表中插入值，如下所示：
Now insert values into this table using the INSERT statement as follows −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );该表将被创建为：
The table will be created as −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Khilan |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
4 |
Chaitali |
25 |
Mumbai |
6500.00 |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |
让我们创建另一个名为 ORDERS 的表，其中包含所做的订单和下单日期的详细信息。
Let us create another table ORDERS, containing the details of orders made and the date they are made on.
CREATE TABLE ORDERS (
OID INT NOT NULL,
DATE VARCHAR (20) NOT NULL,
CUSTOMER_ID INT NOT NULL,
AMOUNT DECIMAL (18, 2)
);使用 INSERT 语句像下面这样向该表中插入值：
Using the INSERT statement, insert values into this table as follows −
INSERT INTO ORDERS VALUES
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);该表显示如下：
The table is displayed as follows −
OID |
DATE |
CUSTOMER_ID |
AMOUNT |
102 |
2009-10-08 00:00:00 |
3 |
3000.00 |
100 |
2009-10-08 00:00:00 |
3 |
1500.00 |
101 |
2009-11-20 00:00:00 |
2 |
1560.00 |
103 |
2008-05-20 00:00:00 |
4 |
2060.00 |
现在让我们使用 Inner Join query 将这两张表组合起来，如下所示：
Let us now combine these two tables using the Inner Join query as shown below −
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;Joining Multiple Tables Using Inner Join
到目前为止，我们仅学习了如何使用内部连接连接两张表。但是，我们还可以使用内部连接连接尽可能多的表，方法是指定条件（用于连接这些表）。
Until now, we have only learnt how to join two tables using Inner Join. However, we can also join as many tables as possible, using Inner Join, by specifying the condition (with which these tables are to be joined).
Syntax
以下是如何使用内联连接合并两个以上表的语法 −
Following is the syntax to join more than two tables using Inner Join −
SELECT column1, column2, column3...
FROM table1
INNER JOIN table2
ON condition_1
INNER JOIN table3
ON condition_2
....
....
INNER JOIN tableN
ON condition_N;请注意，在这种情况下，每次只能连接两个表的一个条件。此过程顺序执行，直到合并所有表。
Note that, even in this case, only two tables can be joined together on a single condition. This process is done sequentially until all the tables are combined.
Example
让我们使用先前的表 CUSTOMERS 和 ORDERS，加上一个新的表 EMPLOYEE 。我们使用以下查询创建 EMPLOYEE 表 −
Let us make use of the previous tables CUSTOMERS and ORDERS along with a new table EMPLOYEE. We will create the EMPLOYEE table using the query below −
CREATE TABLE EMPLOYEE (
EID INT NOT NULL,
EMPLOYEE_NAME VARCHAR (30) NOT NULL,
SALES_MADE DECIMAL (20)
);现在，我们可以使用 INSERT 语句将值插入到此空表中，如下所示：
Now, we can insert values into this empty tables using the INSERT statement as follows −
INSERT INTO EMPLOYEE VALUES
(102, 'SARIKA', 4500),
(100, 'ALEKHYA', 3623),
(101, 'REVATHI', 1291),
(103, 'VIVEK', 3426);EMPLOYEE 表的详细信息如下所述。
The details of EMPLOYEE table can be seen below.
EID |
EMPLOYEE_NAME |
SALES_MADE |
102 |
SARIKA |
4500 |
100 |
ALEKHYA |
3623 |
101 |
REVATHI |
1291 |
103 |
VIVEK |
3426 |
使用以下查询，我们可以合并三个表 CUSTOMERS、ORDERS 和 EMPLOYEE。
Using the following query, we can combine three tables CUSTOMERS, ORDERS and EMPLOYEE.
SELECT OID, DATE, AMOUNT, EMPLOYEE_NAME FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
INNER JOIN EMPLOYEE
ON ORDERS.OID = EMPLOYEE.EID;Inner Join with WHERE Clause
SQL 中的子句用于在使用 SQL 查询检索数据时应用约束。有许多子句 SQL 用于约束数据；例如 WHERE 子句、GROUP BY 子句、ORDER BY 子句、UNION 子句等。
Clauses in SQL work with the purpose of applying constraints while retrieving data using SQL queries. There are various clauses that SQL uses to constraint the data; such as WHERE clause, GROUP BY clause, ORDER BY clause, UNION clause etc.
WHERE 子句用于过滤表中的数据。此子句指定一个条件以仅检索满足该条件的那些记录。
The WHERE clause is used to filter the data from tables. This clause specifies a condition to retrieve only those records that satisfy it.
内联连接使用 WHERE 子句对要检索的数据应用更多约束。例如，在检索组织的员工记录时，如果我们只想查看每月收入超过 25000 的员工的数据，我们需要指定 WHERE 条件 (salary > 25000) 以仅检索那些员工记录。
Inner Join uses WHERE clause to apply more constraints on the data to be retrieved. For instance, while retrieving the employee records of an organization, if we only want to check the data of employees that earn more than 25000 in a month, we need to specify a WHERE condition (salary > 25000) to retrieve only those employee records.
Syntax
当与 WHERE 子句结合使用时，内联连接的语法如下 −
The syntax of Inner Join when used with WHERE clause is given below −
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;Example
在此示例中，我们使用内联连接查询连接表 CUSTOMERS 和 ORDERS，并使用 WHERE 子句对结果应用一些约束。
In this example we are joining the tables CUSTOMERS and ORDERS using the inner join query and we are applying some constraints on the result using the WHERE clause.
在此，我们从 CUSTOMERS 表检索 ID 和 NAME，从 ORDERS 表检索 DATE 和 AMOUNT，其中支付金额高于 2000。
Here, we are retrieving the ID and NAME from the CUSTOMERS table and DATE and AMOUNT from the ORDERS table where the amount paid is higher than 2000.
SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00;