What is mysql nested query statement?

In mysql, a nested query represents a query that nests another SELECT statement in the WHERE clause or HAVING clause of a SELECT statement. It is also called a subquery. The syntax is "SELECT statement WHERE condition ( SELECT statement)".

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

What is mysql nested query statement

Nested query

Definition: An inner query statement (select-from-where) block can be nested within another outer query In the where clause of the layer query block, the outer query is also called the parent query and the main query. Inner queries are also called subqueries, from queries. Subqueries generally do not use the order by clause and can only sort the final query results.

Another definition: A query that nests another SELECT statement in the WHERE clause or HAVING clause of a SELECT statement is called a nested query, also known as a subquery.

The working method of nested query is: process the inner query first, and process it from the inside out. The outer query uses the results of the inner query. Nested query can not only be used for the parent query select statement. It can also be used in insert, update, delete statements or other subqueries.

1. Composition of subquery

1. Standard select query containing standard select list components.

2. A standard from clause containing one or more table or view names.

3. Optional where clause.

4. Optional group by clause.

5. Optional having clause.

2. Syntax rules of subqueries

1. The select query of a subquery is always enclosed in parentheses.

2. Cannot include compute or for.browse clauses.

3. If the top clause is specified at the same time, only the order by clause may be included.

4. Subqueries can be nested up to 32 levels. Some queries may not support 32 levels of nesting.

5. Subquery can be used anywhere an expression can be used, as long as it returns a single value.

6. If a table only appears in the subquery and not in the outer query, the columns of the table cannot be included in the output.

3. Subquery that returns a value

When the subquery returns only one value, you can use comparison operators such as =,, >=, <=, !=, etc. Join rich queries with subqueries. Examples are as follows:

3.1 Simple subquery

select name,age
from person
where age > ( select age
from person
where name = '张三');

3.2 like nested query

select count(distinct(Tname)) --查询姓“张”的老师的个数
from Teacher
where Tname like '张%';

4. Subquery that returns a set of values

If the subquery When the return value is more than one, but a collection, the comparison operator cannot be used directly. You can insert ANY, SOME or ALL between the comparison operator and the subquery. The equivalence relationship can use the IN operator.

4.1 in nested query

The in keyword is used in the where clause to determine whether the query expression is in a list of multiple values. Returns records that satisfy the conditions in the in list.

select name
from person
where countryid in ( select countryid
from country
where countryname = '中国');

4.2 some nested query

some is a logical operation symbol in sql. If in a series of comparisons, some values ​​are True, then the result will be True. The syntax of some is:

select name from person
where countryid = some ( select countryid from country　　　　--用等号和以下查询到的值比较，如果与其中一个相等，就返回
where countryname = '中国');

4.3 all nested query

all is a logical operator in SQL. If a series of comparisons are true, then the result can be true.

select name from person
where countryid > all　( select countryid from country　 --当countryid大于以下返回的所有id，此结果才为True，此结果才返回
where countryname = '中国');

4.4 exists nested query

exists is a logical operation symbol in sql. True if the subquery returns a result set. exists represents the meaning of "existence", and it only finds those records that meet the conditions. Once the first matching record is found, the search stops.

exists Subquery

The subquery is a first select statement, and the compute clause and the into keyword are not allowed.

exists means whether the subquery returns a result set.

SELECT * FROM Person
WHERE exists ( SELECT 1); --SELECT 0 SELECT NULL 返回结果都一样，因为这三个子查询都有结果集返回，因此总是True,SELECT * FROM Person照常执行

But if no result set is returned due to added conditions in the subquery, the main statement will not be executed:

SELECT * FROM Person
WHERE exists ( SELECT * FROM Person
WHERE Person_Id = 100); --如果不存在Person_Id的记录，则子查询没有结果集返回，主语句不执行

5. Subquery that returns the new data table

5.1 Query the student numbers of all students whose grades in the "001" course are higher than those in the "002" course;

Select a.Sno from (select Sno,score from SC where Sno='001') a,
(select Sno,scorefrom SC where Cno='002') b
Where a.score>b.score and a.Sno=b.Sno;

Recommended learning: mysql video tutorial

The above is the detailed content of What is mysql nested query statement?. For more information, please follow other related articles on the PHP Chinese website!