What is the usage of exists in mysql

In mysql, 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 true or false. The syntax is "SELECT field FROM table WHERE EXISTS (subquery);".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What is the usage of exists in mysql

Syntax:

SELECT 字段 FROM table WHERE EXISTS (subquery);

Parameters:

subquery is A restricted SELECT statement (COMPUTE clause and INTO keyword are not allowed)

Example:

SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id);

EXISTS execution order:

1. First execute an external query , and cache the result set, such as SELECT * FROM A

2. Traverse each row of records R in the external query result set, and substitute it into the subquery as a condition to query, such as SELECT 1 FROM B WHERE B.id = A. id

3. If the subquery returns a result, the EXISTS clause returns TRUE. This row R can be used as the result row of the external query, otherwise it cannot be used as the result

The example is as follows:

Suppose there are three tables now:

student: student table, in which the field sno is the student number, and sname is the student name

course: course table, in which the field cno is the course number , cname is the course name

student_course_relation: course selection table, records which courses students have chosen, there are fields sno is the student number, cno is the course number

Let’s illustrate EXISTS through several examples Usage of NOT EXISTS, and the difference between IN and NOT IN

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

The following three cases will return the same data. All data in the student table:

select * from student; 
select * from student where exists (select 1); 
select * from student where exists (select null);

2. The EXISTS subquery returns a Boolean value true or false

EXISTS is used to check whether the subquery will return at least one row of data. The subquery actually does not return any data, but returns a Boolean value of true or false. EXISTS specifies a subquery to detect the existence of rows.

EXISTS only cares about whether there are records in the subquery and has nothing to do with the specific result set. Therefore, in the following example, select sno in the subquery can also be replaced with select cno or select 1. The result set obtained by the query is the same.

Query all students who have taken course number 3:

select * from student a 
where exists (select sno from student_course_relation b where b.cno=3 and b.sno=a.sno); 
select * from student a 
where exists (select cno from student_course_relation b where b.cno=3 and b.sno=a.sno); 
select * from student a 
where exists (select 1 from student_course_relation b where b.cno=3 and b.sno=a.sno);

Recommended learning: mysql video tutorial

The above is the detailed content of What is the usage of exists in mysql. For more information, please follow other related articles on the PHP Chinese website!