--迪卡尔集(交叉联接) SELECT EMPNO,ENAME,JOB,DNAME FROM EMP,DEPT --等值联接 SELECT EMPNO,ENAME,JOB,DNAME FROM EMP,DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO -- SELECT EMPNO,ENAME,JOB,DNAME FROM EMP,DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO AND JOB='CLERK' / --带有条件的等值联接 SELECT EMPNO,ENAME,JOB,DEPT.DEPTNO DEPTNO,DNAME FROM EMP,DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO AND JOB='CLERK' / -- 使用表别名的等值联接 SELECT EMPNO,ENAME,JOB,d.DEPTNO DEPTNO,DNAME FROM EMP e,DEPT d WHERE d.DEPTNO=e.DEPTNO AND JOB='CLERK' /多表联接 SELECT * FROM customer,ord,item where customer.custid=ord.custid AND ord.ordid=item.ordid; --非等值联接 SELECT EMPNO,ENAME,JOB,SAL,GRADE,LOSAL,HISAL FROM EMP,SALGRADE WHERE SAL>LOSAL AND SAL<=HISAL / --右外联接 SELECT EMPNO,ENAME,JOB,DNAME FROM EMP,DEPT WHERE DEPT.deptno=emp.deptno(+) / -- SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,M.EMPNO,M.ENAME FROM EMP E,EMP M WHERE E.MGR=M.EMPNO / --使用JOIN谓词的交叉联接 SELECT * FROM EMP CROSS JOIN DEPT; -- SELECT * FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO ; --使用JOIN谓词的等值联接 SELECT * FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO WHERE JOB='CLERK'; --使用JOIN谓词的等值联接 SELECT * FROM EMP JOIN DEPT USING(DEPTNO) --自然联接 SELECT * FROM EMP NATURL JOIN DEPT -- SELECT * FROM EMP NATURL JOIN DEPT USING(DEPTNO) --左外联接 SELECT * FROM EMP LEFT OUTER JOIN DEPT USING(DEPTNO) --右外联接 SELECT * FROM EMP RIGHT OUTER JOIN DEPT USING(DEPTNO) --全外联接 SELECT * FROM EMP FULL OUTER JOIN DEPT USING(DEPTNO) --并集 SELECT EMPNO,ENAME FROM EMP UNION ALL SELECT DEPTNO,DNAME FROM DEPT; --去掉重复值的并集 SELECT EMPNO,ENAME FROM EMP UNION ALL SELECT EMPNO,ENAME FROM EMP WHERE JOB='CLERK'; --交集 SELECT EMPNO,ENAME FROM EMP INTERSECT SELECT EMPNO,ENAME FROM EMP WHERE JOB='CLERK' / --差集 SELECT EMPNO,ENAME FROM EMP MINUS SELECT EMPNO,ENAME FROM EMP WHERE JOB='CLERK' / --子查询 SELECT DNAME FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE SAL>2000) / |