Learning T-SQL (Transact-SQL) - Unit 3

Transact-SQL

Understand subqueries


A subquery is a SELECT statement nested, or embedded, within another query. The nested query, which is the subquery, is referred to as the inner query. The query containing the nested query is the outer query.In general, subqueries are evaluated once, and provide their results to the outer query.

Multiple levels of subqueries are supported in Transact-SQL , up to 32 levels are supported in T-SQL.

Subquery is a scalar or multi-valued subquery:

  • Scalar subqueries return a single value. Outer queries must process a single result.
  • Multi-valued subqueries return a result much like a single-column table. Outer queries must be able to process multiple values.

SELECT MAX(SalesOrderID)
FROM Sales.SalesOrderHeader

This above query returns a single value that indicates the highest value for an OrderID in the SalesOrderHeader table.

SELECT SalesOrderID, ProductID, OrderQty
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 
   (SELECT MAX(SalesOrderID)
    FROM Sales.SalesOrderHeader);

Multi-valued subqueries, as the name suggests, can return more than one row. However, they still return a single column. 

Self-contained subqueries or they can be correlated with the outer query:

  • Self-contained subqueries can be written as stand-alone queries, with no dependencies on the outer query. A self-contained subquery is processed once, when the outer query runs and passes its results to that outer query.
  • Correlated subqueries reference one or more columns from the outer query and therefore depend on it. Correlated subqueries cannot be run separately from the outer query.
EXample of a correlated subquery -

SELECT SalesOrderID, CustomerID, OrderDate
FROM SalesLT.SalesOrderHeader AS o1
WHERE SalesOrderID =
    (SELECT MAX(SalesOrderID)
     FROM SalesLT.SalesOrderHeader AS o2
     WHERE o2.CustomerID = o1.CustomerID)
ORDER BY CustomerID, OrderDate;

JOIN vs Subquery

Source : Microsoft


In addition to retrieving values from a subquery, T-SQL provides a mechanism for checking whether any results would be returned from a query. The EXISTS predicate determines whether any rows meeting a specified condition exist, but rather than return them, it returns TRUE or FALSE. This technique is useful for validating data without incurring the overhead of retrieving and processing the results.


Source : Microsoft







Comments

Post a Comment