SQL subquery EXISTS and not EXISTS

Content from online:53090467

The data of the main query is placed in the subquery for conditional validation, depending on the validation result (TRUE or FALSE) to determine whether the data results of the main query are retained.

Here's an example of three tables

Let's start with the 3 datasheets that are used:

Student Data Sheet:

Sno School Number	sname	Ssex	Sage
20161181	Altair	Man	20
20161182	Desmond	Man	18
20161183	Ezio	Man	22
20161184	Christina	Woman	19

Course Data Sheet:

CNO Course Number	CNAME Course name
1	C language
2	Data
3	Signal and System
4	Analog Electronic Technology
5	High number

SC Data Sheet:

Sno School Number	CNO Course Number	Grade Results
20161181	1	99
20161182	2	98
20161181	2	97
20161181	3	95
20161184	3	92
20161181	4	90
20161181	5	88
20161183	5	58

EXISTS

exists represents the existence of quantifiers? A subquery with the EXISTS predicate does not return any data, producing only the logical truth "true" or the logical false value "false".

An example 1.1:

Requirements: Inquiries from students who have enrolled in the course "signals and systems"

SELECT s.Sname FROM student sWHERE EXISTS  ‘信号与系统‘)

With the presence of quantifier exists, if the inner query result is non-null, the outer WHERE clause return value is true, otherwise the return value is false.

In this example, the most inner statement is analyzed first:

‘信号与系统‘

• 1

The query criteria for the subquery in this example depend on one of the property values of the outer parent query (in this case, the SNO value of student), and the processing of this correlated subquery is:

Firstly, the first tuple of the outer query (student) table is taken, and the inner query is processed according to the property value (Sno value) related to the inner query, and if the outer layer where return is true, then the sname of the tuple in the outer query is put into the result table;

Then take the next set of tables (student) and repeat the process until the outer (student) table is fully checked.

Query Result table:

Sname
Altair
Christina

Not EXISTS

The opposite of the EXISTS predicate is the NOT EXISTS predicate. When a quantifier not exists is used, if the corresponding query result is empty, then the outer where child statement return value is true, otherwise the false value is returned.

Example 2.1:
Requirements: Query students who do not have an elective course "signals and systems"

SELECT s.Sname FROM student sWHERE NOT EXISTS  ‘信号与系统‘)

After using not exists, if the inner layer query result is non-null, then the corresponding not exists is not established, so the corresponding where statement is not established.

In example 1.1, the corresponding record of Li Yong in accordance with the internal SELECT statement, so return the record data, but the corresponding not exists is not true, where statement is not established, that is not the data we want to query.

Query Result table:

Sname
Desmond
Ezio

Example 2.2 (This is an example of a full quantifier with NOT exists):

Request: Check the name of the student who took all the courses.

SQL statements:

SELECT Sname  FROM Student   WHERE NOT EXISTS  (SELECT * FROM Course WHERE NOT EXISTS       (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno)  );  

This is a more complex SQL statement, two exists and three where.

This SQL statement can be divided into 3 layers, the outermost statement, the most inner statement, and the middle layer statement.

We care about the outermost statement because the data in the result table is the data in the outermost query's table, and we are more concerned with the data in the topmost layer, because the most inner data contains all the judgment statements, which determines the record in the student table is our query.

We analyze from the inside Out:

The first record in the outermost student table is the record of the Altair classmate, then the first record of the course table in the middle layer is the record of the database, then the data is judged (the where statement of the most inner layer), the result returns true, the inner layer not exists is false,
Then continue to judge the next record in the course table, the value of the return not exists is also false, until all the data in the course table is traversed, the value of not exists in the inner layer is always false, so the value of the where statement of the middle tier is always false.
Corresponding to the Altair record of the student, the return value of the middle tier for all records in the course table is false, so the outermost not exists corresponding value is true, the outermost where value is also true, then the Altair corresponding record conforms to the query condition, loaded into the result table.
The next record in the student table is then continued, and all data in the direct student table is traversed.

Here is my own interpretation of this sql:

First take a student record, enter the middle, then take a course record, into the inner layer, at this time student record and course record, as the condition of inner layer judgment, for example, at this time I take the first record is Altair, then I can write in SQL

SELECT * FROM Course WHERE NOT EXISTS       ‘20161181‘ AND Cno=Course.Cno)  )

Here Sno 20161181 is the Altair of the number, the meaning of this SQL is to choose not to be Altair selected courses, if not present, then return false, and the outermost not exists associated with negative positive. The meaning of each cycle is that every student who has been screened does not have a class that has not been chosen, that is, all classes are chosen.

Final query Result:

Sname
Altair

SQL subquery EXISTS and not EXISTS