Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to use EXISTS in SQL. It has certain reference value, and friends who need it can refer to it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

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)

How does the EXISTS work here? The subquery returns the OrderId field, but the outside query is looking for the CustomerID and CompanyName fields. These two fields are definitely not in the OrderID. How do they match?

EXISTS is used to check whether a subquery returns at least one row of data, which does not actually return any data, but returns a value of True or False

EXISTS specifies a subquery to detect the existence of rows.

Syntax: EXISTS subquery

Parameter: 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.

Sample table A:TableIn sample table B:TableEx

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

Select * from TableIn where exists (select null) is equivalent to: select * from TableIn

(2) Compare queries that use 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)

(III)。 Compare queries that use 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)

The effect of NOT EXISTS is opposite to that of EXISTS. If the subquery does not return 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...) inside EXISTS. FROM...), which I call the inner query statement of EXIST. The query statement in it returns a result set. The EXISTS clause returns a Boolean value based on whether the result set of the query statement in it is empty or not.

A popular understanding is that every row of the outer query table is substituted into the inner query as a test, and if the result returned by the inner query takes a non-null value, the EXISTS clause returns TRUE, which can be used as the result row of the outer query, otherwise it cannot be used as the result.

The parser looks at the first word of the statement first, and when it finds out that the first word is the SELECT keyword, it jumps to the FROM keyword, then finds the table name through the FROM keyword and loads the table into memory. The next step is to find the WHERE keyword. If you can't find it, go back to SELECT to find the field parsing. If you find WHERE, analyze the conditions in it, and then return to the SELECT analysis field after completion. Finally, form a virtual table that we want.

After the WHERE keyword is a conditional expression. When the conditional expression is evaluated, there is a return value, that is, non-0 or 0, non-0 is true, and 0 is false. Similarly, the condition after WHERE also has a return value, true or false, to determine whether or not to execute SELECT next.

The parser first finds the keyword SELECT, then jumps to the FROM keyword to import the STUDENT table into memory, finds the first record through the pointer, and then finds the WHERE keyword to calculate its conditional expression. If true, the record is loaded into a virtual table, and the pointer points to the next record. If it is false, then the pointer points directly to the next record without doing anything else. The entire table has been retrieved and the retrieved virtual table is returned to the user. EXISTS, which is part of a conditional expression, also has a return value (true or false).

Before inserting a record, you need to check whether the record already exists, perform the insert operation only if the record does not exist, and you can prevent duplicate records from being inserted by using EXISTS conditionals.

INSERT INTO TableIn (ANAME,ASEX)

SELECT top 1 'Zhang San', 'male' FROM TableIn

WHERE not exists (select * from TableIn where TableIn.AID = 7)

With regard to the efficiency of the use of EXISTS and IN, exists is generally more efficient than in, because IN does not index, but depends on the actual situation:

IN is suitable for situations where the appearance is large and the inner table is small; EXISTS is suitable for situations where the appearance is small and the inner table is large.

Differences between in, not in, exists, and not exists:

Let's first talk about the difference between in and exists:

Exists: yes, 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 query efficiency, the query efficiency of in is faster than that of exists.

The subquery that follows exists (xxxxx) is called a related subquery, and it does not return the value of the list.

Just return a result of ture or false (which is why it is select'x' in the subquery, of course.

Select anything) that is, it only cares about whether the data in parentheses can be found, and whether there is such a record.

Its operation mode is to run the main query once and then remove the results corresponding to the query in the subquery. If it exists, if it returns ture, it will be input.

Output, otherwise return false will not output, and then according to each row in the main query to the subquery to query.

The order of execution is as follows:

1. First execute an external query

two。 The subquery is executed once for each row in the external query, and each time the subquery is executed, it refers to the

The value of the forward line.

3. Use the results of the subquery to determine the result set of the external query.

If the external query returns 100 rows, SQL will execute 101 queries, once, and then return for the external query

Executes a subquery once for each row of the

In: includ

Inquire about boys of 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 not the same as exists (). Subqueries generate result sets first

Then the main query goes to the result set to find the list of fields that meet the requirements. Meet the requirements of the output, otherwise not output.

The difference between not in and not exists:

Not in only uses not in when the field after the select keyword in the subquery has a not null constraint or this implication. In addition, if the table in the main query is large and the table in the subquery is small but has 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 are null values in cid in the table, not in does not handle null values.

Solution: select * from class

Where cid not in

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

The execution order of not in is: the query that records one record in the table (query each record) returns the result set if it meets the requirements, and if it does not meet the requirements, it continues to query the next record until the records in the table are queried. In other words, in order to prove that it can not be found, it can only be proved by querying all the records. The index is not used.

Not exists: if there are fewer records in the main query table, there are more records in the sub-query table and there are indexes.

For example, query those classes where there are no students.

Select * from class2

Where not exists

(select * from stu1 where stu1.cid = class2.cid)

The execution order of not exists is: query in the table, query according to the index, return true if it exists, return false if it does not exist, and not query every record.

The reason for using not exists rather than not in is that the efficiency of not exists query is much higher than that of not in query.

Example:

Examples of how to use exists,not exists can be referenced by friends who need it.

Student table: create table student (id number (8) primary key, name varchar2 (10), deptment number (8)) elective schedule: 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)) course schedule: create table COURSE (ID NUMBER (8) not null, C_NAME VARCHAR2 (20) C_NO VARCHAR2 (10)) data from the student table: ID NAME DEPTMENT_ID- 1 echo 1000 2 spring 2000 3 smith 1000 4 liter Data in 2000course table: ID C_NAME Cellular no-1 database data1 2 Mathematical month2 3 English english2select Data of course table: ID STUDENT_ID COURSE_ID- 1 1 1 21 2 3 1 3 4 21 5 2 2 6 3 21. Inquire about the students who have taken all the courses id, name: (that is, this student does not have a course he did not take. ) Analysis: if a course is not selected, (1) null exists in select * from select_course sc where sc.student_id=ts.idand sc.course_id=c.id at this time, which means that (2) some records do not exist in the query result of select * from course c (1 query), and the query result returns unselected courses. In this case, the not exists after select * from t_student ts determines that the result is false and does not execute the query. 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 10002. Query students who have not selected all courses, that is, students who have not selected all of them. (there is a student who did not take at least one course), analysis: as long as one door is not selected, that is, select * from select_course sc where student_id=t_student.id and course_id=course.id has an empty one, that is, not exists null is true, then select * from course has the query result (id is the course.id in the subquery), so the select id,name from t_student will execute the query (id is the t_student.id in the subquery). 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 liter3. Inquire about the students who have not taken a single course. (there is no such student who takes at least one course.) Analysis: if he takes a select * from course result set that is not empty, not exists determines that false;select id,name from t_student does not execute the query. 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 liter4. Inquire about students who have taken at least one course. 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 Thank you for reading this article carefully. I hope it will be helpful for everyone to share how to use EXISTS in SQL. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.