What exactly does EXISTS in SQL do?

All data tables mentioned in this article are based on the Wang Shan Database System Introduction (4th Edition).

Personally, the EXISTS keyword in SQL is more difficult to understand for beginners, especially for nesting of multiple EXISTS clauses. I will write a small article to simply explain what EXISTS has done.

Let's start with the most basic WHERE clause in SQL.

For example, the following SQL statement:

Obviously, when executing this SQL statement, the DBMS scans each record in the Student table and then filters all records that conform to the sdept = ' is ' condition and puts it in the result set. This means that the WHERE keyword is the function of determining whether the value of the subsequent logical expression is True. If true, the current record (after the SELECT keyword is processed) is placed in the result set, if the value of the logical expression is False.

Next, look at an SQL statement that uses the EXISTS keyword:

The purpose of this SQL statement is to find all the students who have enrolled in course 1th and display their names.

Let's start with whatever the EXISTS keyword does in it, but first look at the expression after the WHERE keyword in the subquery Sno = Student.sno and Cno = ' 1 '.

What's the matter of Sno = Student.sno?

This involves unrelated subqueries and correlated subqueries in SQL.

Our common SQL statement for the tape query is this:

First through the sub-query to get the class name "data Structure" of the course number, and then traverse the SC (elective) table in each of the selected course record, if the current record of the class number is "Data structure" the course number, then the record of the Sno column value into the result set. In the end, we can get all the student numbers that take the "data structure" course.

This type of query executes a subquery first, gets a set (or value), and then takes the collection (or value) as a constant into the parent query's WHERE clause. If you simply execute a subquery, you can also succeed.

This type of query is called "irrelevant subquery."

In most cases, irrelevant subqueries are sufficient, but if there is a query that requires:

Subqueries can be written like this:

So the question comes, huh? What values should be written at the place?

The key question is, what? This constant is not a definite value, but rather the value of the Sno column in each record in the Student table is constantly being placed here, and the average score for that Sno is calculated. What we need is to enter a series of values and then get a series of corresponding outputs.

At this point, we're going to use another nested query called "correlated subqueries." "Correlated subqueries" means that the values in the parent query need to be used in the subquery.

For this query request, we can use the following SQL statement:

It works by scanning every record in the data source (such as the SC table) in the parent query, then substituting the values in the current record for the subquery in the sub-query, then executing the subquery and getting the result (which can be seen as the return value), and then substituting the result into the parent query's condition. Evaluates whether the value of the conditional expression for the parent query is true and, if true, places the record in the current SC table (after SELECT processing) in the result set. If False, it is not put.

In this example, the parent query takes the first record out of the SC table and then Sno the value of the current record's column (such as 95001) into the subquery, finding the average score (such as 80 points) for all courses taken by the student with the number 95001. Then the value of this 80 as Grade >=, if the value of the first record in the SC table is 90, then Grade >= 80, the value of the conditional expression is True, the value of the Cno column in the current record (such as 1) is put into the result set. To. And so on, traversing all records in the SC table, you get the number of each course that exceeds the average student's score for all of his or her courses.

It is also very simple to determine whether a "correlated subquery" is a "correlated subquery" as long as the subquery cannot be executed separately from the parent query.

Now that we know the concept of "correlated subqueries", we can come back to understand the role of the EXISTS keyword. Its function is to determine whether the result set obtained by the subquery is an empty, if not, returns TRUE, or False if it is. EXISTS itself is the meaning of "existence", which, in our understanding, is to return True if there is such a record in the current table that matches the condition, otherwise False.

For convenience, we release this SQL statement again:

In this query, first the first record in the Student table is fetched, and the value of its Sno column (because it is used in the subquery) is taken out, such as 95001, and then the value is put into the subquery. If such a record can be found, then the student number 95001 is enrolled in the 1th course. Because such a record can be found, the result of the subquery is not an empty set, then EXISTS returns TRUE, allowing the value of the Sname column in the first record in the Student table to be placed in the result collection. Then, after traversing all the records in the Student table, you can get the names of all the students who have enrolled in the 1th course.

The opposite of the EXISTS keyword is not EXISTS, which, contrary to EXISTS, returns True when the result of the subquery is an empty set, and returns False instead. The so-called "if not exist".

The following query requirements can only be implemented by the not EXISTS keyword, because the division function in relational algebra is not directly provided in SQL.

This can be accomplished by the following steps:

STEP1: Takes the first tuple in the Student table first to get the value of its Sno column.
STEP2: Then take the first tuple in the Course table and get the value of its Cno column.
STEP3: Iterates through all the records in the SC table (that is, the course selection record), based on the values of Sno and Cno. If the corresponding record is not found in the SC table for a value of Sno and Cno, the corresponding student of the Sno does not take the Cno corresponding course.
STEP4: For a student, after traversing all the records in the Course table (that is, all the courses), there is no other course that he or she does not take, which means that the student has enrolled in all the courses.
STEP5: Puts this student in the result tuple collection.
STEP6: Go back to STEP1 and take the next tuple in Student.
STEP7: Displays all result tuple sets.

Based on these ideas, you can write SQL statements:

Where the first not EXISTS corresponds to STEP4, the second not EXISTS corresponds to STEP3.

Similarly, for similar query requirements

You can use SQL statements:

For query requirements

You can use SQL statements:

What exactly does EXISTS in SQL do?