23 高级SQL功能之8.2 子查询与连接查询

在上一章中，我们探讨了复杂查询的一些基本概念和技法。本章将进一步深入研究在SQL中执行复杂数据检索时常用的两种强大工具：子查询和连接查询。这两种方法在执行多表查询时各有其优势，能够帮助我们更高效地处理数据。

子查询

子查询，也称为嵌套查询，是指在一个查询的WHERE子句、FROM子句、或SELECT列表中嵌套另一个查询。子查询的结果可以用于过滤、计算或输入数据。

子查询的类型

1. 单行子查询: 返回单一的记录。
2. 多行子查询: 返回多条记录，可以使用IN、ANY、或ALL等操作符进行比较。
3. 无限制子查询: 不限制返回的记录数量。

子查询示例

假设有两张表：employees（员工表）和departments（部门表），结构如下：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2),
    department_id INT
);

CREATE TABLE departments (
    id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

使用单行子查询

如果我们想找出薪水高于某一特定员工的所有员工，可以使用单行子查询：

SELECT *
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE name = 'John Doe');

在这个示例中，子查询将返回John Doe的薪水，外部查询将列出所有薪水高于这个值的员工。

使用多行子查询

如果想要查询部门中所有薪水高于该部门平均薪水的员工，可以这样写：

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id);

这里的子查询计算了当前部门的平均薪水，并在外部查询中进行比较。

连接查询

连接查询使用JOIN语句在两张或更多表之间建立联系，以便从中提取相关数据。常见的连接类型包括：INNER JOIN、LEFT JOIN、RIGHT JOIN、和FULL OUTER JOIN。

连接查询示例

在我们的例子中，假设要查询所有员工及其所属部门的名称，可以使用INNER JOIN：

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

在这个查询中，只有那些有匹配部门的员工会被返回。

LEFT JOIN 示例

如果我们想要列出所有员工，即使他们没有部门，也可以使用LEFT JOIN：

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

这里，所有员工都会出现在结果中，而那些没有部门的员工将在department_name字段中显示为NULL。

子查询与连接的比较

• 性能: 子查询在某些情况下可能比连接查询性能差，尤其是在处理大数据集时。连接查询通常更快，但这要根据具体情况而定。
• 可读性: 子查询在逻辑上更直观，特别是在需要对一个特定值进行多次比较时。而连接查询通常使得数据关系更清晰。

小结

在本章中，我们讨论了如何使用子查询和连接查询来处理复杂SQL查询。这两种方法各有优劣，具体使用时可以根据实际需求进行选择。了解这些高级SQL功能，将帮助我们在数据处理和分析中更得心应手。

在下一章中，我们将探索存储过程与触发器，这些确保数据库管理和数据一致性的强大工具。敬请期待！