|
1.先來說下Oracle外連接語句中對非鏈接條件使用（+）的作用問題 之前問過朋友，當時大腦處于短路狀態(tài)，居然沒想明白作用是啥。先看例子如下： select * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename(+)!='KING';
使用scott賬戶登錄，執(zhí)行上面的sql語句，可以分析出此sql語句的意圖是將部門表和員工表進行左外鏈，找出鏈接中員工名字不為‘KING’的記錄，在emp.ename后面加上（+）后，名字為空的記錄也會列出來，即沒有員工的部門也會列出來，如果不加（+），這樣的記錄就列不出來。 2.上面是使用oracle自己的外聯(lián)結(jié)語法的sql語句，如果使用ANSIsql1992標準，即left join，那么情況會有所變化，（+）不能同時和ANSI標準的join一起使用，那么我想emp.ename后面的（+）應(yīng)該變成 emp.ename is null，（可經(jīng)過試驗，發(fā)現(xiàn)根據(jù)ename字段的類型不同，結(jié)果有所不同，一下列出幾個sql語句，供試驗，待有執(zhí)行環(huán)境后，整理之，本次只整理了varchar的情況) --vacrchar類型
select * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename!='KING';
select * from dept left join emp on(dept.deptno=emp.deptno) where emp.ename!='KING'; select * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename(+)!='KING';
select * from dept left join emp on(dept.deptno=emp.deptno) where emp.ename!='KING' or emp.ename is null; --number類型
select * from dept,emp where dept.deptno=emp.deptno(+) and emp.empno!=7782;
select * from dept left join emp on(dept.deptno=emp.deptno) and emp.empno!=7782; select * from dept,emp where dept.deptno=emp.deptno(+) and emp.empno(+)!=7782;
select * from dept left join emp on(dept.deptno=emp.deptno) and emp.empno!=7782 or emp.empno is null;
--char類型又不一樣 (以上用！='KING',用=‘KING’又將如何？) ---------------------------------------------------------------------------------------------- 分析： 先列出兩張表的數(shù)據(jù)
dept：
| DEPTNO | DNAME | LOC |
|---|
| 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON |
emp:
| DEPTNO | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM |
|---|
| 10 | 7782 | CLARK | MANAGER | 7839 | 1981-6-9 | 2450.00 | | | 10 | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | | 10 | 7934 | MILLER | CLERK | 7782 | 1982-1-23 | 1300.00 | | | 20 | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | | 20 | 7566 | JONES | MANAGER | 7839 | 1981-4-2 | 2975.00 | | | 20 | 7788 | SCOTT | ANALYST | 7566 | 1987-4-19 | 3000.00 | | | 20 | 7876 | ADAMS | CLERK | 7788 | 1987-5-23 | 1100.00 | | | 20 | 7902 | FORD | ANALYST | 7566 | 1981-12-3 | 3000.00 | | | 30 | 7499 | ALLEN | SALESMAN | 7698 | 1981-2-20 | 1600.00 | 300.00 | | 30 | 7521 | WARD | SALESMAN | 7698 | 1981-2-22 | 1250.00 | 500.00 | | 30 | 7654 | MARTIN | SALESMAN | 7698 | 1981-9-28 | 1250.00 | 1400.00 | | 30 | 7698 | BLAKE | MANAGER | 7839 | 1981-5-1 | 2850.00 | | | 30 | 7844 | TURNER | SALESMAN | 7698 | 1981-9-8 | 1500.00 | 0.00 | | 30 | 7900 | JAMES | CLERK | 7698 | 1981-12-3 | 950.00 | |
dept對emp做外連接后的結(jié)果是：
| DEPTNO | DNAME | LOC | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
|---|
| 10 | ACCOUNTING | NEW YORK | 7782 | CLARK | MANAGER | 7839 | 1981-6-9 | 2450.00 | | 10 | | 10 | ACCOUNTING | NEW YORK | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | | 10 | ACCOUNTING | NEW YORK | 7934 | MILLER | CLERK | 7782 | 1982-1-23 | 1300.00 | | 10 | | 20 | RESEARCH | DALLAS | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | | 20 | RESEARCH | DALLAS | 7566 | JONES | MANAGER | 7839 | 1981-4-2 | 2975.00 | | 20 | | 20 | RESEARCH | DALLAS | 7788 | SCOTT | ANALYST | 7566 | 1987-4-19 | 3000.00 | | 20 | | 20 | RESEARCH | DALLAS | 7876 | ADAMS | CLERK | 7788 | 1987-5-23 | 1100.00 | | 20 | | 20 | RESEARCH | DALLAS | 7902 | FORD | ANALYST | 7566 | 1981-12-3 | 3000.00 | | 20 | | 30 | SALES | CHICAGO | 7499 | ALLEN | SALESMAN | 7698 | 1981-2-20 | 1600.00 | 300.00 | 30 | | 30 | SALES | CHICAGO | 7521 | WARD | SALESMAN | 7698 | 1981-2-22 | 1250.00 | 500.00 | 30 | | 30 | SALES | CHICAGO | 7654 | MARTIN | SALESMAN | 7698 | 1981-9-28 | 1250.00 | 1400.00 | 30 | | 30 | SALES | CHICAGO | 7698 | BLAKE | MANAGER | 7839 | 1981-5-1 | 2850.00 | | 30 | | 30 | SALES | CHICAGO | 7844 | TURNER | SALESMAN | 7698 | 1981-9-8 | 1500.00 | 0.00 | 30 | | 30 | SALES | CHICAGO | 7900 | JAMES | CLERK | 7698 | 1981-12-3 | 950.00 | | 30 | | 40 | OPERATIONS | BOSTON | | | | | | | | |
最后一行，編號為40的部門是外連接的效果，emp表對應(yīng)的字段都為空
現(xiàn)在考慮如下情況，如果想要找出所有部門中員工姓名不為King的員工和部門都找出來，并且沒有員工的部門也列出來，那么一般會想到用
select * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename!='KING';
這條sql語句，但是發(fā)現(xiàn)40這個部門并不在結(jié)果中，也就是說，最后一行中雖然emp的ename為空，符合！=‘KING’的條件，但卻沒有作為結(jié)果返回，似乎oracle認為這條記錄不存在，于是想要這條結(jié)果出來，那么必須在條件emp.ename!='KING'處加上一個（+）即
select * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename(+)!='KING'，使得好像是外聯(lián)結(jié)果產(chǎn)生的記錄中對應(yīng)于emp表的ename字段的值是任何一個不為‘KING’的字符串，這樣這條記錄便被算作是結(jié)果列了出來。
同樣，使用ANSI語法的語句
select * from dept left join emp on(dept.deptno=emp.deptno) where emp.ename!='KING'
也沒有達到效果，仍然將外聯(lián)結(jié)產(chǎn)生的記錄排除在外了，如果想要包含該條記錄，就應(yīng)該加上emp.ename is null,即
select * from dept left join emp on(dept.deptno=emp.deptno) where emp.ename!='KING' or emp.ename is null
再來看這樣一個需求，如果我們想看一下每個部門及其助理的信息，并且如果該部門沒有助理的話，把部門信息列出來，助理的信息顯示空，那么我們可能會想到如下sql
select * from dept,emp where dept.deptno=emp.deptno(+) and emp.job='ASSISTENT'
但是結(jié)果是沒有任何記錄，因為外聯(lián)結(jié)結(jié)果中沒有任何記錄符合其員工職位為ASSITENT，如果要達到我們的要求，sql語句應(yīng)該寫為
select * from dept,emp where dept.deptno=emp.deptno(+) and emp.job（+）='ASSISTENT' ，
（+）的作用就好象是使oracle造出了這樣的外聯(lián)結(jié)記錄，部門的員工中有一個的工作職位是ASSISTENT,但因為實際并沒有這樣的記錄，所以這條記錄的emp表的字段都是空。
我們可能希望ANSI格式的SQL語句也能達到這樣的效果，你也許可能想到加上emp.job is null不久可以了么，但是那樣只會列出外連接記錄，即部門40的記錄，部門10，20和30的記錄都不會列出來.
所以需要做如下變通，通過左外連接一個子查詢來實現(xiàn)：
select * from dept left join (select * from emp where ename='ASSISTENT') t on dept.deptno=t.deptno
如上的分析是dept對emp進行左外聯(lián)，外聯(lián)條件也是dept.deptno=emp.deptno(+)，我們注意到（+）是加在右邊的表上，而條件中我們的（+）也是加在右邊表的字段上，而不是常量值上，如emp.job（+）='ASSISTENT' ，上面兩種情況都是對于條件是在右邊的表的情況，如果條件是在左邊的表中呢？加上（+）又是什么效果？
因為左連接在沒有其他任何條件的情況下，會將左邊表中的所有記錄都列出來，實驗發(fā)現(xiàn)，當條件中只有關(guān)于左邊表中的條件時，無論！=還是=的情況，不論加上（+）還是不加，效果都是一樣的，都不會有多余記錄列出。
|
