SQL 进阶 表复杂的连接和关联

1、内连接 (INNER JOIN)

内连接（INNER JOIN）是一种常用的表连接方式，用于从两个或多个表中返回满足连接条件的行。当需要基于一或多个共同字段合并表时，内连接非常有用。内连接返回两个表中匹配的记录。如果记录在一个表中有匹配，在另一个表中也有，则该记录会被包含在结果集中。

SELECT a.*, b.*
FROM table1 a
INNER JOIN table2 b ON a.common_field = b.common_field;

1）多表连接

内连接不仅限于两个表，可以连接多个表。若想从客户表、订单表和产品表中获取信息，如下，

SELECT customers.name, orders.order_date, products.product_name
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
INNER JOIN products ON orders.product_id = products.product_id;

2）使用别名

为表设置别名可以简化查询语句，尤其是在涉及多个表的连接时：

SELECT c.name, o.order_date, p.product_name
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
INNER JOIN products AS p ON o.product_id = p.product_id;

3）复合连接条件

可以在ON子句中使用多个条件来连接表，这些条件可以使用AND或OR逻辑运算符组合：

SELECT a.column, b.column
FROM table_a AS a
INNER JOIN table_b AS b
ON a.id = b.a_id AND a.date = b.date;

4）使用聚合函数和分组

连接后，可能需要对数据进行分组和聚合统计：

SELECT c.customer_id, COUNT(o.order_id) AS order_count
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

这个查询将显示每个客户的订单数量。

5）过滤结果

连接后可以使用WHERE子句进一步过滤结果：

SELECT c.name, o.order_date
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2023-01-01';

2、外连接 (OUTER JOIN)

外连接（OUTER JOIN）是一种非常有用的查询类型，可以将两个或多个表中的行进行合并，即使在其中一个表中没有匹配的行也可以。外连接主要分为左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。左外连接 (LEFT JOIN) 返回左表的所有记录和右表中匹配的记录。如果右表没有匹配，结果中右表的部分将为NULL。

SELECT a.*, b.*
FROM table1 a
LEFT JOIN table2 b ON a.common_field = b.common_field;

右外连接 (RIGHT JOIN) 返回右表的所有记录和左表中匹配的记录。如果左表没有匹配，结果中左表的部分将为NULL。

SELECT a.*, b.*
FROM table1 a
RIGHT JOIN table2 b ON a.common_field = b.common_field;

全外连接 (FULL OUTER JOIN) 返回左表和右表中所有的记录。如果表的一侧没有匹配，则该侧的部分将为NULL。

SELECT a.*, b.*
FROM table1 a
FULL OUTER JOIN table2 b ON a.common_field = b.common_field;

1）左外连接（LEFT OUTER JOIN）

左外连接会返回左表（LEFT JOIN左边的表）的所有行，以及右表中与左表匹配的行。如果右表中没有匹配的行，则结果中右表的部分将为NULL。

有两个表，一个是employees（员工）表，另一个是departments（部门）表，需要列出所有员工以及他们所在的部门名称，即使某些员工没有分配到部门。如下，

SELECT employees.name, departments.name AS department
FROM employees
LEFT OUTER JOIN departments
ON employees.department_id = departments.id;

2）右外连接（RIGHT OUTER JOIN）

右外连接正好与左外连接相反，它会返回右表（RIGHT JOIN右边的表）的所有行，以及左表中与右表匹配的行。如果左表中没有匹配的行，则结果中左表的部分将为NULL。

要列出所有部门及其任何员工的姓名，即使某些部门没有员工也要列出。如下，

SELECT departments.name AS department, employees.name
FROM departments
RIGHT OUTER JOIN employees
ON departments.id = employees.department_id;

3） 全外连接（FULL OUTER JOIN）

全外连接结合了左外连接和右外连接的特点，返回左表和右表中的所有行。如果其中一个表中没有匹配的行，则另一个表中的相应列将为NULL。

列出所有员工和所有部门，即使他们之间没有关联。如下

SELECT employees.name, departments.name AS department
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.id;

3、自连接 (SELF JOIN)

自连接（SELF JOIN）是一种特殊的连接方式，用于将一个表与其自身进行连接。这种类型的连接非常有用，特别是在处理具有层次结构或需要比较同一表中的行时。通过自连接，可以在单个查询中解决这些问题，无需创建临时表或执行多次查询。自连接可能会对数据库性能产生影响，特别是当表非常大时。确保适当的索引存在，可以帮助提高查询性能。

有一个名为Employees的表，包含员工的EmployeeID, EmployeeName, 和 ManagerID字段，其中ManagerID是这名员工直属上司的EmployeeID。查找每个员工及其上司的名称，如下，

SELECT
    E.EmployeeName AS Employee,
    M.EmployeeName AS Manager
FROM
    Employees E
LEFT JOIN Employees M ON E.ManagerID = M.EmployeeID;

4、交叉连接 (CROSS JOIN)

交叉连接（CROSS JOIN）是一种特殊类型的连接，会返回参与连接的每个表的每一行与另一个表的每一行的所有可能组合。如果表A有N行，表B有M行，那么交叉连接的结果将会是N*M行。这种类型的连接没有匹配条件，因此它简单地将一个表的每一行与另一个表的每一行组合起来。交叉连接返回第一个表中的每一行与第二个表中的每一行的笛卡尔积。通常用于生成组合。

有两个表，一个是employees（员工），另一个是departments（部门）。如要查看每个员工与每个部门的所有可能组合，可以使用交叉连接。

-- 使用CROSS JOIN连接employees和departments表
SELECT
  e.employee_id,
  e.employee_name,
  d.department_id,
  d.department_name
FROM
  employees e
CROSS JOIN
  departments d;

5、使用多个连接类型

复杂的表连接和关联是数据库查询中常见的需求，特别是在处理涉及多个数据源的大型数据库时。在实际应用中，可能需要结合使用多种连接类型来解决复杂的查询需求。

有三个表：Employees (员工), Departments (部门), 和 Projects (项目)。目标是列出所有员工的信息，包括他们的部门名称和他们参与的项目名称。如员工没有参与任何项目，也应该列出。如下，

SELECT 
    E.Name AS EmployeeName,
    D.DepartmentName,
    P.ProjectName
FROM 
    Employees E
    INNER JOIN Departments D ON E.DepartmentID = D.DepartmentID  -- 连接员工和部门
    LEFT JOIN Projects P ON E.EmployeeID = P.EmployeeID           -- 连接员工和项目

6、连接与复杂条件

连接时可以包括多个条件，甚至将聚合函数和分组用于更复杂的数据分析。在进行复杂的连接时，特别是在大数据集上，需要注意可能的性能问题。

1）处理多表连接

当需要从多个表中获取数据时，可以使用链式连接，每个连接可以使用不同的连接类型。

SELECT *
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
LEFT JOIN salaries s ON e.employee_id = s.employee_id
WHERE s.salary > 50000;

2）使用复杂条件

连接条件不仅限于简单的等式，也可以包含更复杂的逻辑，如不等式、范围查询等。

SELECT e.name, e.email, d.name AS department, s.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN salaries s ON e.employee_id = s.employee_id AND s.salary BETWEEN 40000 AND 80000
WHERE e.status = 'Active';

3）利用子查询

在连接时，可以使用子查询来进一步限制需要连接的数据，这在处理非常大的数据集时特别有用。

SELECT e.name, e.email, d.name AS department
FROM employees e
JOIN (
    SELECT department_id, MAX(salary) AS max_salary
    FROM salaries
    GROUP BY department_id
) max_sal ON e.department_id = max_sal.department_id
WHERE e.salary = max_sal.max_salary;