orace 课堂笔记3

--迪卡尔集（交叉联接）
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)
/