​数据库查询进阶--多表查询

1. 概述

在关系型数据库中，多表查询是一种常见的操作，用于检索和组合多个表的数据结果。通过多表查询，可以根据不同表之间的关联关系获取更全面和详细的数据。

2. 内连接（Inner Join）

内连接是最常用的多表查询类型，它通过匹配两个或多个表中的相关列来检索满足条件的记录。

2.1 语法

SELECT 列名
FROM 表1
INNER JOIN 表2 ON 条件

2.2 示例

假设有以下两个表：Customers 和 Orders。它们通过 CustomerID 列建立了关联关系。

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

      内连接 inner join，inner可省略，与基本连接相似，只是表现不同，实现效果一样。只是等值连接放在on语序块中。

相对于内连接还有外连接，分为左连接（也称左外连接Left Outer Join）、右连接（Right Outer Join）和全连接（Full Outer Join）三种类型。

• 左外连接：返回左表中的所有记录，以及符合连接条件的右表记录。
• 右外连接：返回右表中的所有记录，以及符合连接条件的左表记录。
• 全外连接：返回左表和右表中的所有记录，无论是否符合连接条件。

3. 左连接（Left Join）

左连接是另一种常见的多表查询类型，它返回左表中的所有记录以及与之关联的右表中的匹配记录。

3.1 语法

SELECT 列名
FROM 表1
LEFT JOIN 表2 ON 条件

3.2 示例

继续使用上述的 Customers 和 Orders 表，我们可以执行以下查询来获取所有客户以及他们对应的订单信息，即使他们没有订单。

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

4. 右连接（Right Join）

右连接是左连接的逆操作，它返回右表中的所有记录以及与之关联的左表中的匹配记录。

4.1 语法

SELECT 列名
FROM 表1
RIGHT JOIN 表2 ON 条件

4.2 示例

在上述的 Customers 和 Orders 表的例子中，以下查询将返回所有订单以及订单对应的客户信息，即使客户信息不完整。

SELECT Customers.CustomerName, Orders.OrderDate
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

5. 全外连接（Full Outer Join）

外连接返回两个表中的所有记录，无论是否有匹配的行。

5.1 语法

SELECT Customers.CustomerName, Orders.OrderDate
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

5.2 示例

通过以下查询，我们可以获取所有客户和订单的信息，无论是否存在匹配。

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

6. 交叉连接（Cross Join）

交叉连接（也称为叉积或笛卡尔积）是一种不基于任何条件关系的连接类型。它返回两个表的所有组合结果。

6.1 语法

SELECT 列名
FROM 表1
CROSS JOIN 表2

6.2 示例

假设有以下两个表：Customers 和 Orders。使用以下查询，我们可以获取所有可能的客户和订单组合结果：

SELECT Customers.CustomerName, Orders.OrderDate
FROM Customers
CROSS JOIN Orders;

7. 自连接（Self Join）

自连接是指在同一表中连接两个或多个实例。它通常用于解决需要查找层级结构数据的问题。

7.1 语法

sql
SELECT 列名
FROM 表1 t1
INNER JOIN 表1 t2 ON 条件

7.2 示例

假设有以下一张名为 Employees 的表，其中包含每个员工的 ID、姓名和经理 ID。

使用以下查询，我们可以获取每个员工及其对应的经理信息：

sql
SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
INNER JOIN Employees m ON e.ManagerID = m.ID;

8、表格对比

9、多表查询的注意事项

举个例子：

       假设有两个表：Customers（顾客）和Orders（订单），它们之间通过CustomerID列建立了关联。

表名别名：

SELECT c.CustomerName, o.OrderDate
FROM Customers AS c
JOIN Orders AS o ON c.CustomerID = o.CustomerID;

        在上述查询中，使用了AS关键字为表Customers和Orders设置了别名c和o。

列名冲突：

sqlSELECT OrderID
FROM Customers AS c
JOIN Orders AS o ON c.CustomerID = o.CustomerID;

        当两个表中存在相同名称的列（例如，OrderID），需要使用表名或别名来限定列，以避免冲突。

表之间的关联：

SELECT c.CustomerName, o.OrderDate
FROM Customers AS c
JOIN Orders AS o ON c.CustomerID = o.CustomerID;

        在上述查询中，使用JOIN语句将表Customers和Orders连接起来，通过CustomerID列建立关联。这样，我们可以获取顾客的姓名和订单的日期。

使用合适的连接类型：

SELECT c.CustomerName, o.OrderDate
FROM Customers AS c
LEFT JOIN Orders AS o ON c.CustomerID = o.CustomerID;

          在某些情况下，可能需要使用不同类型的连接操作符，如LEFT JOIN。这将返回所有Customers表中的记录，无论其在Orders表中是否有匹配项。

查询性能优化：

SELECT c.CustomerName, o.OrderDate
FROM Customers AS c
JOIN Orders AS o ON c.CustomerID = o.CustomerID
WHERE c.Country = 'China';

       为了提高查询性能，可以在涉及到关联的列上创建索引。在上述查询中，如果Customers表的Country列上有索引，可以加快检索顾客所在国家为中国的订单数据的速度。

字段命名一致性：

SELECT c.CustomerName, o.OrderDate
FROM Customers AS c
JOIN Orders AS o ON c.CustomerID = o.CustomerID;

       在查询中，尽量为字段使用一致、易理解的名称。例如，使用CustomerName表示顾客姓名，OrderDate表示订单日期，以确保字段命名的一致性和可读性。

谨慎使用通配符：

SELECT *
FROM Customers AS c
JOIN Orders AS o ON c.CustomerID = o.CustomerID;

       谨慎使用通配符（*）来选择所有列。最好明确指定需要的列，以免查询结果包含不必要的数据。明确列出所需的列，例如SELECT c.CustomerName, o.OrderDate，可以减少数据传输和提高查询效率。