查询结果数据表作为筛选条件
查询结果为一个值作为筛选条件,用在where后;查询结果为一个表,作为筛选条件,应用表连接;
查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
#①查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id#②连接①结果集和employees表,进行筛选
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary>ag_dep.ag ;
传统表连接与隐藏式连接
select * from A left/inner join B on A.department_id=B.department_id
SELECT job_id,location_id
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`department_id`=90;通过在 `FROM` 子句中列举多个表名,并使用逗号隔开,没有使用连接函数,这种方式通常称为隐式的连接(implicit join) 这种在查询中被视为进行交叉连接(cross join)。