"T-SQL Basics" 03. Subqueries

This series of "T-SQL Fundamentals" is primarily a summary of the T-SQL Foundation.

"T-SQL Basics" 01. Single-Table query-several SQL query questions

"T-SQL Basics" 02. Join Query

"T-SQL Basics" 03. Subqueries

"T-SQL Basics" 04. Table expressions

"T-SQL Basics" 05. Set operation

"T-SQL Basics" 06. Perspective, inverse perspective, grouping set

"T-SQL Basics" 07. Data modification

"T-SQL Fundamentals" 08. Transactions and concurrency

"T-SQL Basics" 09. Programmable objects

----------------------------------------------------------

"T-SQL Performance Tuning" 01. Index optimization

"T-SQL Performance Tuning" 02. Execution plan

"T-SQL Performance Tuning" 03. Deadlock Analysis

Exercises:

1. write a query statement that returns all orders generated on the last day of the activity in the Orders table.

2. Check out all orders that have been placed by the customer with the largest number of orders.

3. inquire about the employees who have not processed the order since the Year 5 month 1 (including this day).

4. inquire about the customer who has not placed an order in the year under Decade

5. inquiries for customers who have ordered product No.

Overview:

This article is mainly a summary of the basis of sub-query.

Keyword Explanation:

external query: Query result set returned to caller

internal query: The query result set is returned to the external query.

Stand -alone subqueries: Singleton subqueries are independent of the subqueries of their external queries, and can be run independently of subqueries. Logically, a stand-alone subquery executes one time before an external query executes, and the external query then uses the results of the subquery to continue the query.

correlated subquery : A subquery that refers to a table that appears in an external query that relies on an external query and cannot be called independently. Logically, subqueries are evaluated separately for each outer row.

scalar Quantum query: A subquery that returns a single value. A scalar subquery can appear anywhere in an external query where a single value is expected to be used.

Multi-valued subquery: in a column

Why use subqueries?

You can avoid dividing operations into multiple steps in a query solution, and you need to save intermediate query results in variables.

One, independent sub-query1. Independent scalar Quantum query (view exercises )

Example: From HR. returns the maximum employee information for empid in the Employees table.

Can be divided into two steps:

A. define a variable maxid , query out the empid of Empid's largest employee with a separate scalar subquery , and then Save the Empid to a variable @maxid in B. filtering out empid = @maxid Records in the Where condition

DECLARE @maxid as INT = (SELECT    MAX (empid)                          from      HR. Employees                        ) SELECT  *from    HR. Employeeswhere   empid = @maxid  

A simpler approach is to embed a subquery, which requires only one query to query the empid Maximum employee information .

SELECT  *from    HR. Employeeswhere   empid = (SELECT    MAX (empid)                  from      HR. Employees                )

Attention:

1. for a valid scalar subquery, its return value cannot exceed one, and if the scalar subquery returns multiple values, it may fail at run time.

2. if the scalar subquery does not return any values, the result is converted to null, and the null row is compared with UNKNOWN, and thequery filter does not return any results that the filter expression evaluates to UNKNOWN of the line.

2. Independent multivalued subqueries (view exercises 3) (1) syntax format for multi-valued subqueries

< scalar expressions > in (< multivalued subqueries >)

Example: Return title contains information about an order processed by an employee of the manager

Scenario One: Independent multivalued subqueries

Select  *from    sales.orderswhere   empid in (select   empid                   from     HR. Employees                   WHERE    HR. Employees.title like '%manager ')

Scenario two: Inner JOIN query

SELECT  *from    sales.orders        INNER JOIN HR. Employees on Sales.Orders.empid = HR. Employees.empidwhere   HR. Employees.title like '%manager '

Similarly, many places can use subqueries or join queries to solve problems. The database engine's interpretation of the two queries is sometimes the same, while in other cases the interpretation is different. You can use a query to solve the problem first, if the performance is not good, then try to replace the subquery with a join, or use a subquery instead of a join.

3. Sub-query distinct keyword

when we want to eliminate duplicate values in subqueries, it's not necessary to specify the DISTINCT keyword in a subquery, because the database engine helps us remove duplicate values instead of displaying the specified distinct keyword.

Second, related sub-query1. related sub-query

What is a correlated subquery: A column that references a table that appears in an external query, relies on an external query, and cannot run a subquery independently. Logically, subqueries are evaluated separately for each outer row.

Example: Query each customer to return all orders that were placed on the last day of the activity he participated in.

Expected Result:

Number of rows affected :

1. first use the independent scalar subquery to query the largest order date, return to the external query

SELECT  MAX (OrderDate) from    sales. Orders as O2

2. external query with O1. OrderDate Filter to filter out orders equal to the maximum order date

3. in order to find out the orders that each customer participates in, the independent scalar query is changed to the correlated subquery, and the subquery O2.custid is associated with the outer query O1.custid .

For each row in O1, the subquery is responsible for returning the maximum order date for the current customer. If the order date of a row in O1 matches the order date returned by the subquery, then the order date in O1 is the largest order date for the current customer, in which case the query returns the row in the O1 table.

SELECT  MAX (OrderDate) from    sales. Orders as O2where   O2.custid = O1.custid

To synthesize the above steps, get the following query statement:

SELECT  orderid,orderdate,custidfrom    sales. Orders as O1where   o1.orderdate = (SELECT MAX (OrderDate)                         from   sales. Orders as O2                         WHERE  o2.custid = O1.custid                       )

2.EXISTS predicates ( view exercises 4,5)

2. < outside query > WHERE EXISTS ( subquery )
4. Its input is a subquery: If the subquery can return any rows, the predicate returns TRUE, otherwise FALSE is returned.
6. If the subquery query results more than one, theSQL SERVER engine queries out a record, it returns immediately, this processing is called short-circuit processing.
8. The Exist predicate only cares if there is a matching row, regardless of the column specified in the select list, and all uses the select * from TABLE withno negative effect, but in order to expand * The column name of the code will have a small amount of overhead, but it is recommended to use the * wildcard character, the query statement should be as natural and intuitive as possible, unless there is a compelling reason to sacrifice the code in this aspect of the requirements.
10. The not EXISTS predicate is the opposite side of the EXISTS predicate

Three, exercises1. write a query statement that returns all orders generated on the last day of the activity in the Orders table.

Expected Result:

This paper examines the basic usage of the independent subquery, first returns the date of the last day with an independent subquery, and then the outer query filters out all orders with the order date equal to the last day.

Select  OrderID,        OrderDate,        CustID,        empidfrom    sales.orderswhere   orderdate = (select    MAX (OrderDate)                      from      sales.orders                    )

2. Check out all orders that have been placed by the customer with the largest number of orders.

Expected Result:

The use of independent subqueries, similar to the first question, is divided into two steps:

(1) query the customer ID with the largest number of orders with a subquery first

(2) The ID is then returned to the external query, and the external query filters out all orders placed by the customer through the customer ID

Scheme one: Independent scalar quantum query

Select  CustID,        OrderID,        OrderDate,        empidfrom    sales.orderswhere   custid = (select TOP (1) WIT H TIES                            o.custid                   from     sales.orders as O                   GROUP by CustID                   ORDER by COUNT (*) DESC                 )

Attention:

TOP (1) with TIES O.custid

Finds all rows after sorting that are equal to the first record O.custid

Because the total number of orders for the customer with the highest number of orders is , and there is only one customer (custid=71), the final query result is only for all orders placed by custid=71 's customers.

3. inquire about the employees who have not processed the order since the Year 5 month 1 (including this day).

Expected Result:

The use of independent sub-query, the subject can also take two steps to find out the results.

(1) First use a subquery to return all the employees who have processed the order after the Year 5 month (including this day), and return the employee's empid to the external query

(2) The external query then filters out all employees who have not processed the order after 5 months 1 (including this day)

Scheme one: Independent scalar quantum query + not in

SELECT  *from    HR. Employeeswhere   Empid not in (SELECT   empid                       from     sales.orders                       WHERE    OrderDate >= ' 20080501 ')

4. inquire about the customer who has not placed an order in the year under Decade

Expected output:

Scheme one: INNER JOIN + Independent scalar quantum query

1. Check out the customer collection for all 20070101~20071231 orders Collection1

SELECT DISTINCT c.custid,companyname from Sales.orders Oinner joins Sales.customers as C on c.custid = O.custidwhere (Order Date <= ' 20071231 ' and OrderDate >= ' 20070101 ')

2. Check the 20080101~20081231 of all orders placed by the customer Collection2

SELECT C.custid from Sales.orders Oinner joins Sales.customers as C on c.custid = O.custidwhere (OrderDate <= ' 20081231 ' and OrderDate >= ' 20080101 ')

3.collection1 does not contain a subset of Collection2 is a customer who orders in the under decade and under decade the order

SELECT DISTINCT c.custid,companyname from Sales.orders Oinner joins Sales.customers as C on c.custid = O.custidwhere (Order Date <= ' 20071231 ' and OrderDate >= ' 20070101 ') and C.custid not in (SELECT C.custid from sales.orders oinner JOIN Sa Les. Customers as C on c.custid = O.custidwhere (OrderDate <= ' 20081231 ' and OrderDate >= ' 20080101 '))

Scenario Two: Related sub-query exists+not EXISTS

1. Check out the customer collection for all 20070101~20071231 orders Collection1

2. Check the 20080101~20081231 of all orders placed by the customer Collection2

3.collection1 does not contain a subset of Collection2 is a customer who orders in the under decade and under decade the order

Select  C.custid,        companynamefrom    sales.customers as Cwhere   EXISTS (SELECT *   from Sales.orders as O                 WHERE  o.custid = C.custid and                        (OrderDate <= ' 20071231 ' and                              OrderDate >= ' 2007010 1 '                            )) and not        EXISTS (SELECT *                         from   sales.orders as O                         WHERE  o.custid = C.custid                                and (OrderDate <= ' 20081231 '                                      and OrderDate >= ' 20080101 '                                    ))    

By scenario I and scenario two, we can conclude that:INNER join+ Independent subqueries can be replaced with exists+ related subqueries

5. inquiries for customers who have ordered product No.

Expected Result:

Scenario One: Join multiple tables inside

SELECT DISTINCT        C.custid,        companynamefrom    sales.customers as C        INNER JOIN sales.orders as O on C.custid = O.custid        INNER JOIN sales.orderdetails as D on o.orderid = d.orderidwhere   d.productid = ' 12 '

Scenario Two: Nesting correlated subqueries

Select  C.custid,        companynamefrom    sales.customers as Cwhere   EXISTS (SELECT *   from Sales.orders as O                 WHERE  o.custid = C.custid and                        EXISTS (SELECT *                                     from   sales.orderdetails as d< C21/>where  D.orderid = O.orderid and                                            d.productid = ' 12 '))

T-SQL Basics 03. Subqueries