数据库查询join的知识总结

ゝ一纸荒年。 2021-09-20 19:56 270阅读 0赞

在sql当中，有下面这些join类型：

*  **（INNER）JOIN：返回两个表中具有匹配值的记录**
 *  **LEFT（OUTER）JOIN：返回左侧表中的所有记录，以及右侧表中的匹配记录**
 *  **RIGHT (OUTER) JOIN：从右表返回所有记录，并从左表返回匹配的记录**
 *  **FULL（OUTER）JOIN：当左表或右表中存在匹配项时，返回所有记录**

**分别如下图所示：**

![20191015174836829.gif][]![20191015174847888.gif][]![20191015174849686.gif][]![20191015174851975.gif][]

### Inner join ###

INNER JOIN和JOIN是一个意思。

INNER JOIN选择两个表中具有匹配值的记录：

一个INNER JOIN的例子如下：

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

示例database如下：

以下是Orders表中的一些数据：

<table> 
 <tbody> 
  <tr> 
   <th>OrderID</th> 
   <th>CustomerID</th> 
   <th>EmployeeID</th> 
   <th>OrderDate</th> 
   <th>ShipperID</th> 
  </tr> 
  <tr> 
   <td>10308</td> 
   <td>2</td> 
   <td>7</td> 
   <td>1996-09-18</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>10309</td> 
   <td>37</td> 
   <td>3</td> 
   <td>1996-09-19</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>10310</td> 
   <td>77</td> 
   <td>8</td> 
   <td>1996-09-20</td> 
   <td>2</td> 
  </tr> 
 </tbody> 
</table>

下面是Customers表中的一些数据：

<table> 
 <tbody> 
  <tr> 
   <th>CustomerID</th> 
   <th>CustomerName</th> 
   <th>ContactName</th> 
   <th>Address</th> 
   <th>City</th> 
   <th>PostalCode</th> 
   <th>Country</th> 
  </tr> 
  <tr> 
   <td>1<br> &nbsp;</td> 
   <td>Alfreds Futterkiste</td> 
   <td>Maria Anders</td> 
   <td>Obere Str. 57</td> 
   <td>Berlin</td> 
   <td>12209</td> 
   <td>Germany</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Ana Trujillo Emparedados y helados</td> 
   <td>Ana Trujillo</td> 
   <td>Avda. de la Constitución 2222</td> 
   <td>México D.F.</td> 
   <td>05021</td> 
   <td>Mexico</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Antonio Moreno Taquería</td> 
   <td>Antonio Moreno</td> 
   <td>Mataderos 2312</td> 
   <td>México D.F.</td> 
   <td>05023</td> 
   <td>Mexico</td> 
  </tr> 
 </tbody> 
</table>

以下SQL语句选择所有包含客户信息的订单：

SELECT Orders.OrderID, Customers.CustomerName
    FROM Orders
    INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

该查询的查询结果为

<table> 
 <tbody> 
  <tr> 
   <th>OrderID</th> 
   <th>CustomerName</th> 
  </tr> 
  <tr> 
   <td>10308</td> 
   <td>Ana Trujillo Emparedados y helados</td> 
  </tr> 
 </tbody> 
</table>

再来看另一个例子，假设我们现在有如下的database：

Persons表如下：

<table> 
 <tbody> 
  <tr> 
   <th>Id_P</th> 
   <th>LastName</th> 
   <th>FirstName</th> 
   <th>Address</th> 
   <th>City</th> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Adams</td> 
   <td>John</td> 
   <td>Oxford Street</td> 
   <td>London</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Bush</td> 
   <td>George</td> 
   <td>Fifth Avenue</td> 
   <td>New York</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Carter</td> 
   <td>Thomas</td> 
   <td>Changan Street</td> 
   <td>Beijing</td> 
  </tr> 
 </tbody> 
</table>

Orders表如下：

<table> 
 <tbody> 
  <tr> 
   <th>Id_O</th> 
   <th>OrderNo</th> 
   <th>Id_P</th> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>77895</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>44678</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>22456</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>24562</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>34764</td> 
   <td>65</td> 
  </tr> 
 </tbody> 
</table>

我们希望查出所有人的订单，使用inner join查询如下：

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
    FROM Persons
    INNER JOIN Orders
    ON Persons.Id_P=Orders.Id_P
    ORDER BY Persons.LastName

则其查询结果为：

<table> 
 <tbody> 
  <tr> 
   <th>LastName</th> 
   <th>FirstName</th> 
   <th>OrderNo</th> 
  </tr> 
  <tr> 
   <td>Adams</td> 
   <td>John</td> 
   <td>22456</td> 
  </tr> 
  <tr> 
   <td>Adams</td> 
   <td>John</td> 
   <td>24562</td> 
  </tr> 
  <tr> 
   <td>Carter</td> 
   <td>Thomas</td> 
   <td>77895</td> 
  </tr> 
  <tr> 
   <td>Carter</td> 
   <td>Thomas</td> 
   <td>44678</td> 
  </tr> 
 </tbody> 
</table>

INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "Persons" 中的行在 "Orders" 中没有匹配，就不会列出这些行。

### LEFT JOIN ###

LEFT JOIN关键字从左表（表1）返回所有记录，并从右表（表2）返回匹配的记录。 如果不匹配，则右侧结果为NULL。

示例database如下：

下面是Customers表中的一些数据：

下面是Orders表中的数据：

下面是一个LEFT JOIN的例子：

SELECT Customers.CustomerName, Orders.OrderID
    FROM Customers
    LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

上面的查询结果为：

<table> 
 <tbody> 
  <tr> 
   <th>CustomerName</th> 
   <th>OrderID</th> 
  </tr> 
  <tr> 
   <td>Alfreds Futterkiste</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>Ana Trujillo Emparedados y helados</td> 
   <td>10308</td> 
  </tr> 
  <tr> 
   <td>Antonio Moreno Taquería</td> 
   <td>NULL</td> 
  </tr> 
 </tbody> 
</table>

LEFT JOIN 关键字会从左表 (Customers) 那里返回所有的行，即使在右表 (Orders) 中没有匹配的行。

### RIGHT JOIN ###

在某些数据库中， RIGHT JOIN 称为 RIGHT OUTER JOIN， RIGHT JOIN的写法如下：

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

示例database如下：

下面是orders表中的一些数据：

下面是Employees表中的一些数据：

<table> 
 <tbody> 
  <tr> 
   <th>EmployeeID</th> 
   <th>LastName</th> 
   <th>FirstName</th> 
   <th>BirthDate</th> 
   <th>Photo</th> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Davolio</td> 
   <td>Nancy</td> 
   <td>12/8/1968</td> 
   <td>EmpID1.pic</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Fuller</td> 
   <td>Andrew</td> 
   <td>2/19/1952</td> 
   <td>EmpID2.pic</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Leverling</td> 
   <td>Janet</td> 
   <td>8/30/1963</td> 
   <td>EmpID3.pic</td> 
  </tr> 
 </tbody> 
</table>

一个right join的例子为：

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
    FROM Orders
    RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID

上面查询的查询结果为：

<table> 
 <tbody> 
  <tr> 
   <th>OrderID</th> 
   <th>LastName</th> 
   <th>FirstName</th> 
  </tr> 
  <tr> 
   <td>NULL</td> 
   <td>Davolio</td> 
   <td>Nancy</td> 
  </tr> 
  <tr> 
   <td>NULL</td> 
   <td>Fuller</td> 
   <td>Andrew</td> 
  </tr> 
  <tr> 
   <td>10310</td> 
   <td>Leverling</td> 
   <td>Janet</td> 
  </tr> 
 </tbody> 
</table>

RIGHT JOIN 关键字会从右表 (Employees) 那里返回所有的行，即使在左表 (orders) 中没有匹配的行

### FULL JOIN ###

full join的语法如下：

SELECT column_name(s)
    FROM table1
    FULL OUTER JOIN table2
    ON table1.column_name = table2.column_name
    WHERE condition;

示例database如下：

下面是Customers表中的一些数据：

下面是Orders表中的一些数据：

使用full join查询的sql如下：

SELECT Customers.CustomerName, Orders.OrderID
    FROM Customers
    FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID

其查询结果为：

再来看另一个例子

Person表如下：

Orders表如下：

现有full join如下：

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
    FROM Persons
    FULL JOIN Orders
    ON Persons.Id_P=Orders.Id_P
    ORDER BY Persons.LastName

则其查询结果为：

FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 "Persons" 中的行在表 "Orders" 中没有匹配，或者如果 "Orders" 中的行在表 "Persons" 中没有匹配，这些行同样会列出。

[20191015174836829.gif]: https://img-blog.csdnimg.cn/20191015174836829.gif
[20191015174847888.gif]: https://img-blog.csdnimg.cn/20191015174847888.gif
[20191015174849686.gif]: https://img-blog.csdnimg.cn/20191015174849686.gif
[20191015174851975.gif]: /images/20210920/8cbb08a25cbb40988804f6ab7df0d92d.png