首页 > 代码库 > 码字定式之SQL(5)

码字定式之SQL(5)

  1. --等值连接
  2. select dname, loc, empno, ename from emp a, dept b where a.deptno=b.deptno and b.deptno=20;
  3. --自连接
  4. select a.empno, a.sal, b.empno, b.ename, b.sal from emp a, emp b where a.mgr=b.empno and a.ename=‘SCOTT‘;
  5. --笛卡尔积
  6. select a||‘+‘||b||‘=‘||(a+b) as "3*3加法表" from (select rownum a from all_objects where rownum<4), (select rownum b from all_objects where rownum<4);
  7. --内连接
  8. select empno, ename, sal, salgrade.grade from emp, salgrade where emp.deptno=10 and emp.sal between salgrade.losal and salgrade.hisal;
  9. select empno, ename, sal, salgrade.grade from emp inner join salgrade on emp.deptno=10 and emp.sal between salgrade.losal and salgrade.hisal;
  10. --反连接
  11. select * from emp where deptno not in (select deptno from dept where loc in(‘NEW YORK‘, ‘DALLAS‘));
  12. select * from dept where deptno not in (select deptno from emp);
  13. --半连接
  14. select * from emp a where exists (select 1 from dept b where loc=‘NEW YORK‘ and a.deptno=b.deptno);
  15. select a.* from emp a, dept b where loc=‘NEW YORK‘ and a.deptno=b.deptno;
  16. select * from dept a where exists (select 1 from emp b where a.deptno=b.deptno and b.sal>2900);
  17. select a.* from dept a , emp b where a.deptno=b.deptno and b.sal>2900;
  18. select distinct a.* from dept a , emp b where a.deptno=b.deptno and b.sal>2900;




来自为知笔记(Wiz)


码字定式之SQL(5)