提取表和子查询的区别(derived table vs subqueries)

子查询，大家再熟悉不过了，例如： select employee_name from employee where employee_salary ( select avg(employee_salary) from employee ) 下面这也是一个子查询，没用到where语句。 INSERT INTO math_study_group(id, name) SELECT id, name FROM stu

子查询，大家再熟悉不过了，例如：

select employee_name
from employee
where employee_salary >

(select avg(employee_salary)
from employee)

下面这也是一个子查询，没用到where语句。

INSERT INTO math_study_group(id, name)
SELECT id, name
FROM student_details WHERE subject= 'Math'

下面粗体部分是提取表（derived table）

delete from id where Id not in

(select Id)

from

(select id,peopleId from people)as a

GROUP by peopleId );

两者的区别就是

- derived tables are used in the FROM clause
- subqueries are used in the WHERE clause, but can also
be used to select from one table and insert into
another as we showed above

翻译过来就是

提取表是用在 from 语句中的。

子查询是用在 where语句中的，但是也可以用在从一张表中查询，然后插入到另一张表，就像上面展示的那个例子一样。