在含有子查询的SQL语句中,尤其要减少对表的查询。
点击(此处)折叠或打开
- --查看职位和7900号员工相同, 部门和7844号员工相同的员工的编号、姓名、职位、薪水、部门编号
- --表扫描的次数为3次(不建议)
- SELECT empno, ename, job, sal, deptno FROM emp
- WHERE job = (SELECT job FROM emp WHERE empno = 7900)
- AND deptno = (SELECT deptno FROM emp WHERE empno = 7900);
![](http://img.blog.itpub.net/blog/attachment/201401/15/17013648_1389751700rQBn.png?x-oss-process=style/bb)
点击(此处)折叠或打开
- --表扫描的次数降为2次(建议)
- SELECT empno, ename, job, sal, deptno FROM emp
- WHERE (job, deptno) = (SELECT job, deptno FROM emp WHERE empno = 7900);
![](http://img.blog.itpub.net/blog/attachment/201401/15/17013648_138975178672Ui.png?x-oss-process=style/bb)
点击( 此处)折叠或打开
- --修改emp1中员工号为7788的员工的职位和工资与员工号为7369的员工一致
- --低效的写法(扫描emp1表3次)
- UPDATE emp1 SET job = (SELECT job FROM emp1 WHERE empno = 7369),
- sal = (SELECT sal FROM emp1 WHERE empno = 7369)
- WHERE empno = 7788;
![](http://img.blog.itpub.net/blog/attachment/201401/15/17013648_1389751854tPCz.png?x-oss-process=style/bb)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17013648/viewspace-1070725/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17013648/viewspace-1070725/