T-SQL query Advanced: deep understanding of subqueries

Introduction

SQL has a very powerful and flexible way of querying, and multiple table join operations can often be replaced with subqueries, this article will cover all aspects of subqueries.

Brief introduction

A subquery is essentially a restricted SELECT statement that is nested into other select,update,insert,delete statements, and in a subquery only the following clauses can be used

Select clause (required)

FROM clause (required)

WHERE clause (optional)

GROUP by (optional)

Having (optional)

Order BY (available only if the top keyword is used)

Subqueries can also be nested within other subqueries, and this nesting can be up to 32 levels. A subquery is also called an internal query (Inner query) or an internal selection (Inner Select), and a query containing a subquery is also called an external query (outter) or an external selection (Outer Select), the concept of a subquery can be simply illustrated in the following illustration:

The figure above is a subquery used as a data source.

Generally, subqueries can be grouped into three types, according to the type of data returned by the subquery, respectively:

Returns a datasheet (table)

Returns a list of values (column)

Returns a single value (Scalar)

Below, we follow these three ways to explain subqueries

Subqueries are used as data sources

When a subquery is used after the FROM clause of an external query, a subquery is used as a data source, even if the subquery returns only a single value (Scalar) or a column value (column), which can still be viewed as a special data source, a two-dimensional datasheet (table). Subqueries used as data sources are much like a view, except that the subquery only exists temporarily and is not included in the database.

such as this statement:

SELECT     P.productid, P.name, P.productnumber, m.name as ProductModelName
from         production.product as P Innerjoin
(SELECT     Name, ProductModelID
 from          Production.ProductModel) as M on 
p.productmodelid = M.productmodelid

The subquery statement above ProductModel the subset m in the table as the data source (table) and the Product table. The results are as follows:

Use as a data source is also the simplest application of subqueries. Of course, when a subquery is used as a data source, it is also divided into related subqueries and unrelated subqueries, which are described later in this article.