Understanding T-SQL: Advanced queries

1. subquery

A subquery is a regular SQL query nested in other queries. When a SELECT statement is required as the basis of the data section or a condition element in another query, A subquery is created.
Subqueries often meet the following requirements:
A. Break down a query into a series of logical steps
B. Provide a list as the target object of the WHERE clause and [in | exsits | any | all ].
C. Provide search driven by every record in the parent Query
It is worth noting that most subqueries can be expressed by joins. Joining is generally a preferred choice when you can use joins to replace subqueries.

● Nested subquery [nested subquery]
Nested subqueries are only performed in one direction-either a value for external queries or a list of values used together with the in operator.
When the "=" operator is used, a query of a value is returned (one column is returned from a row). When the "in" operator is used, a list is returned.
Syntax: Select <columns> from <Table> where <onecolumn> = (select <onecolumn> from <othertable> where <returns only one row of conditions>)
Select <columns> from <Table> where <onecolumn> in (select <onecolumn> from <othertable> where <condition>)
Example 1: Nested query using a single-value select statement
Assume there are two tables: orders and orderdetails.
The orders table has the following fields: [orderid], [employeeid], [orderdate], [shippeddate]...
The orderdetails table has the following fields: [orderid], [productid], [unitprice] ..
Now we need to find the Order details of the earliest date in the orderdetails table. As a general case, we can do this:
Declare @ firstdatetime smalldatetime;
Select @ firstdatetime = min (orderdate) from orders; -- @ fistdatetime is a returned value.
Select * from orders join orderdetails on orders. orderid = orderdetails. orderid where orderdate = @ firstdatetime;
However, with the query clause, the preceding three statements can be combined into one SQL statement:
Select * from orders join orderdetails on orders. orderid = orderdetails. orderid where orderdate = (select Min (orderdate) from orders );
Example 2: Use nested queries for subqueries that return multiple values
Assume there are two tables: employees, contact
Employees includes the following fields: [employeeid], [conactid], [loginid], and [title] to indicate employee information.
The contact includes the following [conactid], [firstname], and [lastname] to indicate the personnel information.
Assume that you want to get the names of all the personnel whose positions are network manager. You can also use subqueries in addition to the join representation:
Select * From HumanResources. employee e join person. Contact C on E. contactid = C. contactid where E. Title in (select title from HumanResources. Employee where title = 'network manager ')
Of course, the above is just a demonstration technique, and the actual implementation is better with the join:
Select * From HumanResources. employee e join person. Contact C on E. contactid = C. contactid where E. Title = 'network manager'
In addition to using in, you can also use not in to exclude matching records:
Select * From HumanResources. employee e join person. contact C on E. contactid = C. contactid where E. title not in (select title from HumanResources. employee where title = 'network manager ')

 

● Any, some, all operators
The in and = operators fully match records. If you want to perform a full match, you can use the any, some, all operators.
Any and some functions are the same. Other operators with a wider range can be used on the list created by the subquery, such as >=, <=, <> ,!...
For example,> some (<list item>) means that the value is greater than any of the values, that is, greater than a minimum value. Therefore,> some (, 3) means greater than 1, if used with =, it has the same function as the in operator.
If it is> All (, 3), it means that it is greater than all values, that is, it is greater than the maximum value.

 

2. Related subqueries

Related subqueries are one of the things that can become impossible. In addition, they can oftenCodeA single row, which often leads to performance improvement. Related subqueries need to be different from conventional methods of thinking.
The difference between a subquery and a nested subquery is that in a nested subquery, an internal query is processed once and information is transmitted for external query. the external query is executed only once. in related subqueries, the information is bidirectional. the internal query is executed based on the information provided by the External query, and vice versa.
Perform the following three steps for the subquery:
A. obtain a record from the external query and pass it to the internal query.
B. Execute internal queries based on input values
C. The internal query transmits the value from the result to the external query.
For example, an existing orders table has the following fields:

Orderid customerid orderdate freight
-----------------------------------------------------------------
10248 Vinet 00:00:00. 000 32.38
10249 tomsp 00:00:00. 000 11.61
10250 Hanar 00:00:00. 000 65.83
10251 victe 00:00:00. 000 41.34
10252 suprd 00:00:00. 000 51.30
10253 Hanar 00:00:00. 000 58.17
10254 chops 00:00:00. 000 22.98
10255 ricsu 00:00:00. 000 148.33
10256 wellI 00:00:00. 000 13.97
10257 hilaa 00:00:00. 000 81.91
10258 ernsh 00:00:00. 000 140.51
10259 CENTC 00:00:00. 000 3.25
...
The fields indicate the order number, customer ID, order date, and freight respectively.
Now I want to know the orderid of the first order for each customer.
Based on our ideas, we can find the statement that the earliest order in all records is like this:
Select min (orderdate) from orders; --> is the earliest order date in all orders, is a value
Then, find the earliest order information for each customer:
Select customerid, min (orderdate) from orders group by customerid; -- above, the Group by statement cannot be fewer. It is summarized by group to obtain the earliest order date of each customer.
However, if you want to know the order number of each customer's earliest order date, you cannot use the following statement:
Select orderid, customerid, min (orderdate) from orders group by customerid; -- orderid cannot be grouped by customerid
Therefore, a temporary table (# minorderdates) can be used as a solution, which is then divided into two SQL statements:
Select customerid, min (orderdate) as orderdate into # minorderdates from orders group by customerid;
Select O. orderid, O. customerid, O. orderdate from orders o join # minorderdates m on O. customerid = M. customerid and O. orderdate = M. orderdate
So how can we solve this problem with related subqueries?
Select o1.mermerid, o1.orderid, o1.orderdate from orders O1 where o1.orderdate = (select Min (o2.orderdate) from orders O2 where o2.customerid = o1.customerid)
Note that the WHERE clause in the subquery references the table fields in the external query. Therefore, it is necessary to specify an alias.

 

3. Select list subquery

Subqueries can also be used to provide different types of answers in the selection results, which usually occur when the information to be searched is completely different from other data in the query (for example, if you want to aggregate a field, you do not want to affect other returned fields)
It is still the same as the query in the previous section, but this time we need to get the lastname of the customer, instead of the customerid
Select C. lastname (select Min (orderdate) from orders O where O. customerid = C. customerid) as "orderdate" from customers C