SQL Server Technology Insider's 4 sub-query

The result set of the outermost query is returned to the caller, called an external query. The result of an internal query is used by an external query, also known as a subquery. Sub-queries can be divided into independent subqueries and correlated sub-queries in two categories. A standalone subquery does not depend on the external query it belongs to, and the dependent subquery relies on the external query it belongs to. The expected value of a subquery can be single-valued, multivalued, or table-valued.

1. Independent sub-query

A standalone subquery is a subquery that is independent of its outer query. Logically, a stand-alone subquery executes only once before executing an external query, and the external query then uses the results of the subquery to continue the query.

1.1 Independent Scalar Quantum query

A scalar subquery is a subquery that returns a single value, and a scalar subquery can appear anywhere in an external query where a single value is expected to be used (where, select, and so on). Example: Query order information with the largest order number

= (SELECT MAX (O.orderid) from                 sales.orders as O);

For a valid scalar subquery, it cannot have more than one return value. If a scalar subquery returns multiple values, it may fail at run time.

1.2 Independent multi-valued subqueries

A multivalued subquery is a subquery that returns multiple values in one column, without a pipe query being independent. Some predicates, such as the in predicate, can handle multi-valued subqueries.

SELECT orderidfrom sales.orderswhere empid  in (SELECT e.empid   From HR. Employees as E   WHERE e.lastname like N'D%');

In fact, for the above requirements, you can also use joins instead of sub-query to accomplish this task.

SELECT o.orderidfrom HR. Employees as E  JOIN sales.orders as O    = o.empidwhere e.lastname like N'D% ';

2. Related sub-query

A correlated subquery is a subquery that refers to a column of a table that appears in an external query, which means that the subquery relies on an external query.

2.1 EXISTS predicate

Supports a predicate named exists, whose input is a subquery that returns TRUE if the subquery can return any rows, otherwise false. The following query statement returns the Spanish customer who placed the order:

= N'Spain'and  EXISTS    * from sales.orders as O     = C.custid);

3 Advanced Sub-query

3.1 Return previous or next record

Suppose you want to query the Orders table now, for each order, return information about the current order and information about the previous order. The previous concept can be expressed as "maximum value less than the current value". A similar "latter" concept can be expressed with a "minimum value greater than the current value".

SELECT OrderID, OrderDate, Empid, CustID,  (SELECT MAX (O2.orderid)   From Sales.orders as O2   < o1.orderid) as Prevorderidfrom sales.orders as O1;

3.2 Continuous polymerization (Running Aggregate)

Continuous aggregation is an aggregation that is performed on the cumulative data (usually in chronological order), first defining a View sales.ordertotalsbyyear, which contains the total number of orders per year.

Suppose you now have a task that needs to return the year of the order, the quantity of the order, and the total order quantity for several consecutive years. That is, each year returns the sum of the order quantity as of the year. The following is the query statement for the solution:

SELECT orderyear, qty,  (SELECT SUM (o2.qty)   From Sales.ordertotalsbyyear as O2   <= o1.orderyear) as Runqtyfrom sales.ordertotalsbyyear as O1order by O Rderyear;

3.3 Sub-query for improper behavior (misbehaving)

This section describes the scenarios in which the results of several seed queries may run contrary to what you expect, as well as the best practices that should be performed in order to avoid the occurrence of logical flaws in the code that are related to these situations.

the problem of NULL

Consider the following seemingly straightforward query, which was originally intended to return customers who have not placed an order:

Select CustID, Companynamefrom sales.customers as Cwhere CustID not in (SELECT O.custid from                    sales.orders as O);

Consider a situation where if a row custid a null record appears in the Sales.orders table, then all the results returned by the Where condition statement are unknown, so the result set that the last query appears is empty.

So what are the practical principles that can be implemented to avoid this problem?

1. When a column should not be allowed to be null, it is important to define it as NOT NULL. It is much more important to strengthen the definition of data integrity than many people do.

2. In all the query statements you write, you should consider the three true values that can occur with the three-valued logic. Consider explicitly whether you want to handle null values and, if so, whether the default handling of null values is appropriate for your needs, and adjust the query statement when it is not appropriate. An example of explicitly excluding null values is to add a predicate in a subquery o.custid is not null, as follows:

Select CustID, Companynamefrom sales.customers as Cwhere CustID not in (SELECT O.custid from                     sales.orders as O                    wher E O.custid is not NULL);

An example of implicitly excluding null values is to use the NOT EXISTS predicate instead of the not-in predicate, as follows:

SELECT CustID, Companynamefrom sales.customers as Cwhere not EXISTS   * FROM    sales.orders as O   = C.custid);

SQL Server Technology Insider's 4 sub-query