On sub-query in T-SQL

Introduction

In this article, we will briefly discuss the relevant knowledge of a sudden inquiry. Subqueries can be divided into independent subqueries and correlated subqueries. A stand-alone subquery does not depend on the external query it belongs to, and the dependent subquery relies on the external query it belongs to. The values returned by the subquery can be scalar (single-valued), multivalued, or an entire table. The following is a detailed description of what the subquery is about.

Stand-alone sub-query

A standalone subquery is a subquery that is independent of an external query. Logically, a standalone subquery needs to be executed only once before an external query is executed, and the external query continues the query using the results of the subquery. Take a look at the following example:

Suppose you now need to return the record with the largest order number in the Orders table. We can implement this function by using the scalar quantum query.

1 Select Orderid,orderdate,empid,custid 2  from sales.orders 3 where orderid=4         (selectmax from Sales.orders)

1 11077     - -  to -  . xx:00.000    1    

We see 11077 is the largest order number, through the subquery we can first query out the number of the largest order number, so that the external query OrderID and query processing the largest order number of the same.

　　Note: For scalar independent subqueries, if you return multiple values, the SQL statement will error, because in the WHERE clause we are using an equal sign, and an equal sign is an error when compared to multiple values.

independent Multi-valued subqueries

As mentioned earlier, for a scalar subquery, the result returned by a subquery can only be a single value. A stand-alone multivalued subquery is a subquery that can return multiple values. The in predicate can handle stand-alone multivalued subqueries. The in verb's matching rule is that if the value of a scalar expression is equal to any of the values in the subquery return value, the in predicate evaluates to True. Let's take a look at the following example:

Suppose we now need to return an order with an employee whose name is the processing that begins with D. With independent multivalued subqueries we can write:

 1  select   Orderid,orderdate,empid,custid  2   Sales.orders  3  where  empid in  4  (select  empid from   HR. Employees  5  where  lastname like   " d%   ) 

We see that the related multivalued subquery first queries the Employees table to find all employees who have started with D and form a result set. The outer query then matches the data with the result set when querying each row, and returns the row if it finds that empid is in the result set in the subquery. Of course, we can also achieve the same goal through internal connections.

 1  select   Orderid,orderdate,o.empid,custid  2  as   O  3  inner  join  HR. Employees as   E  4  =  e.empid  5  like    d%    

　　Note: null values should be excluded from the result set returned by the subquery

Related sub-query

A related subquery is a subquery that references a column of a table that appears in an external query. Logically, subqueries are evaluated separately for each outer row. Take a look at the following example:

Assuming that we now need to return the maximum order number for each customer, we can implement such a function using the relevant subquery.

 1  select   Custid,orderid,orderdate,empid  2  as   O1  3  where  orderid=  4  (select  max  (OrderID) from  Sales.orders as   O2  5  where  o1.custid=  O2.custid) 
   

Let's take a look at the logical process of this query. As I said earlier, for each row of an external query, the related subquery is recalculated once. For each row of an external query, the related subquery obtains its largest order number based on the customer ID of the outer row, or returns the line if the order number of the current outer row matches the order number returned by the related subquery, or discards the line, and then matches the next line.

EXISTS predicate

SQL supports a predicate named exists, whose input is a subquery, and if the subquery can return any rows, exist returns True, otherwise false. Let's take a look at the following example.

Assuming that you now need to query the Spanish customer who placed the order, we can write the SQL statement:

 1  select  custid,companyname from  sales.customers  C  2  where  country=  n '  

In this query, the external query first filters out the Spanish customers and then matches each row with the subquery, and if the customer has placed the order, the row is filtered out.

Advanced Sub-query

Here are some things called advanced that are implemented using subqueries. Readers can take readings.

　　Returns the previous or last record

　　If we need to query the order form, return the previous order level and the last order for each order. In the order table, the "previous" conceptually refers to the logical order, and "previous" refers to the maximum value that is less than the current value. The "Last" value is the minimum value that is greater than the current value. Let's start by looking at the SQL statement that implements the previous effect.

1 Select Orderid,orderdate,empid,custid, 2     (selectmax from as O23         where O2.orderid< as prevorderid4 from as O1

The results are as follows:

　　

On sub-query in T-SQL