子查询

 

-- 1 工资高于JONES的员工
-- JONES的工资
SELECT sal FROM emp WHERE ename = 'JONES';#单行单列，用于条件
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'JONES');

-- 2 查询与SCOTT同一个部门的员工
-- 找出SCOTT的部门编号
SELECT depno FROM emp WHERE ename = 'SCOTT';#单行单列

SELECT * FROM emp WHERE depno = (SELECT depno FROM emp WHERE ename = 'SCOTT');

-- 3 工资高于30号部门所有人的员工信息
# 30号部门 最高的工资
# 第一种写法
SELECT MAX(sal) FROM emp WHERE depno = 30;#单行单列
SELECT * FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE depno = 30);

# 第二种写法
SELECT sal FROM emp WHERE depno = 30;#单列多行
SELECT * FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE depno = 30);#内部会执行最大值操作


# 4.查询工作和工资与MARTIN（马丁）完全相同的员工信息
SELECT job,sal FROM emp WHERE ename = 'MARTIN';#单行多列

SELECT * FROM emp
WHERE ename != 'MARTIN'
AND (job,sal) IN (SELECT job,sal FROM emp WHERE ename = 'MARTIN');