Oracle Study之---Oracle IN和NOT IN的使用
創(chuàng)新互聯(lián)服務項目包括興安盟網(wǎng)站建設、興安盟網(wǎng)站制作、興安盟網(wǎng)頁制作以及興安盟網(wǎng)絡營銷策劃等。多年來,我們專注于互聯(lián)網(wǎng)行業(yè),利用自身積累的技術(shù)優(yōu)勢、行業(yè)經(jīng)驗、深度合作伙伴關(guān)系等,向廣大中小型企業(yè)、政府機構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案,興安盟網(wǎng)站推廣取得了明顯的社會效益與經(jīng)濟效益。目前,我們服務的客戶以成都為中心已經(jīng)輻射到興安盟省份的部分城市,未來相信會繼續(xù)擴大服務區(qū)域并繼續(xù)獲得客戶的支持與信任!
NOT IN 與 IN 的區(qū)別:
------------------------------------------------------------------------------------------------------
not In 相當于 <> all,如果 Not In 后面跟的是子查詢的話,子查詢中只要包含一個 null 的返回值,則會造成
整個 Not in 字句返回空值,結(jié)果就是查詢不會返回任何結(jié)果。
而 in 相當于 =any 的意思,可以有效處理子查詢中返回空值的情況,返回正確的結(jié)果。
------------------------------------------------------------------------------------------------------
NOT IN示例:
--該例子想要返回沒有下屬的職員的姓名,如果子查詢中有空值返回的話,則整個查詢將沒有結(jié)果返回
11:20:02 SYS@ test3 >conn scott/tiger Connected. 11:21:18 SCOTT@ test3 >select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 14 rows selected. 11:20:11 SCOTT@ test3 >select empno from emp 11:20:21 2 where empno NOT IN (select mgr from emp); no rows selected
說明:
Null Values in a Subquery
The SQL statement in the slide attempts to display all the employees who do not have any
subordinates. Logically, this SQL statement should have returned 12 rows. However, the SQL
statement does not return any rows. One of the values returned by the inner query is a null value and,
therefore, the entire query returns no rows
The reason is that all conditions that compare a null value result in a null. So whenever null values
are likely to be part of the resultsset of a subquery, do not use the NOT INoperator. The NOT IN
operator is equivalent to <> ALL.
---------------------------------------------------------------------------------------------------------
IN的示例:
Notice that the null value as part of the results set of a subquery is not a problem if you use the IN
operator. The IN operator is equivalent to =ANY. For example, to display the employees who have
subordinates(下屬), use the following SQL statement:
11:20:42 SCOTT@ test3 >select empno from emp 11:21:04 2 where empno in (select mgr from emp); EMPNO ---------- 7566 7698 7782 7788 7839 7902 6 rows selected.
---------------------------------------------------------------------------------------------------------
Alternatively, a WHERE clause can be included in the subquery to display all employees who do not
have any subordinates:
--使用 Not In 的話,要注意除掉子查詢中將要返回的空值
11:27:01 SCOTT@ test3 >select empno from emp 11:27:12 2 where empno NOT IN (select mgr from emp WHERE MGR IS NOT NULL); EMPNO ---------- 7844 7521 7654 7499 7934 7369 7876 7900 8 rows selected.
本文標題:OracleStudy之---OracleIN和NOTIN的使用
標題來源:http://bm7419.com/article14/pssode.html
成都網(wǎng)站建設公司_創(chuàng)新互聯(lián),為您提供用戶體驗、網(wǎng)頁設計公司、外貿(mào)網(wǎng)站建設、企業(yè)建站、標簽優(yōu)化、軟件開發(fā)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)