oracle多表查詢，oracle

oracle多表查詢，oracle

多表查詢基本文法

    笛卡爾積在SQL中的實現方式既是交叉串連(Cross Join)。所有串連方式都會先產生臨時笛卡爾積表，笛卡爾積是關係代數裡的一個概念，表示兩個表中的每一行資料任意組合。

-- 笛卡爾積 select * from emp, dept;

-- 使用公用欄位，去掉笛卡爾積select * from emp, dept where emp.deptno = dept.deptno;

-- 查詢出每個僱員的姓名，工作，僱員的直接上級領導的姓名-- 在emp表中的MGR表示一個僱員的上級領導的編號，如果現在要想查詢一個僱員的上級領導姓名，則需要用emp表與emp表進行自串連操作select e.ename, e.job, s.ename from emp s, emp e where s.empno = e.mgr; -- 查詢出每個僱員的姓名，工作，僱員的直接上級領導的姓名，僱員所在的部門名稱select e.ename, e.job,s.ename mgr_name, d.dname from emp e, emp s, dept d where e.mgr = s.empno and e.deptno = d.deptno;-- 查詢出每個僱員的姓名，工資，部門名稱，工資在公司的等級及其領導的姓名，領導的工資，以及領導所對應的等級SELECT e.ename, e.sal, d.dname,decode(g.grade,5,'第一等工資',4,'第二等工資',3,'第三等工資',2,'第四等工資',1,'第五等工資')e_grade, s.ename mgr_name, s.sal mgr_sal,decode(g2.grade,5,'第一等工資',4,'第二等工資',3,'第三等工資',2,'第四等工資',1,'第五等工資') m_gradeFROM emp e,dept d,salgrade g,emp s,salgrade g2WHERE (e.deptno=d.deptno) AND (e.sal BETWEEN g.losal AND g.hisal)AND (s.empno=e.mgr) AND (s.sal BETWEEN g2.losal AND g2.hisal);

左右串連

-- 利用emp 和 dept 做一個串連查詢，查詢結果包括僱員編號，僱員姓名，部門編號，部門名稱，部門所在位置select e.empno, e.ename, d.deptno, d.dname, d.locfrom emp e,dept dwhere e.deptno = d.deptno;

-- 通過觀察不難發現，在以上結果中部門編號40沒有出現,這是因為在進行普通串連時兩邊都有的才出現在結果中,在emp表中沒有部門40,所以結果中也就沒有40select e.empno, e.ename, d.deptno, d.dname, d.locfrom emp e, dept dwhere e.deptno(+) = d.deptno;

部門40就出現在結果中了.此時我們使用的是右串連。

      (+)在=左邊 表示右串連
      (+)在=右邊 表示左串連

      左串連以左表為基準，右串連以右表為基準

-- 左串連以左表為基準，右串連以右表為基準select e.empno, e.ename, d.deptno, d.dname, d.locfrom emp e, dept dwhere e.deptno = d.deptno(+);

-- 查詢僱員的編號，姓名，及其領導的編號，姓名select e.empno, e.ename, s.empno, s.enamefrom emp e, emp swhere e.mgr = s.empno;

-- 但我們經查詢知道emp表中應該有14條記錄,在以上結果中缺少了KING,因為KING是最高領導，所以他的領導編號為空白，故沒辦法查詢出來.為了顯示出來，我們用左/右串連來實現select e.empno, e.ename, s.empno, s.enamefrom emp e, emp swhere e.mgr = s.empno(+);

-- 左右串連的串連方式不是固定的，具體採用左串連還是右串連取決於基準條件在等式的左邊還是右邊，上面的左串連把基準條件放在右邊，就變為了右串連select e.empno, e.ename, s.empno, s.enamefrom emp e, emp swhere s.empno(+) = e.mgr;

得到的同樣是的結果值，這裡就不重複貼圖了。

SQL:1999SQL定義

文法格式

SELECT table1.column,table2.column

FROM table1 [CROSS JOIN table2]|

[NATURAL JOIN table2]|

[JOIN table2 USING(column_name)]|

[JOIN table2 ON(table1.column_name=table2.column_name)]|

[LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name=table2.column_name)]

-- 交叉串連(CROSS JOIN):迪卡爾積運算select * from emp cross join dept;

select * from emp cross join dept where emp.empno = 7369;

-- 自然串連 NATURAL JOINselect * from emp natural join dept;

-- 根據結果可以發現，自然串連按公用欄位相等進行串連，並且產生的結果會自動消除重複的列select * from emp e, dept dwhere e.deptno = d.deptno;

-- USING(column_name) 用於指定兩個表之間的串連欄位select * from emp join dept using(deptno);

-- ON(tab1.column_name=tab2.column_name) 用於指定兩表的串連條件select * from emp join dept on emp.deptno = dept.deptno;

-- LEFT[OUTER] JOIN 左串連 OUTER可有可無select e.empno, e.ename, d.deptno, d.dname, d.locfrom emp e left join dept don e.deptno = d.deptno;select e.empno, e.ename, d.deptno, d.dname, d.locfrom emp e left outer join dept don e.deptno = d.deptno;

-- RIGHT JOIN 右串連select e.empno, e.ename, d.deptno, d.dname, d.locfrom emp e right join dept don e.deptno = d.deptno;

-- INNER JOIN 取交集create table empbak as select * from emp where emp.empno in (7369, 7499, 7521, 7566, 7654, 7698);select e1.empno, e1.empno, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno from emp e1 inner join empbak e2 on e1.empno = e2.empno;

-- FULL OUTER JOIN 取並集update empbak e set e.empno = 8369 where e.empno = 7369;update empbak e set e.empno = 8499 where e.empno = 7499;update empbak e set e.empno = 8521 where e.empno = 7521;select * from emp e full join empbak s on e.empno = s.empno;

-- 對於沒有匹配的記錄，則會以null做為值select * from emp e full join empbak s on e.empno = s.empnowhere e.empno is not null and s.empno is not null;

-- UNION 取並集 去掉重複記錄select * from empunionselect * from empbak;

-- UNION ALL 取並集 不去除重複記錄select * from empunion allselect * from empbak;

最下面的三條記錄是重複記錄，在上面的union串連中，多餘的三條重複記錄是被去掉的。