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

Editor to share with you what the MySQL sub-query refers to. I hope you will gain a lot after reading this article. Let's discuss it together.

The subquery of MySql is an important part of multi-table query, which is often used with join query and is the basis of multi-table query. The following article will take you to understand the inquiry for a while, hoping to be helpful to you.

What is a subquery?

Sub-query, also known as internal query. When a query is a condition of another query, it is called a subquery. Subqueries can use several simple commands to construct powerful compound commands. Subqueries are most commonly used in the WHERE clause of the SELECT-SQL command.

A subquery is a SELECT statement that is nested in a SELECT, SELECT... INTO statement, INSERT... The INTO statement, DELETE statement, or UPDATE statement may be nested in another subquery.

Second, subquery classification

Subqueries are divided into the following categories:

1. Scalar quantum query: returns a scalar with a single value, in the simplest form.

2. Column subquery: the returned result set is N rows and one column.

3. Row subquery: the returned result set is a row of N columns.

4. Table subquery: the returned result set is N rows and N columns.

Operators that can be used: = >

=,

=

=

= ANY (SELECT S2 FROM table2) SELECT S1 FROM table1 WHERE S1 > ALL (SELECT S2 FROM table2)

NOT IN is an alias for ALL, and they are the same.

Special circumstances:

If table2 is an empty table, the result after ALL is TRUE

If the subquery returns a result with blank rows, such as (0 ALL null 1), even though S1 is larger than the returned result, the result after SQL is null.

Note: in the case of an empty table2 table, the following statement returns NULL:

SELECT S1 FROM table1 WHERE S1 > (SELECT S2 FROM table2) SELECT S1 FROM table1 WHERE S1 > ALL (SELECT MAX (S1) FROM table2)

3. MySQL row subquery:

The result set returned by a subquery is a row of N columns, and the result of the subquery is usually the result set returned by querying a row of data in the table.

Example:

SELECT * FROM table1 WHERE (1Magazine 2) = (SELECT column1, column2 FROM table2) / / Note: (1Magazine 2) is equivalent to row (1Magazine 2) SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)

4. Subquery of MySQL table:

The result set returned by a subquery is a table data with N rows and N columns.

Example:

SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog) after reading this article, I believe you have a certain understanding of what MySQL sub-query refers to, want to know more about it, welcome to follow the industry information channel, thank you for reading!

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.