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

This article mainly explains "mysql nested query statement example analysis". The explanation content in this article is simple and clear, easy to learn and understand. Please follow the idea of Xiaobian and go deep into it slowly to study and learn "mysql nested query statement example analysis" together!

In mysql, nested queries represent queries in which a SELECT statement is nested within the WHERE clause or HAVING clause of another SELECT statement, also known as subqueries, with the syntax SELECT statement WHERE condition.

Operating environment of this tutorial: Windows 10 system, mysql version 8.0.22, Dell G3 computer.

What is a mysql nested query statement?

nested queries

Definition: A select-from-where block of an inner query statement can be nested within the where clause of another outer query block, where the outer query is also called the parent query, the main query. Inner queries are also called subqueries, from queries. Subqueries generally do not use an 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.

Nested queries work like this: inner queries are processed first, from the inside out, outer queries use the results of inner queries Nested queries can be used not only for parent queries select statements. It can also be used in insert, update, delete statements, or other subqueries.

1. Composition of subqueries

Standard select queries that contain standard select list components.

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

Optional where clause.

Optional group by clause.

5. Optional having clause.

II. Syntax rules for subqueries

Select queries for subqueries are always enclosed in parentheses.

Cannot include compute or for.browse clauses.

If you specify a top clause at the same time, you may include only an order by clause.

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

A subquery can be used anywhere an expression can be used, as long as it returns a single value.

If a table appears only in a subquery and not in an external query, then the columns of that table cannot be included in the output.

Subqueries that return a value

When there is only one return value of the subquery, you can use comparison operators such as =,>=,( select agefrom personwhere name ='Zhang San');

3.2 like nested queries

select count(distinct(Tname)) --Query the number of teachers with last name "Zhang" from Teachers where Tname like 'Zhang %'; 4. Subquery that returns a set of values

If the subquery returns more than one value, but a collection, you cannot use the comparison operator directly. You can insert ANY, SOME, or ALL between the comparison operator and the subquery. The equivalence relation can be represented by the IN operator.

4.1 in nested queries

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 meet the criteria in the in list.

select namefrom personwhere countryid in ( select countryid from countrywhere countryname ='China ');

4.2 some nested queries

Some logical operators in sql that result in True if some of the values in a series of comparisons are True. Some of the syntax is:

select name from personwhere countryid = some ( select countryid from country --Compare the equal sign with the following query values, and if it is equal to one of them, return where countryname = 'China');

4.3 all nested queries

All is a logical operator in sql. Well, if a series of comparisons are true, then the result can be true.

select name from personwhere countryid > all ( select countryid from country --this result is True only when countryid is greater than all ids returned below, and this result returns where countryname = 'China');

4.4 exists nested queries

exists is a logical operator in sql. True if the subquery returns a result set. exists stands for "exists," which finds only those records that satisfy the condition. Once you find the first match, you stop looking.

exists subquery

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

exists means whether the subquery has a result set returned.

SELECT * FROM PersonWHERE exists ( SELECT 1); --SELECT 0 SELECT NULL returns the same result because all three subqueries return result sets, so always True,SELECT * FROM Person executes as usual

However, if there is no result set returned because of the condition added to the subquery, the main statement is not executed:

SELECT * FROM PersonWHERE exists ( SELECT * FROM PersonWHERE Person_Id = 100); --If there is no record of Person_Id, the subquery has no result set to return, and the main statement is not executed.

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

Select a.Sno from (select Sno,score from SC where Sno='001') a,(select Sno, score from SC where Cno=' 002') bWhere a.score>b.score and a.Sno=b.Sno; Thank you for reading, the above is the content of "mysql nested query statement instance analysis", after learning this article, I believe everyone has a deeper understanding of mysql nested query statement instance analysis, and the specific use situation still needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

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.