Usage of EXISTS in SQL

For example, there is a query in the Northwind database as
SELECT c.CustomerId,CompanyName FROM Customers c
WHERE EXISTS(
SELECT OrderID FROM Orders o WHERE o.CustomerID=c.CustomerID)
This How does EXISTS inside work? The subquery returns the OrderId field, but the external query is looking for the CustomerID and CompanyName fields. These two fields are definitely not in the OrderID. How is this matched?
EXISTS is used to check whether the subquery will return at least one row of data. The subquery does not actually return any data, but returns a value of True or False
EXISTS specifies a subquery to detect the existence of a row.
Syntax: EXISTS subquery
Parameters: subquery is a restricted SELECT statement (COMPUTE clause and INTO keyword are not allowed).
Result type: Boolean Returns TRUE if the subquery contains rows, FLASE otherwise.

Example Table A: TableIn	Example Table B: TableEx

(1). Using NULL in the subquery still returns the result set

select * from TableIn where exists(select null)
等同于： select * from TableIn

(2). Compare queries using EXISTS and IN . Notice that both queries return the same results.

select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME in(select BNAME from TableEx)

(3). Compare queries using EXISTS and = ANY. Notice that both queries return the same results.

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 does the exact opposite of EXISTS. If the subquery returns no rows, the WHERE clause in NOT EXISTS is satisfied.

Conclusion:
The return value of the EXISTS (including NOT EXISTS) clause is a BOOL value. There is a subquery statement (SELECT ... FROM...) inside EXISTS, which I call the inner query statement of EXIST. The query statement within it returns a result set. The EXISTS clause returns a Boolean value based on whether the result set of the query statement within it is empty or non-empty.

A popular way to understand it is: Substitute each row of the outer query table into the inner query as a test. If the result returned by the inner query is a non-null value, the EXISTS clause returns TRUE, and this row can As the result row of the outer query, otherwise it cannot be used as the result.

The analyzer will first look at the first word of the statement. When it finds that the first word is the SELECT keyword, it will jump to the FROM keyword, then find the table name through the FROM keyword and put The table is loaded into memory. The next step is to look for the WHERE keyword. If it cannot be found, it will return to SELECT to find the field analysis. If WHERE is found, it will analyze the conditions in it. After completion, it will return to SELECT to analyze the field. Finally, a virtual table is formed.
What follows the WHERE keyword is the conditional expression. After the conditional expression is calculated, there will be a return value, which is non-zero or 0. Non-zero means true (true), and 0 means false (false). In the same way, the condition after WHERE also has a return value, true or false, to determine whether to execute SELECT next.
The analyzer first finds the keyword SELECT, then jumps to the FROM keyword to import the STUDENT table into the memory, and finds the first record through the pointer, and then finds the WHERE keyword to calculate its conditional expression. If it is true, then put this Records are loaded into a virtual table, and the pointer points to the next record. If false, the pointer points directly to the next record without performing other operations. Always retrieve the entire table and return the retrieved virtual table to the user. EXISTS is part of a conditional expression, which also has a return value (true or false).

Before inserting a record, you need to check whether the record already exists. The insertion operation will only be performed when the record does not exist. You can prevent the insertion of duplicate records by using the EXISTS conditional statement.
INSERT INTO TableIn (ANAME,ASEX)
SELECT top 1 '张三', '男' FROM TableIn
WHERE not exists (select * from TableIn where TableIn.AID = 7)

EXISTS and IN use efficiency issues, usually using exists is more efficient than in, because IN does not use indexes, but the specific use depends on the actual situation:
IN is suitable for situations where the outer table is large and the inner table is small; EXISTS is suitable for situations where the appearance is small but the interior is large.

The difference between in, not in, exists and not exists:

Let’s talk about the difference between in and exists first :
exists: Exists, usually followed by a subquery. When the subquery returns the number of rows, exists returns true.
select * from class where exists (select'x"form stu where stu.cid=class.cid)
When in and exists are compared in terms of query efficiency, the efficiency of in query is faster than the query efficiency of exists
The subquery after exists(xxxxx) is called a correlated subquery. It does not return the value of the list.
It just returns a true or false result (this is why select 'x' is used in the subquery Of course the reason can also be

select anything) That is, it only cares about whether the data in brackets can be found and whether such a record exists.
The operation mode is to run the main query first and then query the corresponding results in the subquery. If it exists, if true is returned, the output will be

, otherwise If false is returned, there will be no output, and then the subquery will be queried based on each row in the main query.

The execution sequence is as follows:
1. Execute first An external query
2. Execute a subquery for each row in the external query, and each time the subquery is executed, it will reference the date in the external query

The value of the previous row.
3. Use the results of the subquery to determine the result set of the outer query.
If the external query returns 100 rows, SQL will execute the query 101 times, once for the external query, and then for each row returned by the external query

## A subquery.

in: Contains Query boys with the same age as all girls
select * from stu where sex='male' and age in(select age from stu where sex='female')
The subquery after in() returns the result set. In other words, the execution order is different from exists(). The subquery first generates the result set,
and then the main The query then goes to the result set to find a list of fields that meet the requirements. The output that meets the requirements will not be output.

not in and not The difference between exists: not in Use not in only when the field after the select keyword in the subquery has a not null constraint or has such a hint. In addition, if the table in the main query is large, the table in the subquery is small but If there are many records, you should use not in,
For example: query those classes where there are no students,
select * from class where cid not in(select distinct cid from stu)
When there is a null value for cid in the table , not in does not process null values
Solution: select * from class

where cid not in

##(select distinct cid from stu where cid is not null)

#not in execution sequence is: it is a query that records one record in the table (query each record) meets the requirements If the result set is not met, the result set will be returned. If it does not match, the next record will be queried until all records in the table are queried. In other words, in order to prove that it cannot be found, we can only prove it by querying all records. Indexes are not used.
not exists: If there are few records in the main query table and many records in the subquery table, and there are indexes. For example: To query those classes where there are no students,
select * from class2

where not exists

(select * from stu1 where 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）简介

Mysql常用基准测试工具

Mysql函数 的相关讲解

The above is the detailed content of Usage of EXISTS in SQL. For more information, please follow other related articles on the PHP Chinese website!