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

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

In mysql, another table expression can be called in a table representation. This table expression is called a subquery statement, also known as subselect or embedded selection. The result of the subquery is passed to the table that calls it to continue processing.

Subquery classification

1. Classify by returned result set

According to the different result sets, subqueries are divided into four types: table subquery, row subquery, column subquery and scalar quantum query.

Table subquery: the returned result set is a collection of rows, N rows and N columns (N > = 1). Table subqueries are often used in the FROM clause of the parent query.

Row subquery: the returned result set is a collection of columns, a row of N columns (N > = 1). Row subqueries can be used in the FROM clause and WHERE clause of a Fu query.

Column subquery: the returned result set is a collection of rows, N rows and one column (N > = 1).

Scalar quantum query: the returned result set is a scalar set, one row and one column, that is, a scalar value. Anywhere you can specify a scalar expression, you can use a scalar quantum query.

By definition, each scalar quantum query is also a row subquery and a column subquery, and vice versa; each row subquery and column subquery is also a table subquery, and vice versa.

2. According to the method of calling the returned result

According to the method of calling the returned result set, the subquery can be divided into where subquery, from subquery and arguments subquery.

Where subquery: (use the results of the inner query as a comparison condition for the outer query)

Definition: where type subquery takes the results of the inner query as the condition of the outer query.

From subquery: (provide the query results of the inner layer for the outer layer to query again)

Definition: a from subquery treats the results of a subquery (a table in memory) as a temporary table, and then processes it.

Exists subquery: (take the outer query result to the inner layer to see whether the inner layer query is valid)

Definition: subquery is to loop the outer table and then query the inner table. It's pretty much like in (), but there's a difference. It mainly depends on the degree of size difference between the two tables. Use exists (inner index) if the subquery table is large, and in (outer index) if the subquery table is small.

Use the principle of subquery

1. A subquery must be placed in parentheses.

two。 Place the subquery to the right of the comparison condition to increase readability.

The subquery does not contain an ORDER BY clause. Only one ORDER BY clause can be used for a SELECT statement, and if specified, it must be placed at the end of the main SELECT statement.

3. Two comparison conditions can be used in subqueries: single-line operators (>, =, > =, select playerno from players where (sex, town) = (select sex, town from players where playerno = 100)

Description: the result of a subquery is a row with two values: ('Maurethecontrol stratford`). This value is compared to a line expression (sex,town).

An example of 5-scale quantum query

Almost anywhere you can specify a scalar expression, you can use a scalar quantum query.

For example, get the number of a player who was born in the same year as player 27.

Mysql > select playerno from players where year (birth_date) = (select year (birth_date) from players where playerno = 27) and playerno 27

The above statement is equivalent to:

Mysql > select playerno from players where year (birth_date) = 1964 and playerno 27

6-column subquery instance

Because the result set returned by the column subquery is N rows and one column, you cannot directly use = >

= select playerno, name, town from players where playerno in (select playerno from players where sex ='F')

Example 2 (any): get the number, date and city of residence of all players who are at least younger than another player in the same city.

Mysql > select playerno, birth_date, town from players as p1 where birth_date > any (select birth_date from players as p2 where p1.town = p2.town)

Example 3 (all): get the number, name and birthday of the oldest player. (that is, a player whose date of birth is less than or equal to all other players)

Mysql > select playerno, name, birth_date from players where birth_date select name, initials from players where exists (select * from penalties where playerno = players.playerno)

Example 2 (not exists): get the names and initials of players who have never been fined.

Mysql > select name, initials from players where not exists (select * from penalties where playerno = players.playerno)

This is the end of the case study on MySQL sub-condition query. I hope the above content can be helpful to everyone and 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.