SQL中EXISTS的用法
- jacklove原創
- 2018-06-14 16:54:552740瀏覽
比如在Northwind數據庫中有一個查詢為
SELECT c.CustomerId,CompanyName FROM Customers c
WHERE EXISTS(
SELECT OrderID FROM Orders o WHERE o.CustomerID=c.CustomerID)
這裡面的EXISTS是如何運作呢？子查詢回傳的是OrderId字段，可是外面的查詢要找的是CustomerID和CompanyName字段，這兩個字段肯定不在OrderID裡面啊，這是如何匹配的呢？
EXISTS用於檢查子查詢是否至少會傳回一行數據，該子查詢實際上並未傳回任何數據，而是傳回值True或False
EXISTS 指定子查詢，偵測 行 的存在。
語法： EXISTS subquery
參數： subquery 是受限的 SELECT 語句 (不允許有 COMPUTE 子句和 INTO 關鍵字)。
結果類型： Boolean 如果子查詢包含行，則傳回 TRUE ，否則傳回 FLASE 。
| 例表A：TableIn | 範例表B：TableEx |
|
 |
（一）. 在子查詢中使用NULL 仍然傳回結果集
select * from TableIn where exists(select null) 等同于： select * from TableIn
（二）.比較使用EXISTS 和IN 的查詢。注意兩個查詢傳回相同的結果。
select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME) select * from TableIn where ANAME in(select BNAME from TableEx)
（三）. 比較使用 EXISTS 和 = ANY 的查詢。注意兩個查詢傳回相同的結果。
select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME) select * from TableIn where ANAME=ANY(select BNAME from TableEx)
NOT EXISTS 的作用與 EXISTS 剛好相反。如果子查詢沒有回傳行，則滿足了 NOT EXISTS 中的 WHERE 子句。
結論：
EXISTS(包括 NOT EXISTS )子句的回傳值是一個BOOL值。 EXISTS內部有一個子查詢語句(SELECT ... FROM...)， 我稱之為EXIST的內查詢語句。其內查詢語句傳回一個結果集。 EXISTS子句根據其內查詢語句的結果集空或非空，傳回一個布林值。
一種通俗的可以理解為：將外查詢表的每一行，代入內查詢作為檢驗，如果內查詢傳回的結果取非空值，則EXISTS子句傳回TRUE，這一行可作為外查詢的結果行，否則不能作為結果。
分析器會先看語句的第一個字，當它發現第一個字是SELECT關鍵字的時候，它會跳到FROM關鍵字，然後透過FROM關鍵字找到表名並把錶裝入內存。接著是找WHERE關鍵字，如果找不到就回到SELECT找欄位解析，如果找到WHERE，則分析其中的條件，完成後再回到SELECT分析欄位。最後形成一張我們要的虛表。
WHERE關鍵字後面的是條件式。條件式計算完成後，會有一個回傳值，即非0或0，非0即為真(true)，0即為假(false)。同理WHERE後面的條件也有一個回傳值，真或假，來確定接下來執不執行SELECT。
分析器先找到關鍵字SELECT，然後跳到FROM關鍵字將STUDENT表匯入內存，並透過指標找到第一筆記錄，接著找到WHERE關鍵字計算它的條件表達式，如果為真那麼把這筆記錄裝到一個虛表當中，指標再指向下一筆記錄。如果為假那麼指標直接指向下一筆記錄，而不進行其它操作。一直檢索完整個表，並把檢索出來的虛擬表回傳給使用者。 EXISTS是條件表達式的一部分，它也有一個回傳值(true或false)。
在插入記錄前，需要檢查這條記錄是否已經存在，只有當記錄不存在時才執行插入操作，可以透過使用 EXISTS 條件句來防止插入重複記錄。
INSERT INTO TableIn (ANAME,ASEX)
SELECT top 1 '張三', '男' FROM TableIn
WHERE not exists (select * from TableIn where TableIn.AID = 7)
EXISTS與IN的使用效率的問題，通常情況下採用exists要比in效率高，因為IN不走索引，但要看實際情況具體使用：
IN適合於外表大而內表小的情況； EXISTS適合於外表小而內表大的情況。
in、not in、exists和not exists的差別：
##先談談in和exists的差別：exists:存在，後面一般都是子查詢，當子查詢回傳行數時，exists回傳true。
select * from class where exists (select'x"form stu where stu.cid=class.cid)
當in和exists在查詢效率上比較時，in查詢的效率快於exists的查詢效率
exists(xxxxx)後面的子查詢被稱做相關子查詢, 他是不返回列表的值的.
只是返回一個ture或false的結果(這也是為什麼子查詢裡是select 'x'的原因當然也可以
select任何東西) 也就是它只在乎括號裡的資料能不能找出來，是否有這樣的記錄。
其運作方式是先執行主查詢一次再去子查詢裡查詢與其對應的結果如果存在，返回ture則輸
#出,反之傳回false則不輸出,再根據主查詢中的每一行去子查詢裡去查詢.
#執行順序如下：
1.先執行一次外部查詢
2.對於外部查詢中的每一行分別執行一次子查詢，而且每次執行子查詢時都會引用外部查詢中當
#前行的值。
3.使用子查詢的結果來決定外部查詢的結果集。
如果外部查詢回傳100行，SQL 就會執行101次查詢，一次執行外部查詢，然後為外部查詢回傳
##的每一行執行一次子查詢。
in：包含查詢和所有女生年齡相同的男生
select * from stu where sex='男' and age in(select age from stu where sex='女')
in()後面的子查詢是傳回結果集的,換句話說執行次序和exists()不一樣.子查詢先產生結果集,
然後主查詢再去結果集裡去找符合要求的欄位清單去.符合要求的輸出,反之則不輸出.
not in和not exists的區別：not in 只有當子查詢中，select 關鍵字後的字段有not null約束或者有這種暗示時用not in,另外如果主查詢中表大，子查詢中的表小但是記錄多，則應使用not in,
例如:查詢那些班級中沒有學生的，
select * from class where cid not in(select distinct cid from stu)
當表中cid存在null值，not in 不對空值進行處理
解決:select * from class
#where cid not in
(select distinct cid from stu where cid is not null)
not in的執行順序是：是在表中一筆記錄一筆記錄的查詢(查詢每筆記錄）符合要求的就返回結果集，不符合的就繼續查詢下一筆記錄，直到把表中的記錄查詢完。也就是說為了證明找不到，所以只能查詢全部記錄才能證明。並沒有用到索引。 not exists：如果主查詢表中記錄少，子查詢表中記錄多，並有索引。
例如:查詢那些班級中沒有學生的，
select * from class2
#where not exists
(select * from stu1 其中 stu1.cid =class2.cid)
not exists的执行顺序是：在表中查询，是根据索引查询的，如果存在就返回true，如果不存在就返回false，不会每条记录都去查询。
之所以要多用not exists，而不用not in，也就是not exists查询的效率远远高与not in查询的效率。
实例：
exists,not exists的使用方法示例，需要的朋友可以参考下。
学生表：create table student ( id number(8) primary key, name varchar2(10),deptment number(8) )
选课表：create table select_course ( ID NUMBER(8) primary key, STUDENT_ID NUMBER(8) foreign key (COURSE_ID) references course(ID), COURSE_ID NUMBER(8) foreign key (STUDENT_ID) references student(ID) )
课程表：create table COURSE ( ID NUMBER(8) not null, C_NAME VARCHAR2(20), C_NO VARCHAR2(10) )
student表的数据： ID NAME DEPTMENT_ID ---------- --------------- ----------- 1 echo 1000 2 spring 2000 3 smith 1000 4 liter 2000
course表的数据： ID C_NAME C_NO ---------- -------------------- -------- 1 数据库 data1 2 数学 month1 3 英语 english1
select_course表的数据： ID STUDENT_ID COURSE_ID ---------- ---------- ---------- 1 1 1 2 1 2 3 1 3 4 2 1 5 2 2 6 3 2
1.查询选修了所有课程的学生id、name:（即这一个学生没有一门课程他没有选的。）
分析：如果有一门课没有选，则此时(1)select * from select_course sc where sc.student_id=ts.id
and sc.course_id=c.id存在null，
这说明(2)select * from course c 的查询结果中确实有记录不存在(1查询中)，查询结果返回没有选的课程，
此时select * from t_student ts 后的not exists 判断结果为false，不执行查询。
SQL> select * from t_student ts where not exists (select * from course c where not exists (select * from select_course sc where sc.student_id=ts.id and sc.course_id=c.id));
ID NAME DEPTMENT_ID ---------- --------------- ----------- 1 echo 1000
2.查询没有选择所有课程的学生，即没有全选的学生。（存在这样的一个学生，他至少有一门课没有选），
分析：只要有一个门没有选，即select * from select_course sc where student_id=t_student.id and course_id
=course.id 有一条为空，即not exists null 为true,此时select * from course有查询结果（id为子查询中的course.id ），
因此select id,name from t_student 将执行查询（id为子查询中t_student.id ）。
SQL> select id,name from t_student where exists
(select * from course where not exists
(select * from select_course sc where student_id=t_student.id and course_id=course.id));
ID NAME
---------- ---------------
2 spring
3 smith
4 liter
3.查询一门课也没有选的学生。（不存这样的一个学生，他至少选修一门课程），
分析：如果他选修了一门select * from course结果集不为空，not exists 判断结果为false;
select id,name from t_student 不执行查询。
SQL> select id,name from t_student where not exists
(select * from course where exists
(select * from select_course sc where student_id=t_student.id and course_id=course.id));
ID NAME
---------- ---------------
4 liter
4.查询至少选修了一门课程的学生。
SQL> select id,name from t_student where exists
(select * from course where exists
(select * from select_course sc where student_id=t_student.id and course_id=course.id));
ID NAME
---------- ---------------
1 echo
2 spring
3 smith本文介绍了SQL中EXISTS的用法 ，更多相关内容请关注php中文网。
相关推荐：
Sql 四大排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）简介
以上是SQL中EXISTS的用法的詳細內容。更多資訊請關注PHP中文網其他相關文章！