Mysql 简明教程
MySQL - Inner Join
MySQL Inner Join
MySQL内连接是一种连接，它用于根据两个相关表中的公共列将两个相关表中的记录组合到一起。这些表基于特定条件连接在一起。如果两个表中的记录满足所指定的条件，则将它们组合在一起。
MySQL Inner Join is a type of join that is used to combine records from two related tables, based on common columns from both the tables. These tables are joined together on a specific condition. If the records in both tables satisfy the condition specified, they are combined.
这是一个默认连接；也就是说，即使使用JOIN关键字代替INNER JOIN，也会使用公共列的匹配记录连接表。它们还称为等值连接。
This is a default join; that is, even if the JOIN keyword is used instead of INNER JOIN, tables are joined using matching records of common columns. They are also referred to as an Equijoin.
Syntax
以下是MySQL内连接的基本语法−
Following is the basic syntax of MySQL Inner Join −
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_nameExample
创建一个名为CUSTOMERS的表，其中包含客户的个人信息，包括他们的姓名、年龄、地址和工资等。
Creating a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.
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 −
ORDERS Table −
ORDERS Table −
让我们创建另一个名为 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 −
Inner Join Query −
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
通过使用内连接查询，我们还可以连接尽可能多的表。
Using the Inner Join query, we can also join as many tables as possible.
不过，一次只能基于单个条件连接两个表。该过程按顺序进行，直到组合所有表。
However, only two tables can be joined together on a single condition. This process is done sequentially until all the tables are combined.
Syntax
以下是如何使用内联连接合并两个以上表的语法 −
Following is the syntax to join more than two tables using Inner Join −
SELECT column_name1, column_name2...
FROM table_name1
INNER JOIN
table_name2
ON condition_1
INNER JOIN
table_name3
ON condition_2
.
.
.Example
在此示例中，让我们连接三个表，包括 CUSTOMERS 和 ORDERS 以及新表 EMPLOYEE。首先，我们将通过以下查询创建 EMPLOYEE 表 −
In this example, let us join three tables including CUSTOMERS and ORDERS along with a new table EMPLOYEE. We will first 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 are seen below.
使用以下查询，我们正在组合三个表 CUSTOMERS、ORDERS 和 EMPLOYEE。
Using the following query, we are combining 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
内连接使用 WHERE 子句对要从表中检索的记录应用约束。
Inner Join uses WHERE clause to apply constraints on the records to be retrieved from a table.
Syntax
当与 WHERE 子句结合使用时，内联连接的语法如下 −
The syntax of Inner Join when used with WHERE clause is given below −
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name
WHERE conditionExample
考虑前两个表 CUSTOMERS 和 ORDERS，以及使用内连接查询通过使用 WHERE 子句应用一些约束来连接它们。
Consider the previous two tables CUSTOMERS and ORDERS; and join them using the inner join query by applying some constraints using the WHERE clause.
SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00;