mysql supports subqueries. Subquery refers to nesting a query statement within another query statement. It is a commonly used query method in MySQL. Multi-table queries can be implemented through subqueries. 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, and the syntax is "WHERE
(subquery)", the subquery must be closed in parentheses.
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
mysql supports subqueries.
Subquery in MySQL
Subquery is a commonly used query method in MySQL. Multi-table query can be realized through subquery. Subquery refers to nesting one query statement within 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 <表达式> <操作符> (子查询)
Among them, the operator can be a comparison operator and keywords such as IN, NOT IN, EXISTS, NOT EXISTS; the subquery must be in Closed in brackets
1) IN | NOT IN
Returns TRUE when the expression is equal to a value in the result set returned by the subquery, otherwise it returns FALSE ; If the keyword NOT is used, the return value is exactly the opposite.
2) EXISTS | NOT EXISTS
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 it is returned FALSE; if keyword NOT is used, the returned value is exactly the opposite.
Example 1
Use subquery to query the names of students studying Java courses in the tb_students_info table and tb_course table
mysql> SELECT name FROM tb_students_info
-> WHERE course_id IN (SELECT id FROM tb_course WHERE course_name = 'Java');The results show that only Dany and Henry are taking Java courses. The above query process can also be divided into the following two steps to achieve the same effect.
1) First execute the inner query separately to find out the ID of the course Java in the tb_course table. The SQL statement and running results are as follows.
mysql> SELECT id FROM tb_course
-> WHERE course_name = 'Java';#As you can see, the value of the id field that meets the conditions is 1.
2) Then execute the outer query to query the names of students whose course_id is equal to 1 in the tb_students_info table. The SQL statements and running results are as follows.
mysql> SELECT name FROM tb_students_info
-> WHERE course_id IN (1);Conventionally, the outer SELECT query is called the parent query, and the query embedded in parentheses is called a subquery (The subquery must be placed in parentheses Inside). When MySQL processes the SELECT statement in the above example, the execution flow is: first execute the subquery, and then execute the parent query.
Example 2
Similar to Example 1, use the NOT IN keyword in the SELECT statement to query the names of students who have not taken Java courses. The SQL statement and 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');It can be seen that the running results are exactly the opposite of Example 1. Students other than Dany and Henry are not taking Java courses.
Example 3
Use the = operator to query the names of all students studying Python courses in the tb_course table and tb_students_info table, SQL statement And the running results are as follows.
mysql> SELECT name FROM tb_students_info
-> WHERE course_id = (SELECT id FROM tb_course WHERE course_name = 'Python');
The results show that the only student studying the Python course is Jane.
Example 4
Use the operator to query the names of students who have not studied Python courses in the tb_course table and tb_students_info table , the SQL statements and running results are as follows.
mysql> SELECT name FROM tb_students_info
-> WHERE course_id <> (SELECT id FROM tb_course WHERE course_name = 'Python');It can be seen that the running result is just the opposite of Example 3. The students other than Jane are not learning Python courses.
Example 5
Query whether there is a course with id=1 in the tb_course table. If it exists, query the records in the tb_students_info table. The SQL statement and running results are as follows .
mysql> SELECT * FROM tb_students_info
-> WHERE EXISTS(SELECT course_name FROM tb_course WHERE id=1);As can be seen from the results, there is a record with id=1 in the tb_course table, so the EXISTS expression returns TRUE, and the outer query statement receives TRUE and then performs the query on the table tb_students_info Query to return all records.
EXISTS 关键字可以和其它查询条件一起使用，条件表达式与 EXISTS 关键字之间用 AND 和 OR 连接。
例 6
查询 tb_course 表中是否存在 id=1 的课程，如果存在，就查询出 tb_students_info 表中 age 字段大于 24 的记录，SQL 语句和运行结果如下。
mysql> SELECT * FROM tb_students_info
-> WHERE age>24 AND EXISTS(SELECT course_name FROM tb_course WHERE id=1);
结果显示，从 tb_students_info 表中查询出了一条记录，这条记录的 age 字段取值为 25。内层查询语句从 tb_course 表中查询到记录，返回 TRUE。外层查询语句开始进行查询。根据查询条件，从 tb_students_info 表中查询 age 大于 24 的记录。
拓展
子查询的功能也可以通过表连接完成，但是子查询会使 SQL 语句更容易阅读和编写。
一般来说，表连接（内连接和外连接等）都可以用子查询替换，但反过来却不一定，有的子查询不能用表连接来替换。子查询比较灵活、方便、形式多样，适合作为查询的筛选条件，而表连接更适合于查看连接表的数据。
【相关推荐：mysql视频教程】
The above is the detailed content of Does mysql not support subqueries?. For more information, please follow other related articles on the PHP Chinese website!