SQL  Detailed explanation of sub queries for Server

catalogue1 Basic knowledge of sub queries2 Sub query rules3 Column names in qualified subqueries4 Multi layer nesting of subqueries5 Related sub queries6 Sub query typesummary1 Basic knowledge of sub queriesA subquery is a query nested within a SELECT, Insert, UPDATE, DELETE statement, or another subquery.You can use subqueries anywhere an expression is allowed

1 Basic knowledge of sub queries

A subquery is a query nested within a SELECT, Insert, UPDATE, DELETE statement, or another subquery.
You can use subqueries anywhere an expression is allowed.

Example:

USEAdventureWorks2016; GOSELECTOrd.SalesOrderID,Ord.OrderDate,(SELECTMAX(OrdDet.UnitPrice)FROMSales.SalesOrderDetailASOrdDetWHEREOrd.SalesOrderID=OrdDet.SalesOrderID)ASMaxUnitPriceFROMSales.SalesOrderHeaderASOrd; GO

Sub queries are also known as internal queries or internal selections, while statements containing sub queries are also known as external queries or external selections.

Many Transact-SQL statements that contain subqueries can also be expressed as joins. Other questions can only be raised using sub queries. In Transact-SQL, there is usually no performance difference between statements containing subqueries and semantic equivalent versions without subqueries. However, in certain situations where it is necessary to check for existence, joins can yield better performance. Otherwise, nested queries must be processed for each result of external queries to ensure the elimination of duplicates. In this case, the join method will yield better results.

The following example shows subqueries and joins that return the same result set and execution plan:

USEAdventureWorks2016; GO/*SELECTstatementbuiltusingasubquery.*/SELECT[Name]FROMProduction.ProductWHEREListPrice=(SELECTListPriceFROMProduction.ProductWHERE[Name]='ChainringBolts'); GO/*SELECTstatementbuiltusingajointhatreturnsthesameresultset.*/SELECTPrd1.[Name]FROMProduction.ProductASPrd1JOINProduction.ProductASPrd2ON(Prd1.ListPrice=Prd2.ListPrice)WHEREPrd2.[Name]='ChainringBolts'; GO

A subquery nested within an external SELECT statement has the following components:

• A general query containing a general selection list component.
• A regular clause containing one or more table or view names.
• Optional: WHERE, GROUPBY, HAVING.

The SELECT query of a subquery is always enclosed in parentheses. It cannot contain an or clause and can only include clauses when the TOP clause is also specified.

A subquery can be nested within an external WHERE, HAVING, SELECT, Insert, UPDATE, DELETE or clause of a statement, or within another subquery. A maximum of 32 levels can be nested, but the limit varies depending on the available memory and the complexity of other expressions in the query. A single query may not support nesting up to 32 levels. If a subquery returns a single value, the subquery can appear anywhere an expression can be used.

If a table only appears in subqueries and not in external queries, the columns in that table cannot be included in the output (selection list of external queries).

Statements containing subqueries typically take one of the following formats:

• WHEREexpression[NOT]IN(subquery)
• WHEREexpressioncomparison_ operator[ANY|ALL](subquery)
• WHERE[NOT]EXISTS(subquery)

In some Transact-SQL statements, subqueries can be calculated like independent queries. Conceptually, subquery results are replaced into external queries (although this may not necessarily be the way SQL Server actually processes Transact-SQL statements with subqueries).

There are three basic types of subqueries:

Operate on the imported list, or compare the list modified by INANY or ALL operators.
Introduced using an unmodified comparison operator and must return a single value.
Whether to use EXISTS to introduce existence testing.

2 Sub query rules

• The selection list of subqueries introduced using comparison operators can only contain one expression or column name.
• If the clause of an external query contains column names, the clause must be compatible with column connections in the sub query selection list.
• The ntext, text, and image data types cannot be used in the selection list of subqueries.
• Since they must return a single value, subqueries introduced by unmodified comparison operators (not followed by keywords or) cannot contain an and clause.
• Keywords cannot be used with included subqueries.
• The and clause cannot be specified.
• ORDERBY can only be specified when TOP is also specified.
• Cannot update views created using subqueries.

3 Column names in qualified subqueries

Example: BusinessEntityID Sales.Store CustomerID Sales.Customer

USEAdventureWorks2016; GOSELECT[Name]FROMSales.StoreWHEREBusinessEntityIDNOTIN(SELECTCustomerIDFROMSales.CustomerWHERETerritoryID=5); GO

Generally, the column names in a statement are implicitly qualified by tables referenced in clauses at the same level. If a column does not exist in the table referenced in the sub query clause, the table referenced in the external query clause will implicitly qualify that column.

The following is the appearance of queries that specify these implicit assumptions:

USEAdventureWorks2016; GOSELECT[Name]FROMSales.StoreWHERESales.Store.BusinessEntityIDNOTIN(SELECTSales.Customer.CustomerIDFROMSales.CustomerWHERETerritoryID=5); GO

Explicit declaration of table names is never wrong, and explicit qualifications can always be used to override implicit assumptions about table names.

4 Multi layer nesting of subqueries

The subquery itself can contain one or more subqueries. Any number of subqueries can be nested within a statement.

Example:

USEAdventureWorks2016; GOSELECTLastName,FirstNameFROMPerson.PersonWHEREBusinessEntityIDIN(SELECTBusinessEntityIDFROMHumanResources.EmployeeWHEREBusinessEntityIDIN(SELECTBusinessEntityIDFROMSales.SalesPerson)); GO

Output:

The innermost query returns the salesperson ID. The next higher level query uses these salesperson IDs for evaluation and returns the employee's contact ID number. Finally, external queries use contact IDs to find the names of employees.

This query can also be represented as a join:

USEAdventureWorks2016; GOSELECTLastName,FirstNameFROMPerson.PersoncINNERJOINHumanResources.EmployeeeONc.BusinessEntityID=e.BusinessEntityIDJOINSales.SalesPersonsONe.BusinessEntityID=s.BusinessEntityID; GO

5 Related sub queries

Many queries can be calculated by executing a subquery once and replacing the result values into the clauses of the external query. In queries containing related subqueries (also known as duplicate subqueries), subqueries rely on external queries with their values. This means that sub queries will be executed repeatedly, and external queries may select each row to execute once.

Example:

USEAdventureWorks2016; GOSELECTDISTINCTc.LastName,c.FirstName,e.BusinessEntityIDFROMPerson.PersonAScJOINHumanResources.EmployeeASeONe.BusinessEntityID=c.BusinessEntityIDWHERE5000.00IN(SELECTBonusFROMSales.SalesPersonspWHEREe.BusinessEntityID=sp.BusinessEntityID); GO

Output results:

The previous subquery in this statement cannot be calculated independently of an external query. It requires a value for Employee.BusinessEntityID, but this value will change as SQL Server checks for different rows in Employee. This is exactly how this query is calculated: SQL Server considers including each row of the Employee table in the results by replacing the values in each row with an internal query. For example, if SQL Server checks the row first, the variable Employee. BusinessEntityID takes a value of 285, and SQL Server replaces it in the internal query. These two query examples represent the decomposition of the previous example with related subqueries.

USEAdventureWorks2016; GOSELECTBonusFROMSales.SalesPersonWHEREBusinessEntityID=285; GO

The result is 0.00 (no bonus received as they are not sales personnel), therefore the calculation result of the external query is:

USEAdventureWorks2016; GOSELECTLastName,FirstNameFROMPerson.PersonAScJOINHumanResources.EmployeeASeONe.BusinessEntityID=c.BusinessEntityIDWHERE5000IN(0.00); GO

Due to this being false, the row of is not included in the results of the previous example query with related subqueries. Perform the same process on the row of. You will see that this row is included in the results because it contains the results.

Summary: Related sub queries can also include table valued functions in clauses by referencing columns in tables as parameters in external queries. In this case, for each row of the external query, the table valued function will be calculated based on the subquery.

6 Sub query type

• With an alias.
• With IN or NOTIN.
• In UPDATE, DELETE, and Insert statements.
• Use comparison operators.
• Use any, SOME, or ALL.
• Follow IS [NOT] DISTINCTFROM.
• With EXISTS or NOTEXISTS.
• Replace expression.

summary

If the column referenced in the subquery does not exist in the subquery, but exists in the table referenced by the clause of the external query, the query will execute without error. SQL Server uses table names in external queries to implicitly qualify columns in subqueries.

That's all for this article on SQL Server sub query explanation. For more related SQL Server sub query content, please search for previous articles or continue browsing related articles below. We hope everyone can support Script Home more in the future!