Mysql 简明教程

MySQL - Left Join

与提供两个表的交集值的内部联接不同，还有一种称为 Outer Join 的联接类型。这种外联接在多种情况下提供了两个表的已匹配和未匹配记录的集合。

Unlike inner join, which provides the intersection values of two tables, there is another type of join called Outer Join. This outer join provides the collection of matched and unmatched records of two tables in multiple cases.

MySQL Left Join

Left Join 是一种检索第一个表中的所有记录并将它们与第二个表中的记录匹配的外联接。

Left Join is a type of outer join that retrieves all the records from the first table and matches them to the records in second table.

如果左表中的记录在第二个表中没有对应的记录，则添加 NULL 值。

If the records in left table do not have their counterparts in the second table, NULL values are added.

但是，如果第一个表中的记录数少于第二个表中的记录数，则第二个表中在第一个表中没有对应记录的记录将从结果中舍弃。

But, if the number of records in first table is less than the number of records in second table, the records in second table that do not have any counterparts in the first table will be discarded from the result.

leftjoin

Syntax

以下是 MySQL 中左联接的基本语法：

Following is the basic syntax of Left Join in MySQL −

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example

使用以下查询，让我们创建一个名为 CUSTOMERS 的表，其中包含客户的个人详细信息，包括姓名、年龄、地址和薪水。

Using the following query, let us create a table named CUSTOMERS, that contains the personal details of customers including their name, age, address and salary.

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 ，其中包含已下的订单及其下的日期。

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 −

Left Join Query:

Left Join Query:

使用如下左连接查询，我们将检索在指定日期下订购的客户的详细信息。如果没有找到匹配项，则以下查询将在该记录中返回 NULL。

Using the following left join query, we will retrieve the details of customers who made an order at the specified date. If there is no match found, the query below will return NULL in that record.

SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output

联接的结果集可通过以下方式获取：

The joined result-set is obtained as −

Joining Multiple Tables with Left Join

左连接也会将多个表格联接起来，其中第一个表格全部返回，而后续表格与第一个表格中的行匹配。如果记录未匹配，则返回 NULL。

Left Join also joins multiple tables where the first table is returned as a whole and the next tables are matched with the rows in the first table. If the records are not matched, NULL is returned.

Syntax

以下给出了使用左联接联接多个表的语法 -

The syntax to join multiple tables using Left Join is given below −

SELECT column1, column2, column3...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name
LEFT JOIN table3
ON table2.column_name = table3.column_name
.
.
.

Example

为了演示带有多个表格的左连接，让我们考虑先前创建的表格 CUSTOMERS 和 ORDERS。除此之外，我们将使用以下查询创建另一张名为 EMPLOYEE 的表格，其中包含一个组织中员工的详细信息及他们进行的销售：

To demonstrate Left Join with multiple tables, let us consider the previously created tables CUSTOMERS and ORDERS. In addition to these we will create another table named EMPLOYEE, which consists of the details of employees in an organization and sales made by them, using the following query −

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);

表创建如下 −

The table is created as −

Left Join Query:

Left Join Query:

让我们使用下面给出的左连接查询将这三个表格联接起来：

Let us join these three tables using the left join query given below −

SELECT CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.DATE, EMPLOYEE.EMPLOYEE_NAME
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
LEFT JOIN EMPLOYEE
ON ORDERS.OID = EMPLOYEE.EID;

Output

结果表如下：

The resultant table is obtained as follows −

Left Join with WHERE Clause

若要筛选联接两个表格后的记录，可以使用 WHERE 子句。

To filter the records after joining two tables, a WHERE clause can be applied.

Syntax

与 WHERE 子句一起使用时，Left Join 的语法如下：

The syntax of Left Join when used with WHERE clause is given below −

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name = table_name2.column_name
WHERE condition

Example

可以使用 WHERE 子句筛选组合数据库表中的记录。考虑之前的两个表 CUSTOMERS 和 ORDERS；并通过使用 WHERE 子句应用一些约束来使用左联接查询联接它们。

Records in the combined database tables can be filtered using the WHERE clause. Consider the previous two tables CUSTOMERS and ORDERS; and join them using the left join query by applying some constraints using the WHERE clause.

SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00;

Output

输出以以下方式获取：

The output is obtained as − −

Left Join Using a Client Program

我们还可以使用客户端程序对一张或多张表格执行左连接操作。

We can also perform the left join operation on one or more tables using a client program.

Syntax

Example

以下是这些程序 −

Following are the programs −