Edit

Subqueries

  • Article

Applies to: Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

This topic gives examples of using subqueries in Azure Synapse Analytics, Analytics Platform System (PDW), or Microsoft Fabric.

For the SELECT statement, see SELECT (Transact-SQL)

Contents

Basics

Subquery
A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. This is also called an inner query or inner select.

Outer query
The statement that contains the subquery. This is also called an outer select.

Correlated subquery
A subquery that refers to a table in the outer query.

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

This section provides examples of subqueries supported in Azure Synapse Analytics or Analytics Platform System (PDW).

A. TOP and ORDER BY in a subquery

SELECT * FROM tblA  
WHERE col1 IN  
    (SELECT TOP 100 col1 FROM tblB ORDER BY col1);

B. HAVING clause with a correlated subquery

SELECT dm.EmployeeKey, dm.FirstName, dm.LastName   
FROM DimEmployee AS dm   
GROUP BY dm.EmployeeKey, dm.FirstName, dm.LastName  
HAVING 5000 <=   
(SELECT sum(OrderQuantity)  
FROM FactResellerSales AS frs  
WHERE dm.EmployeeKey = frs.EmployeeKey)  
ORDER BY EmployeeKey;

C. Correlated subqueries with analytics

SELECT * FROM ReplA AS A   
WHERE A.ID IN   
    (SELECT sum(B.ID2) OVER() FROM ReplB AS B WHERE A.ID2 = B.ID);

D. Correlated union statements in a subquery

SELECT * FROM RA   
WHERE EXISTS   
    (SELECT 1 FROM RB WHERE RB.b1 = RA.a1   
     UNION ALL SELECT 1 FROM RC);

E. Join predicates in a subquery

SELECT * FROM RA INNER JOIN RB   
    ON RA.a1 = (SELECT COUNT(*) FROM RC);

F. Correlated join predicates in a subquery

SELECT * FROM RA   
    WHERE RA.a2 IN   
    (SELECT 1 FROM RB INNER JOIN RC ON RA.a1=RB.b1+RC.c1);

G. Correlated subselects as data sources

SELECT * FROM RA   
    WHERE 3 = (SELECT COUNT(*)   
        FROM (SELECT b1 FROM RB WHERE RB.b1 = RA.a1) X);

H. Correlated subqueries in the data values used with aggregates

SELECT Rb.b1, (SELECT RA.a1 FROM RA WHERE RB.b1 = RA.a1) FROM RB GROUP BY RB.b1;

I. Using IN with a correlated subquery

The following example uses IN in a correlated, or repeating, subquery. This is a query that depends on the outer query for its values. The inner query is run repeatedly, one time for each row that may be selected by the outer query. This query retrieves one instance of the EmployeeKey plus first and last name of each employee for which the OrderQuantity in the FactResellerSales table is 5 and for which the employee identification numbers match in the DimEmployee and FactResellerSales tables.

SELECT DISTINCT dm.EmployeeKey, dm.FirstName, dm.LastName   
FROM DimEmployee AS dm   
WHERE 5 IN   
    (SELECT OrderQuantity  
    FROM FactResellerSales AS frs  
    WHERE dm.EmployeeKey = frs.EmployeeKey)  
ORDER BY EmployeeKey;

J. Using EXISTS versus IN with a subquery

The following example shows queries that are semantically equivalent to illustrate the difference between using the EXISTS keyword and the IN keyword. Both are examples of a subquery that retrieves one instance of each product name for which the product subcategory is Road Bikes. ProductSubcategoryKey matches between the DimProduct and DimProductSubcategory tables.

SELECT DISTINCT EnglishProductName  
FROM DimProduct AS dp   
WHERE EXISTS  
    (SELECT *  
     FROM DimProductSubcategory AS dps   
     WHERE dp.ProductSubcategoryKey = dps.ProductSubcategoryKey  
           AND dps.EnglishProductSubcategoryName = 'Road Bikes')  
ORDER BY EnglishProductName;

Or

SELECT DISTINCT EnglishProductName  
FROM DimProduct AS dp   
WHERE dp.ProductSubcategoryKey IN  
    (SELECT ProductSubcategoryKey  
     FROM DimProductSubcategory   
     WHERE EnglishProductSubcategoryName = 'Road Bikes')  
ORDER BY EnglishProductName;

K. Using multiple correlated subqueries

This example uses two correlated subqueries to find the names of employees who have sold a particular product.

SELECT DISTINCT LastName, FirstName, e.EmployeeKey  
FROM DimEmployee e JOIN FactResellerSales s ON e.EmployeeKey = s.EmployeeKey  
WHERE ProductKey IN  
(SELECT ProductKey FROM DimProduct WHERE ProductSubcategoryKey IN  
(SELECT ProductSubcategoryKey FROM DimProductSubcategory   
 WHERE EnglishProductSubcategoryName LIKE '%Bikes'))  
ORDER BY LastName;