mysql 里面为什么有时候子查询需要 alias，有时候则不需要呢？

[movq]

我给这个题提交了一个解答 https://leetcode.cn/problems/employees-with-missing-information/

这是我的代码：

with eid as (select employee_id
             from Employees),
     sid as (select employee_id
             from Salaries)
select employee_id
from (select e.employee_id as employee_id
       from eid e
       where e.employee_id not in
             (select * from sid)
      union
      select s.employee_id as employee_id
       from sid s
       where s.employee_id not in 
       		(select * from eid)
       ) t
order by employee_id;

可以发现，not in (select * from sid)和not in (select * from eid)这两个子查询都没有加 alias ，但可以通过，如果加了反而不通过

而

from (select e.employee_id as employee_id
       from eid e
       where e.employee_id not in
             (select * from sid)
      union
      select s.employee_id as employee_id
       from sid s
       where s.employee_id not in 
       		(select * from eid)
       ) t

这个子查询则需要加别名（我的命名是 t ）

为什么会这样呢？

[liprais]

有没有可能有些你觉得是子查询的，其实是 derive table 呢

[movq]

@liprais 所以是不是 from 后面是 derived table ，需要 alias ，但是 where clause 里面的是 subquery 不是 derived table ，所以不需要 alias ？

[liprais]

@movq 对

[FYFX]

你这个是 Subquery as Scalar Operand https://dev.mysql.com/doc/refman/8.0/en/scalar-subqueries.html,
然后你再看一下 derived table 的 https://dev.mysql.com/doc/refman/8.0/en/derived-tables.html
> The [AS] tbl_name clause is mandatory because every table in a FROM clause must have a name. Any columns in the derived table must have unique names.