Oracle 子查询学习

Oracle 子查询

1.定义:子查询就是嵌套在主查询里的查询了
2.示例:找出所有工资比 Den(FIRST_NAME) 高的员工

1
2
3
4
5
6
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) 的领导是谁(非关联子查询

1
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) 的领导是谁(关联子查询

1
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);
查看评论