Oracle 子查询
1.定义:子查询就是嵌套在主查询里的查询了
2.示例:找出所有工资比Den(FIRST_NAME)高的员工
SELECT FIRST_LAME || ' ' || LAST_NAME FROM EMPLOYEES T0, EMPLOYEES T1
WHERE T0.SALARY > T1.SALARY
AND T1.FIRST_NAME = 'Den'
SELECT FIRST_LAME || ' ' || LAST_NAME FROM EMPLOYEES
WHERE SALARY > ( SELECT SALARY FROM EMPLOYEES WHERE FIRST_NAME = 'Den')
第一句sql只是普通的连接查询,第二句sql就是一个子查询
Oracle 非关联子查询
1.定义:非关联子查询就是指子查询可以脱离主查询独立执行
2.示例:Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导是谁(非关联子查询)
SELECT FIRST_NAME || ' ' || LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = (SELECT MANAGER_ID FROM EMPLOYEES WHERE FIRST_NAME = 'Den' AND LAST_NAME = 'Raphaely');
Oracle 关联子查询
1.定义:关联子查询就是指子查询与主查询之间有条件关联, 不能独自执行
2.示例:Den(FIRST_NAME)、Raphaely(LAST_NAME) 的领导是谁(关联子查询)
SELECT FIRST_NAME || ' ' || LAST_NAME FROM EMPLOYEES T0 WHERE EXISTS (SELECT 1 FROM EMPLOYEES T1 WHERE FIRST_NAME = 'Den' AND LAST_NAME = 'Raphaely' AND T1.MANAGER_ID = T0.EMPLOYEE_ID);