Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use exists in mysql. It is very detailed and has a certain reference value. Friends who are interested must finish it!

In mysql, exists is used to check whether a subquery returns at least one row of data, which does not actually return any data, but instead returns true or false with the syntax "SELECT field FROM table WHERE EXISTS (subquery);".

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

Syntax:

SELECT field 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 record R in the external query result set, and query it as a condition in the subquery, 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.

Examples are as follows:

Suppose there are now three tables:

Student: student table with fields sno as student number and sname as student name

Course: course schedule with fields cno as course number and cname as course name

Student_course_relation: course selection schedule, which records which courses students have chosen, in which the field sno is the student number and cno is the course number.

Here are a few examples to illustrate the use of EXISTS and NOT EXISTS and their differences from IN and NOT IN

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

The following three cases return the same data and return all the data from 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 of true or false

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

EXISTS only depends on whether there are records in the subquery and has nothing to do with the specific result set, so in the following example, the select sno in the subquery can also be replaced with select cno or select 1, and the result set is the same.

Enquire about 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). The above is all the content of this article "how to use exists in mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.