子查询
-- 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');