使用CTE的SQL查询返回0结果，子查询却正常，问题出在哪？

原需求查询（子查询版本） #

原查询逻辑是筛选出所有薪资高于employee_id为108的员工姓名：

SELECT first_name,
       last_name
  FROM employees
  WHERE salary > (SELECT salary
                    FROM employees
                    WHERE employee_id = 108);

你的CTE查询问题点 #

你编写的CTE查询存在逻辑错误：

with emp as
(
    select employee_id, salary from employees where employee_id = 108 
)
select first_name, last_name, employees.salary, emp.salary from employees
left join emp
on emp.employee_id = employees.employee_id
where employees.salary > emp.salary
order by employees.salary desc;

• 使用LEFT JOIN emp ON emp.employee_id = employees.employee_id后，只有employee_id等于108的那条记录，emp.salary才有值，其他所有员工的emp.salary都是NULL。
• SQL中任何值与NULL做比较（employees.salary > emp.salary）都会返回未知，这类记录会被WHERE条件过滤掉。而唯一有emp.salary值的108号员工，薪资不可能大于自己的薪资，最终返回0条结果。

正确的等价CTE写法 #

只需获取108号员工的薪资作为基准值，无需关联employee_id，以下两种写法都可行：

写法一：使用CROSS JOIN（因CTE仅返回一行，等价于笛卡尔积） #

with emp_salary as
(
    select salary from employees where employee_id = 108 
)
select first_name, last_name
from employees
cross join emp_salary
where employees.salary > emp_salary.salary
order by employees.salary desc;

写法二：在WHERE子句中引用CTE的单值 #

with emp_salary as
(
    select salary from employees where employee_id = 108 
)
select first_name, last_name
from employees
where employees.salary > (select salary from emp_salary)
order by employees.salary desc;

内容的提问来源于stack exchange，提问作者Vinit Dawane