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

This article will explain in detail the method of sub-query in mysql. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

In mysql, a subquery refers to nesting a query statement in another query statement, which can be implemented with the WHERE clause in the SELECT, UPDATE and DELETE statements. The syntax format in the WHERE clause is "WHERE (another query statement)".

Sub-query is a common query method in MySQL. Multi-table query can be realized through sub-query. A subquery refers to nesting one query statement in another query statement. Subqueries can be used in SELECT, UPDATE, and DELETE statements, and can be nested at multiple levels. In actual development, subqueries often appear in the WHERE clause.

The syntax format of the subquery in WHERE is as follows:

WHERE (subquery statement)

Where operators can be comparison operators and keywords such as IN, NOT IN, EXISTS, NOT EXISTS, and so on.

1) IN | NOT IN

TRUE is returned when the expression is equal to a value in the result set returned by the subquery, otherwise FALSE; is returned if the keyword NOT is used, the return value is the opposite.

2) EXISTS | NOT EXISTS

It is used to determine whether the result set of the subquery is empty. If the result set of the subquery is not empty, TRUE is returned, otherwise FALSE; is returned. If the keyword NOT is used, the value returned is just the opposite.

Example 1

Use the subquery to query the names of the students learning the Java course in the tb_students_info table and the tb_course table, the SQL statement and the running results are as follows.

Mysql > SELECT name FROM tb_students_info-> WHERE course_id IN (SELECT id FROM tb_course WHERE course_name = 'Java'); +-+ | name | +-+ | Dany | | Henry | +-+ 2 rows in set (sec)

The results show that only Dany and Henry are taking the Java course. The above query process can also be divided into the following two steps, and the implementation effect is the same.

1) first, execute the internal query separately to find out the id,SQL statements in the tb_course table that are Java and the running results are as follows.

Mysql > SELECT id FROM tb_course-> WHERE course_name = 'Java';+----+ | id | +-+ | 1 | +-+ 1 row in set (0.00 sec)

As you can see, the value of the eligible id field is 1.

2) then execute an outer query to query the name of the student whose course_id is equal to 1 in the tb_students_info table. The SQL statement and the run result are as follows.

Mysql > SELECT name FROM tb_students_info-> WHERE course_id IN (1); +-+ | name | +-+ | Dany | | Henry | +-+ 2 rows in set (0.00 sec)

Traditionally, the outer SELECT query is called the parent query, and the query embedded in parentheses is called the subquery (the child query must be placed in parentheses). When MySQL processes the SELECT statement in the example above, the execution process is as follows: execute the child query first, and then execute the parent query.

Example 2

Similar to example 1, the NOT IN keyword is used in the SELECT statement to query the names of students who are not taking the Java course. The SQL statement and the running results are as follows.

Mysql > SELECT name FROM tb_students_info-> WHERE course_id NOT IN (SELECT id FROM tb_course WHERE course_name = 'Java'); +-+ | name | +-+ | Green | | Jane | | Jim | | John | | Lily | | Susan | | Thomas | | Tom | | LiMing | +-+ 9 rows in set (sec)

As you can see, the result of the operation is just the opposite of example 1. The students who do not take the Java course are students other than Dany and Henry.

Example 3

Using the = operator, look up the names of all students taking the Python course in the tb_course table and the tb_students_info table. The SQL statement and run results are as follows.

Mysql > SELECT name FROM tb_students_info-> WHERE course_id = (SELECT id FROM tb_course WHERE course_name = 'Python'); +-+ | name | +-+ | Jane | +-+ 1 row in set (0.00 sec)

The results show that the only students enrolled in the Python course are Jane.

Example 4

Using the operator, look up the names of the students who are not taking the Python course in the tb_course table and the tb_students_info table. The SQL statement and the run results are as follows.

Mysql > SELECT name FROM tb_students_info-> WHERE course_id (SELECT id FROM tb_course WHERE course_name = 'Python'); +-+ | name | +-+ | Dany | | Green | | Henry | | Jim | | John | | Lily | | Susan | | Thomas | | Tom | LiMing | +-+ 10 rows in set (0.00 sec)

As you can see, the result of the operation is just the opposite of example 3. The students who do not take the Python course are students other than Jane.

Example 5

Query whether the id=1 course exists in the tb_course table, and if so, query the records in the tb_students_info table. The SQL statement and the running results are as follows.

Mysql > SELECT * FROM tb_students_info-> WHERE EXISTS (SELECT course_name FROM tb_course WHERE id=1) +-+-+ | id | name | age | sex | height | course_id | +-+-+ | 1 | Dany | 25 | male | 160 | 1 | 2 | Green | 23 | male | 3 | Henry | 23 | female | 185 | 1 | 4 | Jane | 22 | male | 162 | 3 | 5 | Jim | 24 | female | 175 | 2 | 6 | John | 21 | female | 172 | 4 | 7 | Lily | 22 | | male | 165,4 | 8 | Susan | 23 | male | 170,5 | 9 | Thomas | 22 | female | 178,5 | 10 | Tom | 23 | female | 165th | | 11 | LiMing | 22 | male | 180 | 7 | + -+-+ 11 rows in set (0.01 sec)

As you can see from the result, there are records of id=1 in the tb_course table, so the EXISTS expression returns TRUE, and the outer query statement queries the table tb_students_info after receiving the TRUE and returns all the records.

The EXISTS keyword can be used with other query conditions, and conditional expressions are joined with the EXISTS keyword with AND and OR.

Example 6

Query whether the id=1 course exists in the tb_course table, and if so, query out the records in the tb_students_info table where the age field is greater than 24. The SQL statement and running results are as follows.

Mysql > SELECT * FROM tb_students_info-> WHERE age > 24 AND EXISTS (SELECT course_name FROM tb_course WHERE id=1) +-+-+ | id | name | age | sex | height | course_id | +-+-+ | 1 | Dany | 25 | male | 160 | 1 | +-+ 1 row in set (0.01sec)

The results show that a record is queried from the tb_students_info table with a value of 25 in the age field. The inner query statement queries the record from the tb_course table and returns TRUE. The outer query statement starts the query. Query records with age greater than 24 from the tb_students_info table according to the query criteria.

Development

Subqueries can also be done through table joins, but subqueries make SQL statements easier to read and write.

Generally speaking, table joins (inner joins and outer joins, etc.) can be replaced by subqueries, but not necessarily, some subqueries cannot be replaced by table joins. The subquery is more flexible, convenient and diverse, and it is suitable to be used as the filter condition of the query, while the table join is more suitable for viewing the data of the join table.

On the method of subquery in mysql to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.