Sql 简明教程

Left Join vs Right Join

左联接和右联接的主要区别在于联接表的方式。

The main difference between the Left Join and Right Join can be observed in the way tables are joined.

它们都是外联接的类型；也就是说，它们保留一张表中不匹配的行，并丢弃另一张表中不匹配的行。左联接保留左表中不匹配的行，而右联接保留右表中不匹配的行。

They are both types of Outer Joins; that is, they retain unmatched rows in one table and discard the unmatched rows of another. Left Join preserves the unmatched rows of left table while Right join preserves the unmatched rows of right table.

Working of Left Join

SQL 中的 Left Join 或 Left Outer Join 会合并两个或更多表，其中第一个表按原样返回；但仅从后续表中返回与第一个表有对应项的记录。

Left Join or Left Outer Join in SQL combines two or more tables, where the first table is returned as it is; but, only the record(s) that have counterparts in first table are returned from consequent tables.

如果 ON 子句与后续表中与第一个表中的行的记录匹配为零，左联接仍将返回第一个表中的这些行，但在右侧表的每一列中都为 NULL。

If the ON clause matches zero records in consequent tables with the rows in first table, left join will still return these rows of first table 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 table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example

下面的示例演示了两个相关表上的左联接操作。这里，第一个表包含薪资信息，而第二个表包含婚姻状况信息。由于 Alex 的状态未知，因此未记录在表中。

The example below demonstrates the Left Join operation on two relative tables. Here, the first table contains the salary information while the second table contains marital status information. Since Alex’s status is unknown, it is not recorded in the table.

ljvrj1

当两个表使用左联接查询联接时，由于没有记录与 Alex 的状态匹配，因此该值在最终表中记录为 NULL。

When both tables are joined using the Left Join query, since there is no record matching Alex’s Status, the value is recorded as NULL in the final table.

Working of Right Join

SQL 中的右联接或右外联接返回右侧表中的所有行，即使在左侧表中没有匹配项。这意味着，如果 ON 子句与右侧表中的记录在左侧表中匹配为 0（零）个记录；右联接仍将返回右侧表中的行，但在左侧表的每一列中都会有 NULL 值。

Right Join or Right Outer Join query in SQL returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in left table with the records in right table; right join will still return the rows of right table in the result, but with a NULL value in each column of the left table.

Syntax

以下是 SQL 中右联接的基本语法 −

Following is the basic syntax of a Right Join in SQL −

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

Example

现在在这个示例中，对相同的表执行右联接操作。这里，我们从右侧表开始联接；由于右侧表不包含与 Alex 行匹配的记录值，因此从最终表中丢弃该行。

Now in this example, the Right Join operation is performed on the same tables. Here, we are starting the join from the right table; since, the right table does not contain the record value matching Alex’s row, the row is discarded from the final table.

ljvrj2

最终表仅包含两行，因为右侧表仅包含两行。

The final table only consists of two rows as the right table consists of two rows only.

Left Join Vs Right Join

让我们在下面的表中总结左联接和右联接之间的所有差异 −

Let us summarize all the differences between the Left Join and Right Join in the table below −

Left Join

Right Join

Left Join matches the data of the first table or the left table with the data in second table. If the data is matched, the records are combined; otherwise, NULL is recorded.

Right Join matches the data of the second table or right table with the data in first table. If the data is matched, the records are combined; otherwise, NULL is recorded.

If the first table has less rows than the second table, extra unmatched rows from the second table are discarded.

If the second table has less rows than the first table, extra unmatched rows from the first table are discarded.

This Join is also known as Left Outer Join

This Join is also known as Right Outer Join

*= is used in Transact SQL, instead of using the LEFT JOIN or LEFT OUTER JOIN query.

=* is used in Transact SQL, instead of using the RIGHT JOIN or RIGHT OUTER JOIN query.

正如我们从总结中可以观察到的，左联接和右联接没有什么太大的不同。它们之间的每个差异都会归结为连接表和连接观点的方式。

As we can observe from the summary, there aren’t wide range of differences between the Left and Right joins. Every difference between them zeroes down to the way the tables are joined and the join point of view.