Sql 简明教程

SQL - Left Join

联接用于基于两个或更多表之间的逻辑关系从这些表中检索记录。此关系使用联接条件定义。正如我们在前几章中讨论过的，有两种类型的联接 -

Joins are used to retrieve records from two or more tables based on a logical relation between them. This relation is defined using a join condition. As we discussed in the previous chapters, there are two types of Joins −

Inner Join
Outer 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. First of all, let us understand what is outer join.

What is Outer Join?

外部联接用于将多个数据库表联接到一个合并的 result-set 中，其中包括所有记录，即使它们不满足联接条件。在不满足联接条件的这些记录中显示 NULL 值。

Outer Join is used to join multiple database tables into a combined result-set, that includes all the records, even if they don’t satisfy the join condition. NULL values are displayed against these records where the join condition is not met.

外部联接有三种类型，即 -

There are three types of outer joins, namely −

*Left (Outer) Join: * Retrieves all the records from the first table, Matching records from the second table and NULL values in the unmatched rows.
*Right (Outer) Join: * Retrieves all the records from the second table, Matching records from the first table and NULL values in the unmatched rows.
*Full (Outer) Join: * Retrieves records from both the tables and fills the unmatched values with NULL.

以下图表展示了两个表（即 EmpDetails 和 MaritalStatus）之间的各种外部联接。此处，联接操作假定基于联接谓词 EmpDetails.EmpID = MaritalStatus.EmpID.

Following diagram illustrates various outer joins between two tables namely, EmpDetails and MaritalStatus. Here, the join operation is presumed based on the join-predicate EmpDetails.EmpID = MaritalStatus.EmpID.

The SQL Left Join

SQL 中的左联接或左外部联接将两个或更多个表组合在一起，其中第一个表将全部返回；但是，仅从后续表中检索匹配的记录。如果在后续表中匹配了零 (0) 条记录，则联接仍将在结果中返回一行，但右表中每列都显示为 NULL。

Left Join or Left Outer Join in SQL combines two or more tables, where the first table is returned wholly; but, only the matching record(s) are retrieved from the consequent tables. If zero (0) records are matched in the consequent tables, the join will still return a row in the result, but with NULL in each column from the right table.

Syntax

以下是 SQL 中左联接的基本语法 -

Following is the basic syntax of Left Join in SQL −

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example

为了更好地理解此查询，让我们在现有数据库中创建一些表，并使用左联接或左外部联接将它们联接起来。

To understand this query better, let us create some tables in an existing database and join them using Left Join or Left Outer Join.

假设我们已经使用以下查询创建了一个名为 CUSTOMERS 的表，其中包含客户的个人详细信息，包括其姓名、年龄、地址和工资。

Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary, 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

以下 left join query 检索在指定日期下达订单和未下达订单的客户的详细信息。如果没有找到匹配项，则以下查询将在该记录中返回 NULL。

Following left join query, retrieves the details of customers who made an order at the specified date and who did not. 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 resultant table is obtained as −

NAME

AMOUNT

DATE

Ramesh

NULL

Khilan

1560.00

2009-11-20 00:00:00

Kaushik

1500.00

2009-10-08 00:00:00

Kaushik

3000.00

2009-10-08 00:00:00

Chaitali

2060.00

2008-05-20 00:00:00

Hardik

NULL

Komal

NULL

Muffy

NULL

正如我们在上面的表中看到的，只有 Khilan、Kaushik 和 Chaitali 在 ORDERS 表中提到的日期下了订单；因此，记录匹配。CUSTOMERS 表中的其他客户未在指定日期下订单，因此记录返回为 NULL。

As we can see in the table above, only Khilan, Kaushik and Chaitali made purchases on the mentioned dates in ORDERS table; hence, the records are matched. The other customers in CUSTOMERS table did not make purchases on the specified dates, so the records are returned as NULL.

Joining Multiple Tables with Left Join

与内部联接查询类似，左联接也会将多个表联接在一起，其中第一个表按原样返回，其余表与第一个表中的行匹配。如果记录不匹配，则返回 NULL。

Similar to the Inner Join query, Left Join also joins multiple tables where the first table is returned as it is and the remaining tables are matched with the rows in the first table. If the records are not matched, NULL is returned.

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

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

SELECT column1, column2, column3...
FROM table1
LEFT JOIN table2
ON condition_1
LEFT JOIN table3
ON condition_2
....
....
LEFT JOIN tableN
ON condition_N;

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 the EMPLOYEE table 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);

EMPLOYEE 表包含某个组织中员工的详细信息及他们进行的销售。

The EMPLOYEE table consists of the details of employees in an organization and sales made by them.

EID	EMPLOYEE_NAME	SALES_MADE
102	SARIKA	4500
100	ALEKHYA	3623
101	REVATHI	1291
103	VIVEK	3426

下面的查询使用左联接联接 CUSTOMERS、ORDERS 和 EMPLOYEE 表

Following query joins the CUSTOMERS, ORDERS and EMPLOYEE tables using the left join −

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;

通过此查询，我们将显示客户的 id、名称以及创建订单的日期和销售该商品的员工的名称。

Through this query, we will display the id, name of the customer along with the date on which the orders are made and the name of the employee who sold the item.

Output

结果表如下：

The resultant table is obtained as follows −

NAME

DATE

EMPLOYEE_NAME

Ramesh

NULL

Khilan

2009-11-20 00:00:00

REVATHI

Kaushik

2009-10-08 00:00:00

ALEKHYA

Kaushik

2009-10-08 00:00:00

SARIKA

Chaitali

2008-05-20 00:00:00

VIVEK

Hardik

NULL

Komal

NULL

Muffy

NULL

正如我们在上表中看到的，客户 Kaushik 下了三笔订单，其中两笔由员工 Alekhya 售出，一笔由 Sarika 售出。Khilan 和 Chaitali 各下单一笔，分别由 Revathi 和 Vivek 售出。这些订单所做的日期也会显示。如果没有在特定日期下订单，则返回 NULL。

As we can see in the table above, the customer Kaushik made three orders, in which two are sold by employee Alekhya and one is sold by Sarika. Khilan and Chaitali made one order each, that are sold by Revathi and Vivek respectively. The dates on which these orders are made will also be displayed. If the orders are not made on the specific dates, NULL is returned.

Left Join with WHERE Clause

除了 ON 子句，可以在实现左联接后对获得的结果集应用 WHERE 子句。这将进一步筛选数据。

Along with the ON clause, a WHERE clause can also be applied on the obtained result-set after Left Join is implemented. Doing this will filter the data further.

Syntax

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

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

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.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

使用左联接应用 where 子句后得到的结果表包含金额值大于 2000.00 的行

The resultant table after applying the where clause with left join contains the rows that has amount values greater than 2000.00 −

NAME

DATE

AMOUNT

Kaushik

2009-10-08 00:00:00

3000.00

Chaitali

2008-05-20 00:00:00

2060.00