SQL statements-Nested queries

Nested queries mean that one query statement (select-from-where) query block can be nested within another query block's WHERE clause, called a nested query. Where the outer query is also called the parent query, the main query. An inner query is also called a subquery, from a query.

Nested queries work by processing internal queries, inner-outer processing, and the results of inner-layer queries using nested queries that are not only used by the parent query SELECT statement. It can also be used in INSERT, UPDATE, DELETE statements, or other subqueries.

the composition of a subquery

1. A standard select query that contains a standard selection list component.

2. A standard FROM clause that contains one or more table or view names.

3. An optional WHERE clause.

4. Optional GROUP BY clause.

5. Optional have clause.

syntax rules for sub-queries

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, sub-queries can be nested up to 32 layers. Individual queries may not support 32-tier nesting.

5. Any place where you can use an expression can use a subquery, as long as it returns a single value.

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

three, simple sub-query

Example:

Select  from  where>  (      Select      from person  where=' Sun Quan ')

The output is:

　　　

four, in nested query

The In keyword is used in the WHERE clause to determine whether the expression of a query is in a list of multiple values. Returns the record that satisfies the criteria in the in list.

Example:

Select  from  where in (    Select from country    where  =' wei ')

The output is:

　　　

Five, some nested query

　　1. Grammar

Some the logical operation symbol in SQL, the result is true if, in a series of comparisons, some values are true. The syntax of some is:

< expressions >=|<>|! =|>|>=|! >|<|<=|! <}some(sub-query)　　

Example:

Select  from  where=some--  compares the values with the equals sign and the following query, and returns if one is equal to    ( Select  from Country     where = ' Wei ' ) 　　　　　　

The output is:

　　

vi. all nested queries

All is a logical operator in SQL, and if a series of comparisons are true, the result can be true.

　　1. Grammar

< expressions >=|<>|! =|>|>=|! >|<|<=|! <all(sub-query)

　　Example:

Select  from  where>all--  This result is true only if Countryid is greater than all the IDs returned below, this result returns (    Selectfrom  country    where=' Wei ' )

The output is:

　　

Seven, exists nested query

　　　1. Grammar

Exists is a logical operation symbol in SQL. True if the subquery has a result set returned. exists represents the meaning of "presence", and it finds only those records that meet the criteria. once the first matching record is found, the search stops immediately.

exists sub-query　　　　　　　　

Where a subquery is a first SELECT statement, the COMPUTE clause and the INTO keyword are not allowed.

exists means that the subquery has a result set returned.
For example:

SELECT *  from  Person WHERE exists (    Select1      --select 0  Select NULL returns the same result because all three subqueries have a result set returned, so always true  SELECT * from person execution as usual)

However, if a subquery is returned with no result set due to a condition being added, the subject sentence is not executed:

SELECT *  from  Person WHERE exists (    SELECT* from person WHERE=        -    --If no person_id records exist, the subquery does not return with the result set, the main statement does not execute )

SQL statements-Nested queries