SQL subqueries and nested queries

Subqueries and nested queries

Tags: it

A nested query is an external query.

A subquery is also a select query inside select that is often referred to as inner query

The following error example:

Select name from [user] where-age <= (select-Age-from [user] where id>1)

The subquery returns more than one value. This is not allowed when subqueries are followed by =,! =, <, <=, >, >=, or when the subquery is used as an expression.

1. Grammar

The syntax of a subquery is as follows:

(SELECT [All | Distinct]<select Item list>

From <table list>

[Where<search Condition>]

[GROUP by <group item list>

[Have <group by Search Conditoon>] ）

2. Grammar rules

(1) Select queries for subqueries are always enclosed in parentheses.

(2) cannot include compute or for browse clauses.

(3) If you specify the TOP clause at the same time, only the ORDER BY clause may be included.

(4) A subquery can nest up to 32 layers, and individual queries may not support 32-tier nesting.

(5) Any place where an expression can be used can use a subquery, as long as it returns a single value.

(6) If a table appears only in a subquery and not in an external query, the columns in the table cannot be included in the output

3. Syntax format

(1) WHERE query expression [NOT] in (subquery).

(2) WHERE query expression comparison operator [any | All] (sub-query).

(3) WHERE [NOT] EXISTS (subquery).

SQL subqueries are typically present as part of a WHERE clause or a HAVING clause. In the WHERE clause, they help select individual records that are rendered in the query results. In the HAVING clause, they moderators select the group of records to render in the query results.
the difference between a subquery and the actual SELECT statement:
In a common usage, a subquery must generate a data field as its query result. This means that a subquery almost always has a selection in its select clause.
The ORDER BY clause cannot be specified in a subquery, and the subquery results are used internally by the query, and are never visible to the user, so sorting them is meaningless.
The field names that are rendered in the subquery may refer to the fields of the table in the main query.
In most implementations, a word query cannot be a union of several different select statements, it allows only one select.

Sub-Query search criteria
* subquery Comparison test = <> < <= > >= (in this type of test, the subquery must produce a value of the appropriate data type, that is, it must produce a record of the query result, which contains only one field for the query result record.) SQL will report an error if the query produces more than one record or multiple fields, which is not meaningful for a long time. If the subquery does not produce a record or produces a null value, the comparison test returns null).
* Sub-query Group member test (in)
* Presence Test (EXISTS)
* Qualification comparison test any of all
Sub-queries and links
Many queries written by subqueries can also be written as multiple table queries or joins.
a subquery in the having query
When a subquery is rendered in the HAVING clause, it works as part of the selection of the group of records executed by the HAVING clause.

Simple nested query

Nested query inner-layer subqueries are typically rendered as part of a search condition in a WHERE or HAVING clause. For example, comparing the value of an expression to a value generated by a subquery, this test is similar to a simple comparison test.

The operators used for subquery comparison tests are: =, <>, <, >, <=, >=. The subquery comparison test compares the value of an expression to a value produced by a subquery and returns a record that compares the result to true.

Nested query with in with in nested query syntax format is: WHERE query expression in (sub-query).

Some subqueries in nested inner layers produce a value, and some subqueries return a column of values, that is, a subquery cannot return a table with rows and columns of data. The reason is that the result of the subquery must fit into the statement for the outer query. When a subquery produces a series of values, it is appropriate to use nested queries with in.

Use student

SELECT *

From student

Where study number in (select study number from grade)

Inner nested nesting with in can also be a list of multiple values.

For example, the query "age" is the student information for "22, 21, 24". The SQL statements are as follows:

Usestudent

select*

Fromstudent

where age in (21,22,24)

In summary, when a null value is stored in a nested subquery, avoid using the not

A nested query with not in is a nested query syntax format in which the WHERE query expression is not in (subquery) is similar to the query process for not in and in.

SELECT *

From course

Where course code not in

(select Course code from grade where course code is not null)

When a subquery has a null value, avoid using not in. Because when the result of a subquery includes a list of NULL values, the null value is treated as an unknown, and there is no record that the query value is not in the list.

Usestudent

select*

Fromstudent

where study No in

(select school number from grade

)

Because the result of the subquery includes a null value, the final query result has only null values. The SQL statements that are queried correctly are as follows:

Usestudent

select*

From student

where study No in

(select school number from Gradewhere, is Notnull)

Nested queries with some select*from[user]where age<some (Selectavg (age) from [user]) SQL supports 3 quantitative comparison predicates: Some, any, and all. They are all judged whether any or all of the return values meet the search requirements. Where the some and any predicates are present, only pay attention to whether there is a return value to meet the search requirements. The two predicates have the same meaning and can be substituted for use.

The some is to compare the column values specified by each row with the results of the subquery, and if that row is true, the row is returned if the condition is met. The nested subquery result of this instance is a numeric value, and the age column value of each record of the "Student" student table is compared to that of the returned row.

Nested query with any

Any is one of the 3 quantitative predicates supported by SQL. And some is completely equivalent, that is, can be used some place can use any.

Use any to resolve the above example. The SQL statements entered in Query Analyzer are as follows:

Usestudent

select*

Fromstudent

where Age <any

(Selectavg (age) from student)

The quantitative predicate in SQL does not support anti-operation, that is, cannot add the NOT keyword before any or some. But you can use the "<>" sign to negate

Nested query with all

The all predicate, like any or some predicate, compares the column value to the subquery result, but it does not require the column value of any result value to be true, but rather requires that the query result of all columns be true, otherwise the row will not be returned.

In addition to the course information for these "course codes", these "course codes" refer to the corresponding "course codes" with a "course score" greater than 90 points. The SQL statements entered in Query Analyzer are as follows:

Usestudent

select*

Fromcourse

Where course code <> all

(select Course Code fromgrade where course results >90)

These quantitative predicates for SQL can all use any operator in 6 comparison operators

Nested query with Exists

The EXISTS predicate only focuses on whether the subquery returns rows. If the subquery returns one or more rows, the predicate is returned as true, otherwise false. exists search conditions do not really use the results of subqueries. It only tests whether the subquery produces any results.

Nested queries with in can also be rewritten with nested queries with exists.

The following example writes a nested query with an in, instead of a nested query written with exists. The SQL statements entered in Query Analyzer are as follows:

Usestudent

select*

Fromstudent

where exists

(select study number from Grade Wherestudent. =grade. Number of studies)

Any column name can be used in a SELECT clause in a EXISTS predicate subquery, or any number of columns, which focus only on whether rows are returned and not on the contents of the rows. Users can specify any column name or use only one asterisk. For example, the SQL statement above is exactly equivalent to the following SQL statement

SELECT *

Fromstudent

where exists

(Select *from gradewhere student, =grade.)

The role of Notexists is opposite to exists. If the subquery does not return rows, the WHERE clause in not EXISTS is satisfied.

SQL subqueries and nested queries