极客笔记SQL 外连接
- 在SQL外连接中， 两个表中的所有内容被整合在一起。
- 即使两个表中的记录匹配与否，SQL外连接的输出将考虑来自两个表的匹配和非匹配记录。
- SQL中有三种不同类型的外连接:
- 左外连接
- 右外连接
- 全外连接
现在让我们通过示例深入了解SQL中的不同类型的外连接。所有示例中的查询将使用MySQL数据库编写。
考虑我们有以下带有给定数据的表:
表1：employee
| EmployeeID | Employee_Name | Employee_Salary |
|---|---|---|
| 1 | Arun Tiwari | 50000 |
| 2 | Sachin Rathi | 64000 |
| 3 | Harshal Pathak | 48000 |
| 4 | Arjun Kuwar | 46000 |
| 5 | Sarthak Gada | 62000 |
| 6 | Saurabh Sheik | 53000 |
| 7 | Shubham Singh | 29000 |
| 8 | Shivam Dixit | 54000 |
| 9 | Vicky Gujral | 39000 |
| 10 | Vijay Bose | 28000 |
表2：部门
| DepartmentID | Department_Name | Employee_ID |
|---|---|---|
| 1 | Production | 1 |
| 2 | Sales | 3 |
| 3 | Marketing | 4 |
| 4 | Accounts | 5 |
| 5 | Development | 7 |
| 6 | HR | 9 |
| 7 | Sales | 10 |
表3：贷款
| LoanID | Branch | Amount |
|---|---|---|
| 1 | B1 | 15000 |
| 2 | B2 | 10000 |
| 3 | B3 | 20000 |
| 4 | B4 | 100000 |
| 5 | B5 | 150000 |
| 6 | B6 | 50000 |
| 7 | B7 | 35000 |
| 8 | B8 | 85000 |
表格 4: 借款人
| CustID | CustName | LoanID |
|---|---|---|
| 1 | Sonakshi Dixit | 1 |
| 2 | Shital Garg | 4 |
| 3 | Swara Joshi | 5 |
| 4 | Isha Deshmukh | 2 |
| 5 | Swati Bose | 7 |
| 6 | Asha Kapoor | 10 |
| 7 | Nandini Shah | 9 |
1. 左连接：
- 如果我们使用左连接来合并两个不同的表，则会从左表中获取所有记录。但是，我们只会从右表中获取具有左表中对应键的记录。
- 编写执行左连接查询的语法：
SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1 LEFT OUTER JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName;
示例1：
编写一个查询以执行左连接，左表为employee表，右表为department表。
查询：
mysql> SELECT e.EmployeeID, e.Employee_Name, e.Employee_Salary, d.DepartmentID, d.Department_Name FROM employee e LEFT OUTER JOIN department d ON e.EmployeeID = d.Employee_ID;
我们使用SELECT命令检索员工表和部门表中的EmployeeID、Employee_Name、Employee_Salary、DepartmentID和Department_Name。然后我们使用LEFT OUTER JOIN关键字执行了员工表和部门表的左外连接操作，其中’e’和’d’是别名。这两个表是根据存在于两个表中的EmployeeID列进行连接的。
您将获得以下输出：
| EmployeeID | Employee_Name | Employee_Salary | DepartmentID | Department_Name |
|---|---|---|---|---|
| 1 | Arun Tiwari | 50000 | 1 | Production |
| 2 | Sachin Rathi | 64000 | NULL | NULL |
| 3 | Harshal Pathak | 48000 | 2 | Sales |
| 4 | Arjun Kuwar | 46000 | 3 | Marketing |
| 5 | Sarthak Gada | 62000 | 4 | Accounts |
| 6 | Saurabh Sheik | 53000 | NULL | NULL |
| 7 | Shubham Singh | 29000 | 5 | Development |
| 8 | Shivam Dixit | 54000 | NULL | NULL |
| 9 | Vicky Gujral | 39000 | 6 | HR |
| 10 | Vijay Bose | 28000 | 7 | Sales |
EmployeeID，Employee_Name，Employee_Salary，Department_ID，Department_Name从employee和department表中检索出来。从employee表中检索出所有记录。从department表中检索出只有在employee表中有相应EmployeeID的记录。department表中的其余记录（即employee表的employeeID不匹配的记录）将显示为NULL。
例子2:
编写一个查询，将loan表作为左表，borrower表作为右表进行左外连接。
查询:
mysql> SELECT l.LoanID, l.Branch, l.Amount, b.CustID, b.CustName FROM Loan l LEFT OUTER JOIN Borrower b ON l.LoanID = b.LoanID;
我们使用了SELECT命令来检索贷款和借款人表中的LoanID，Branch，Amount，CustID和CustName。然后，我们使用LEFT OUTER JOIN关键字在贷款和借款人表上执行左外连接操作，其中’l’和’b’是别名。这两个表根据LoanID列进行连接，该列在两个表中都存在。
您将获得以下输出：
| LoanID | Branch | Amount | CustID | CustName |
|---|---|---|---|---|
| 1 | B1 | 15000 | 1 | Sonakshi Dixit |
| 2 | B2 | 10000 | 4 | Isha Deshmukh |
| 3 | B3 | 20000 | NULL | NULL |
| 4 | B4 | 100000 | 2 | Shital Garg |
| 5 | B5 | 150000 | 3 | Swara Joshi |
| 6 | B6 | 50000 | NULL | NULL |
| 7 | B7 | 35000 | 5 | Swati Bose |
| 8 | B8 | 85000 | NULL | NULL |
贷款编号（LoanID），分支（Branch），金额（Amount），客户编号（CustID），客户姓名（CustName）从贷款（loan）和借款人（borrower）表中检索。从贷款表中检索所有记录。仅从借款人表中检索那些在贷款表中有对应贷款编号（LoanID）的记录。借款人表中那些贷款编号（LoanID）与贷款表中的贷款编号（LoanID）不匹配的其它记录，则显示为NULL。
2. 右外连接（Right Outer Join）：
- 右外连接是左外连接的相反。如果我们使用右外连接来组合两个不同的表，那么我们将从右表中获取所有记录。但是我们只会从左表中获取那些在右表中具有对应键的记录。
- 执行右外连接的查询的语法：
SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1 RIGHT OUTER JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName;
示例1：
编写一个查询来执行右外连接，将员工表作为左表，部门表作为右表。
查询：
mysql> SELECT e.EmployeeID, e.Employee_Name, e.Employee_Salary, d.DepartmentID, d.Department_Name FROM employee e RIGHT OUTER JOIN department d ON e.EmployeeID = d.Employee_ID;
我们已经使用SELECT命令从员工表和部门表中检索了EmployeeID，Employee_Name，Employee_Salary，DepartmentID和Department_Name。然后我们使用RIGHT OUTER JOIN关键字在员工表和部门表上执行了右外连接运算，其中’e’和’d’是别名。这两个表通过在两个表中都存在的列EmployeeID进行连接。
您将获得以下输出：
| EmployeeID | Employee_Name | Employee_Salary | DepartmentID | Department_Name |
|---|---|---|---|---|
| 1 | Arun Tiwari | 50000 | 1 | Production |
| 3 | Harshal Pathak | 48000 | 2 | Sales |
| 4 | Arjun Kuwar | 46000 | 3 | Marketing |
| 5 | Sarthak Gada | 62000 | 4 | Accounts |
| 7 | Shubham Singh | 29000 | 5 | Development |
| 9 | Vicky Gujral | 39000 | 6 | HR |
| 10 | Vijay Bose | 28000 | 7 | Sales |
EmployeeID、Employee_Name、Employee_Salary、DepartmentID、Department_Name是从员工表和部门表中检索出来的。从部门表中检索出所有记录。只有在部门表中有相应的EmployeeID的记录才从员工表中检索出来。
示例2：
编写一个查询，以贷款表作为左表，借款人表作为右表进行右外连接。
查询：
mysql> SELECT l.LoanID, l.Branch, l.Amount, b.CustID, b.CustName FROM Loan l RIGHT OUTER JOIN Borrower b ON l.LoanID = b.LoanID;
我们使用SELECT命令从贷款和借款人表中检索LoanID、Branch、Amount、CustID、CustName。然后我们使用RIGHT OUTER JOIN关键字在贷款和借款人表上执行右外连接操作，其中’l’和’b’是别名。这两个表根据LoanID列进行连接，该列在两个表中都存在。
您将获得以下输出：
| LoanID | Branch | Amount | CustID | CustName |
|---|---|---|---|---|
| 1 | B1 | 15000 | 1 | Sonakshi Dixit |
| 4 | B4 | 100000 | 2 | Shital Garg |
| 5 | B5 | 150000 | 3 | Swara Joshi |
| 2 | B2 | 10000 | 4 | Isha Deshmukh |
| 7 | B7 | 35000 | 5 | Swati Bose |
| NULL | NULL | NULL | 6 | Asha Kapoor |
| NULL | NULL | NULL | 7 | Nandini Shah |
从贷款和借款人表中检索LoanID、Branch、Amount、CustID和CustName。从借款人表中检索所有记录。仅检索与借款人表中的LoanID对应的记录。对于贷款表中LoanID与借款人表的LoanID不匹配的其他记录，则显示为NULL。
3. 全外连接：
- 如果我们使用全外连接来合并两个不同的表， 那么将获得来自两个表的所有记录， 即从左表和右表获取所有记录。
- MySQL不直接支持全外连接 。因此，为了在MySQL中实现全外连接，我们将在一个单一查询中执行两个查询。第一个查询将是左外连接的查询，第二个查询将是右外连接的查询。我们将使用UNION运算符将第一和第二个查询组合起来，以查看全外连接的结果。
- 编写执行全外连接查询的语法：
SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1 LEFT OUTER JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName UNION SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1 RIGHT OUTER JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName;
例子 1:
编写一个查询来执行全外连接，将雇员表作为左表，部门表作为右表考虑进去。
查询:
mysql> SELECT e.EmployeeID, e.Employee_Name, e.Employee_Salary, d.DepartmentID, d.Department_Name FROM department d LEFT OUTER JOIN employee e ON e.EmployeeID = d.Employee_ID UNION SELECT e.EmployeeID, e.Employee_Name, e.Employee_Salary, d.DepartmentID, d.Department_Name FROM department d RIGHT OUTER JOIN employee e ON e.EmployeeID = d.Employee_ID;
我们使用SELECT命令检索在员工表和部门表中存在的EmployeeID、Employee_Name、Employee_Salary、DepartmentID和Department_Name。然后我们使用LEFT OUTER JOIN关键字在员工表和部门表上执行左外连接操作，其中’e’和’d’是别名。然后我们编写了一个SELECT查询，在员工表和部门表上执行右外连接操作，其中’e’和’d’是别名。这两个表是根据在两个表中都存在的EmployeeID列进行连接的。这两个SELECT查询使用UNION运算符组合起来。
您将得到以下输出：
| EmployeeID | Employee_Name | Employee_Salary | DepartmentID | Department_Name |
|---|---|---|---|---|
| 1 | Arun Tiwari | 50000 | 1 | Production |
| 3 | Harshal Pathak | 48000 | 2 | Sales |
| 4 | Arjun Kuwar | 46000 | 3 | Marketing |
| 5 | Sarthak Gada | 62000 | 4 | Accounts |
| 7 | Shubham Singh | 29000 | 5 | Development |
| 9 | Vicky Gujral | 39000 | 6 | HR |
| 10 | Vijay Bose | 28000 | 7 | Sales |
| 2 | Sachin Rathi | 64000 | NULL | NULL |
| 6 | Saurabh Sheik | 53000 | NULL | NULL |
| 8 | Shivam Dixit | 54000 | NULL | NULL |
从员工和部门表中检索EmployeeID、Employee_Name、Employee_Salary和Department_ID、Department_Name。由于进行了左外连接，从员工表中检索出所有记录。只有那些在员工表中有对应EmployeeID的记录才会从部门表中检索出来。对于那些员工表的EmployeeID与部门表的EmployeeID不匹配的其他记录，将显示为NULL。由于进行了右外连接，从部门表中检索出所有记录。只有那些在部门表中有对应EmployeeID的记录才会从员工表中检索出来。
示例2：
编写一个查询，以贷款表作为左表，以借款人表作为右表进行全外连接。
查询：
mysql> SELECT l.LoanID, l.Branch, l.Amount, b.CustID, b.CustName FROM Loan l LEFT OUTER JOIN Borrower b ON l.LoanID = b.LoanID UNION SELECT l.LoanID, l.Branch, l.Amount, b.CustID, b.CustName FROM Loan l RIGHT OUTER JOIN Borrower b ON l.LoanID = b.LoanID;
我们已经使用SELECT命令从贷款和借款人表中检索LoanID，Branch，Amount，CustID和CustName。然后我们使用LEFT OUTER JOIN关键字在贷款和借款人表上执行左外连接操作，其中“l”和“b”是别名。然后我们编写了一个SELECT查询，在贷款和借款人表上执行右外连接操作，其中“l”和“b”是别名。这两个表根据LoanID列进行连接，该列在两个表中都存在。使用UNION运算符将这两个SELECT查询组合起来。
您将获得以下输出：
| LoanID | Branch | Amount | CustID | CustName |
|---|---|---|---|---|
| 1 | B1 | 15000 | 1 | Sonakshi Dixit |
| 2 | B2 | 10000 | 4 | Isha Deshmukh |
| 3 | B3 | 20000 | NULL | NULL |
| 4 | B4 | 100000 | 2 | Shital Garg |
| 5 | B5 | 150000 | 3 | Swara Joshi |
| 6 | B6 | 50000 | NULL | NULL |
| 7 | B7 | 35000 | 5 | Swati Bose |
| 8 | B8 | 85000 | NULL | NULL |
| NULL | NULL | NULL | 6 | Asha Kapoor |
| NULL | NULL | NULL | 7 | Nandini Shah |
从贷款和借款人表中检索LoanID、Branch、Amount、CustID、CustName。借款表中的所有记录都是通过左外连接检索出来的结果。只有在借款表中存在相应LoanID的记录才会从借款人表中检索出来。借款表中的借款ID与借款人表的LoanID不匹配的其他记录将显示为NULL。作为右外连接的结果，所有借款人表中的记录都被检索出来。只有在借款人表中存在相应LoanID的记录才会从借款表中检索出来。借款表中的借款ID与借款人表的LoanID不匹配的其他记录将显示为NULL。