为什么在这种情况下我需要创建一个子查询，而不是仅仅进行比较

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM employees
WHERE SALARY > (SELECT AVG(SALARY)
FROM employees)

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM employees
WHERE SALARY > AVG(SALARY)

您的第一个语句之所以有效，是因为您有两个SELECT语句，每个语句都定义了一个不同的集合。一个集合通过avg()函数进行聚合，另一个集合保持非聚合状态。

它也起作用，因为聚合集是标量的(它只有一个值)。它可以将非聚合集中的每一行与单值聚合集中的标量值进行比较。如果该聚合集是以非标量的方式定义的，那么就会引发错误，您将不得不通过FROM子句中JOIN中的ON子句或通过相关的子查询来建立两个集之间的关系。

另一个原因是操作顺序。通过WHERE子句进行筛选首先发生在SQL的执行中。在对您的数据进行筛选时，还没有发生其他操作。GROUP BY/Agregation在SQL执行过程中发生得非常晚。因此，您试图将SQL中两个非常不同的步骤的结果相互比较。

操作顺序是HAVING存在的原因。它与WHERE非常相似，但在聚合之后起作用。不过，这对您在该SQL中尝试执行的操作没有帮助，因为您再次尝试将非聚合集中的值与聚合集的值进行比较，而这在单个SELECT语句中根本无法完成。

可能值得注意的是，您可以使用"；窗口函数"；(也称为"有序分析函数"或"分析函数")，其在大多数RDBMS中得到支持。

例如：

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, AVG(SALARY) OVER () as avg_salary
FROM employees;

这仍然会为表employees中的每一行吐出一个非聚合行。它将有一个第四列，每行包含所有员工的平均工资每一行都将包含相同的值

+-------------+------------+------------+--------+------------+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME  | SALARY | avg_salary |
+-------------+------------+------------+--------+------------+
|           1 | bob        | mcbob      |    100 |       210  |
|           2 | sue        | o'susan    |    230 |       210  |
|           3 | venkat     | van venkat |    300 |       210  |
+-------------+------------+------------+--------+------------+

也就是说，由于操作顺序的原因，您无法比较WHERE或HAVING子句内的窗口函数的结果。在sql执行中，窗口函数逻辑几乎每隔一步都会运行一次。你只会得到一个不允许的错误。因此，您将再次需要两个SELECT语句：

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM 
(
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, AVG(SALARY) OVER () as avg_salary
FROM employees;
) dt
WHERE SALARY > avg_salary;

最后，市场上有两个RDBMS具有QUALIFY子句(Snowflake和Teradata)，它们类似于允许窗口函数成为过滤器一部分的WHERE或HAVING子句。如果你使用的是这两个平台中的一个，那么你可以把它写成一个SELECT语句：

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM employees
QUALIFY SALARY > AVG(SALARY) OVER (); 

就像WHERE在执行开始时起作用，HAVING在执行结束时起作用一样，QUALIFY的作用甚至比这晚(就在ORDER BY之前)。奇怪的是，这正是您最初想要的，而且一些RDBMS预料到了您的需求。我希望更多的RDBMS在未来的版本中使用QUALIFY子句，因为它非常方便。