左、右和全外连接之间的差异

原文:https://www . geeksforgeeks . org/左-右-和-全-外-连接的区别/

数据库管理系统(DBMS)允许使用连接从多个表中检索数据。

联接主要是两个或多个关系(或表)的笛卡尔乘积。

SQL 连接大致分为内部连接和外部连接。内部联接从满足联接条件的表中选择行。但是使用内部连接会丢失两个表中不满足条件的行。外部连接可用于防止表中的数据丢失。

外连接的类型: 外连接又分为 3 种:左外连接、右外连接和全外连接。这些解释如下。

1. Left Outer Join: Left Outer Join returns all the rows from the table on the left and columns of the table on the right is null padded. Left Outer Join retrieves all the rows from both the tables that satisfy the join condition along with the unmatched rows of the left table.

   语法:

   ```sql SELECT [column1, column2, ....] FROM table1

   LEFT OUTER JOIN table2 ON table1.matching_column = table2.matching_column

   WHERE [condition]; ```

   或者

   ```sql SELECT [column1, column2, ....] FROM table1

   LEFT OUTER JOIN table2 ON table1.matching_column = table2.matching_column

   WHERE [condition]; ```

   图解表示:

2. Right Outer Join: Right Outer Join returns all the rows from the table on the right and columns of the table on the left is null padded. Right Outer Join retrieves all the rows from both the tables that satisfy the join condition along with the unmatched rows of the right table.

   语法:

   ```sql SELECT [column1, column2, ....] FROM table1

   RIGHT OUTER JOIN table2 ON table1.matching_column = table2.matching_column

   WHERE [condition]; ```

   或者，

   ```sql SELECT [column1, column2, ....] FROM table1

   RIGHT OUTER JOIN table2 ON table1.matching_column(+) = table2.matching_column

   WHERE [condition]; ```

   图解表示:

3. Full Outer Join: Full Outer Join returns all the rows from both the table. When no matching rows exist for the row in the left table, the columns of the right table are null padded. Similarly, when no matching rows exist for the row in the right table, the columns of the left table are null padded. Full outer join is the union of left outer join and right outer join.

   语法:

   ```sql SELECT [column1, column2, ....] FROM table1

   FULL OUTER JOIN table2 ON table1.matching_column = table2.matching_column

   WHERE [condition]; ```

   图解表示:

例: 考虑以下员工表，

部门表:

现在， 1。左外连接查询–

Select empid, ename, deptid, deptname 
from employee 
left outer join department 
on employee.empdept = department.deptname; 

输出:

2。右外连接查询–

Select empid, ename, deptid, deptname 
from employee right outer join department 
on employee.empdept = department.deptname;

3。完全外部连接查询–

Select empid, ename, deptid, deptname 
from employee full outer join department 
on employee.empdept = department.deptname;

左外连接、右外连接、全外连接之间的差异:

版权属于：月萌API www.moonapi.com，转载请注明出处

本文链接：https://www.moonapi.com/news/26789.html