하위 쿼리(SQL Server)
적용 대상:
Microsoft Fabric의 SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW) SQL 데이터베이스
하위 쿼리는 SELECT, INSERT, UPDATE 또는 DELETE 문 안에 중첩되거나 다른 하위 쿼리 안에 중첩되는 쿼리입니다.
이 문서의 Transact-SQL 코드 샘플은 AdventureWorks2022 또는 AdventureWorksDW2022 샘플 데이터베이스를 사용하며, 이는 Microsoft SQL Server 예시 및 커뮤니티 프로젝트(Microsoft SQL Server Samples and Community Projects) 홈 페이지에서 다운로드할 수 있습니다.
하위 쿼리는 식이 허용되는 모든 위치에서 사용할 수 있습니다. 다음 예에서 하위 쿼리는 SELECT 문에서 MaxUnitPrice라는 열 식으로 사용됩니다.
USE AdventureWorks2022;
GO
SELECT Ord.SalesOrderID, Ord.OrderDate,
(SELECT MAX(OrdDet.UnitPrice)
FROM Sales.SalesOrderDetail AS OrdDet
WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice
FROM Sales.SalesOrderHeader AS Ord;
GO
하위 쿼리 기본 사항
하위 쿼리는 내부 쿼리 또는 내부 선택이라고도 하며 하위 쿼리가 포함된 문을 외부 쿼리 또는 외부 선택이라고 합니다.
하위 쿼리를 포함하는 많은 Transact-SQL 문을 조인으로 작성할 수도 있습니다. 다른 질문은 하위 쿼리로만 질문할 수 있습니다. 일반적으로 Transact-SQL에서는 하위 쿼리를 포함하는 문과 그렇지 않은 의미상 동등한 버전 간에 성능 차이가 없습니다. SQL Server에서 쿼리를 처리하는 방법에 대한 아키텍처 정보는 SQL 문 처리를 참조하세요. 그러나 존재가 검사 경우에 조인은 더 나은 성능을 생성합니다. 그렇지 않으면 중복을 제거하려면 외부 쿼리의 각 결과에 대해 중첩된 쿼리를 처리해야 합니다. 이런 경우 조인을 사용하면 결과를 더 쉽게 얻을 수 있습니다.
다음 예에서는 동일한 결과 집합과 실행 계획을 반환하는 하위 쿼리 SELECT와 조인 SELECT를 모두 보여 줍니다:
USE AdventureWorks2022;
GO
/* SELECT statement built using a subquery. */
SELECT [Name]
FROM Production.Product
WHERE ListPrice =
(SELECT ListPrice
FROM Production.Product
WHERE [Name] = 'Chainring Bolts' );
GO
/* SELECT statement built using a join that returns
the same result set. */
SELECT Prd1.[Name]
FROM Production.Product AS Prd1
JOIN Production.Product AS Prd2
ON (Prd1.ListPrice = Prd2.ListPrice)
WHERE Prd2.[Name] = 'Chainring Bolts';
GO
외부 SELECT 문에 중첩된 하위 쿼리는 다음과 같은 구성 요소를 갖습니다.
- 일반 선택 목록 구성 요소를 포함하는 일반
SELECT쿼리. - 하나 이상의 테이블이나 뷰 이름이 포함된 일반
FROM절 - 선택적
WHERE절. - 선택적
GROUP BY절. - 선택적
HAVING절.
하위 쿼리의 SELECT 쿼리는 항상 괄호로 묶습니다. COMPUTE 또는 FOR BROWSE 절을 포함할 수 없으며, TOP 절도 지정된 경우에만 ORDER BY 절을 포함할 수 있습니다.
하위 쿼리는 외부 WHERE, HAVING, SELECT 또는 INSERT 문의 UPDATE 또는 DELETE 절 내부 또는 다른 하위 쿼리 내부에 중첩될 수 있습니다. 사용 가능한 메모리 및 쿼리의 다른 식의 복잡성에 따라 한도가 다르지만 최대 32개의 중첩 수준이 가능합니다. 개별 쿼리는 최대 32개 수준까지 중첩을 지원하지 않습니다. 하위 쿼리는 단일 값을 반환할 경우 식을 사용할 수 있는 모든 위치에 나타날 수 있습니다.
테이블이 하위 쿼리에만 나타나고 외부 쿼리에는 나타나지 않으면 해당 테이블의 열은 결과(외부 쿼리의 SELECT 목록)에 포함될 수 없습니다.
하위 쿼리가 포함된 문은 다음 중 한 가지 형식을 취합니다.
WHERE expression [NOT] IN (subquery)WHERE expression comparison_operator [ANY | ALL] (subquery)WHERE [NOT] EXISTS (subquery)
일부 Transact-SQL 문에서 하위 쿼리는 독립적인 쿼리인 것처럼 평가할 수 있습니다. 개념적으로는 하위 쿼리 결과가 외부 쿼리로 대체됩니다(SQL Server가 실제로 하위 쿼리가 있는 Transact-SQL 문을 처리하는 방식이 반드시 이런 방식인 것은 아닙니다).
세 가지 기본 유형의 하위 쿼리가 있습니다. 있습니다.
ANY또는ALL에 의해 수정된 비교 연산자나IN으로 시작하는 목록에서 실행- 수정되지 않은 비교 연산자로 시작하고 단일 값을 반환
EXISTS를 사용하여 도입된 존재 테스트인가.
하위 쿼리 규칙
하위 쿼리에는 다음과 같은 제한 사항이 적용됩니다.
- 비교 연산자를 사용하여 도입된 하위 쿼리의 선택 목록에는 하나의 식 또는 열 이름만 포함할 수 있습니다(단,
EXISTS및IN는 각각SELECT *또는 목록에서 연산함). - 외부 쿼리의
WHERE절에 열 이름이 포함된 경우 하위 쿼리 선택 목록의 열과 조인 호환되어야 합니다. - ntext, text 및 image 데이터 형식은 하위 쿼리의 선택 목록에서 사용할 수 없습니다.
- 단일 값을 반환해야 하므로 수정되지 않은 비교 연산자(키워드
ANY또는ALL가 뒤에 오지 않는 연산자)로 도입된 하위 쿼리에는GROUP BY및HAVING절을 포함할 수 없습니다. DISTINCT키워드는GROUP BY를 포함하는 하위 쿼리와 함께 사용할 수 없습니다.COMPUTE및INTO절을 지정할 수 없습니다.ORDER BY은TOP을 함께 지정해야만 지정할 수 있습니다.- 하위 쿼리를 사용하여 만든 뷰는 업데이트할 수 없습니다.
EXISTS로 시작하는 하위 쿼리의 선택 목록은 규칙에 따라 단일 열 이름 대신 별표(*)로 구성됩니다.EXISTS를 사용하여 도입된 하위 쿼리의 규칙은 표준 선택 목록의 규칙과 동일하지만,EXISTS를 사용하여 도입된 하위 쿼리는 존재 테스트를 생성하고 데이터 대신 TRUE 또는 FALSE를 반환하므로 표준 선택 목록의 규칙과 동일합니다.
하위 쿼리의 열 이름 한정
다음 예에서는 외부 쿼리의 WHERE 절에 있는 BusinessEntityID 열이 외부 쿼리 FROM 절(Sales.Store)의 테이블 이름으로 암시적으로 한정되어 있습니다. 하위 쿼리의 SELECT 목록에서 CustomerID에 대한 참조는 하위 쿼리의 FROM 절, 즉 Sales.Customer 테이블로 한정됩니다.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Sales.Store
WHERE BusinessEntityID NOT IN
(SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID = 5);
GO
일반적으로 문의 열 이름은 같은 수준의 FROM 절에서 참조하는 테이블로 암시적으로 한정됩니다. 하위 쿼리의 FROM 절에 참조된 테이블에 열이 존재하지 않는 경우, 외부 쿼리의 FROM 절에 참조된 테이블에 의해 암시적으로 한정됩니다.
이러한 암시적 가정이 지정된 쿼리의 모양은 다음과 같습니다.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Sales.Store
WHERE Sales.Store.BusinessEntityID NOT IN
(SELECT Sales.Customer.CustomerID
FROM Sales.Customer
WHERE TerritoryID = 5);
GO
테이블 이름을 명시적으로 명시하는 것은 결코 잘못된 것이 아니며, 명시적인 자격을 사용하여 테이블 이름에 대한 암시적인 가정을 재정의하는 것은 언제나 가능합니다.
Important
하위 쿼리의 FROM 절에서 참조하는 테이블에 열이 없지만 외부 쿼리의 FROM 절에서 참조하는 테이블에 열이 있는 경우 쿼리는 오류 없이 실행됩니다. SQL Server는 외부 쿼리의 테이블 이름으로 하위 쿼리의 열을 암시적으로 한정합니다.
여러 수준의 중첩
하위 쿼리 자체에는 하나 이상의 하위 쿼리가 포함될 수 있습니다. 문에 여러 하위 쿼리를 중첩할 수 있습니다.
다음 쿼리는 영업 담당자인 직원의 이름을 찾습니다.
USE AdventureWorks2022;
GO
SELECT LastName, FirstName
FROM Person.Person
WHERE BusinessEntityID IN
(SELECT BusinessEntityID
FROM HumanResources.Employee
WHERE BusinessEntityID IN
(SELECT BusinessEntityID
FROM Sales.SalesPerson)
);
GO
결과 집합은 다음과 같습니다.
LastName FirstName
-------------------------------------------------- -----------------------
Jiang Stephen
Abbas Syed
Alberts Amy
Ansman-Wolfe Pamela
Campbell David
Carson Jillian
Ito Shu
Mitchell Linda
Reiter Tsvi
Saraiva Jos
Vargas Garrett
Varkey Chudukatil Ranjit
Valdez Rachel
Tsoflias Lynn
Pak Jae
Blythe Michael
Mensa-Annan Tete
(17 row(s) affected)
가장 안쪽의 쿼리는 영업 사원 ID를 반환합니다. 이 쿼리보다 한 수준 위의 쿼리는 이러한 영업 사원 ID로 평가하여 직원의 연락처 ID 번호를 반환합니다. 마지막으로 외부 쿼리는 연락처 ID를 사용하여 직원의 이름을 찾습니다.
위의 쿼리를 조인으로 표시할 수도 있습니다.
USE AdventureWorks2022;
GO
SELECT LastName, FirstName
FROM Person.Person c
INNER JOIN HumanResources.Employee e
ON c.BusinessEntityID = e.BusinessEntityID
JOIN Sales.SalesPerson s
ON e.BusinessEntityID = s.BusinessEntityID;
GO
상관 하위 쿼리
대부분의 쿼리는 하위 쿼리를 한 번 실행하고 그 결과 값을 외부 쿼리의 WHERE 절에 대체함으로써 평가됩니다. 상호 관련된 하위 쿼리(반복 하위 쿼리라고도 함)를 포함하는 쿼리에서 하위 쿼리는 값에 대해 외부 쿼리에 종속됩니다. 즉, 하위 쿼리는 외부 쿼리에서 선택할 수 있는 각 행에 대해 한 번씩 반복적으로 실행됩니다.
이 쿼리는 SalesPerson 테이블에서 보너스가 5000이고 Employee 및 SalesPerson 테이블에서 직원 ID 번호가 일치하는 각 직원의 이름 및 성의 인스턴스 하나를 검색합니다.
USE AdventureWorks2022;
GO
SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000.00 IN
(SELECT Bonus
FROM Sales.SalesPerson sp
WHERE e.BusinessEntityID = sp.BusinessEntityID) ;
GO
결과 집합은 다음과 같습니다.
LastName FirstName BusinessEntityID
-------------------------- ---------- ------------
Ansman-Wolfe Pamela 280
Saraiva José 282
(2 row(s) affected)
이 문의 이전 하위 쿼리는 외부 쿼리와 독립적으로 평가할 수 없습니다. Employee.BusinessEntityID의 값이 필요하지만 이 값은 SQL Server에서 Employee의 다른 행을 검사할 때 변경됩니다.
이것이 바로 이 쿼리를 평가하는 방식입니다. SQL Server는 각 행의 값을 내부 쿼리로 대체하여 Employee 테이블의 각 행을 결과에 포함할지 고려합니다.
예를 들어, SQL Server가 먼저 행에서 Syed Abbas를 검사하는 경우 Employee.BusinessEntityID 변수는 285 값을 사용하며, SQL Server는 이 값을 내부 쿼리로 대체합니다. 이러한 두 쿼리 샘플은 상호 관련된 하위 쿼리를 사용하여 이전 샘플을 분해한 것입니다.
USE AdventureWorks2022;
GO
SELECT Bonus
FROM Sales.SalesPerson
WHERE BusinessEntityID = 285;
GO
결과 값은 0.00(Syed Abbas는 영업 사원이 아니기 때문에 보너스를 받지 않음)이 되어 외부 쿼리는 다음으로 평가됩니다.
USE AdventureWorks2022;
GO
SELECT LastName, FirstName
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000 IN (0.00);
GO
false이기 때문에 Syed Abbas에 대한 행은 상호 관련된 하위 쿼리가 있는 이전 샘플 쿼리의 결과에 포함되지 않습니다. Pamela Ansman-Wolfe에 대한 행을 사용하여 동일한 절차를 진행합니다. WHERE 5000 IN (5000)에는 결과가 포함되므로 이 행이 결과에 포함된 것을 볼 수 있습니다.
상호 관련된 하위 쿼리는 외부 쿼리에서 테이블의 열을 테이블 반환 함수의 인수로 참조하여 FROM 절에 테이블 반환 함수를 포함할 수도 있습니다. 이 경우 외부 쿼리의 각 행에 대해 테이블 반환 함수는 하위 쿼리에 따라 평가됩니다.
하위 쿼리 유형
하위 쿼리는 다음과 같이 여러 위치에서 지정할 수 있습니다.
- 별칭 사용. 자세한 내용은 테이블 별칭이 있는 하위 쿼리를 참조하세요.
IN또는NOT IN사용. 자세한 내용은 IN이 있는 하위 쿼리 및 NOT IN이 있는 하위 쿼리를 참조하세요.UPDATE,DELETE,INSERT문에서. 자세한 내용은 UPDATE, DELETE 및 INSERT 문의 하위 쿼리를 참조하세요.- 비교 연산자와 함께 사용. 자세한 내용은 비교 연산자가 있는 하위 쿼리를 참조하세요.
ANY,SOME또는ALL사용. 자세한 내용은 ANY, SOME 또는 ALL에 의해 수정된 비교 연산자를 참조하세요.IS [NOT] DISTINCT FROM사용. 자세한 내용은 IS [NOT] DISTINCT FROM(Transact-SQL)을 참조하세요.EXISTS또는NOT EXISTS사용. 자세한 내용은 EXISTS가 있는 하위 쿼리 및 NOT EXISTS가 있는 하위 쿼리를 참조하세요.- 식 대신. 자세한 내용은 식 대신 사용된 하위 쿼리를 참조하세요.
테이블 별칭이 있는 하위 쿼리
하위 쿼리와 외부 쿼리가 동일한 테이블을 참조하는 많은 문을 셀프 조인(테이블 자체에 조인)으로 표현할 수 있습니다. 예를 들어 하위 쿼리를 사용하여 특정 주에서 직원의 주소를 찾을 수 있습니다.
USE AdventureWorks2022;
GO
SELECT StateProvinceID, AddressID
FROM Person.Address
WHERE AddressID IN
(SELECT AddressID
FROM Person.Address
WHERE StateProvinceID = 39);
GO
결과 집합은 다음과 같습니다.
StateProvinceID AddressID
----------- -----------
39 942
39 955
39 972
39 22660
(4 row(s) affected)
또는 셀프 조인을 사용할 수 있습니다.
USE AdventureWorks2022;
GO
SELECT e1.StateProvinceID, e1.AddressID
FROM Person.Address AS e1
INNER JOIN Person.Address AS e2
ON e1.AddressID = e2.AddressID
AND e2.StateProvinceID = 39;
GO
조인되는 테이블이 서로 다른 두 가지 역할로 나타나기 때문에 테이블 별칭 e1 및 e2가 필요합니다. 내부 및 외부 쿼리에서 동일한 테이블을 참조하는 중첩된 쿼리에서도 별칭을 사용할 수 있습니다.
USE AdventureWorks2022;
GO
SELECT e1.StateProvinceID, e1.AddressID
FROM Person.Address AS e1
WHERE e1.AddressID IN
(SELECT e2.AddressID
FROM Person.Address AS e2
WHERE e2.StateProvinceID = 39);
GO
명시적 테이블 별칭은 하위 쿼리에서 Person.Address에 대한 참조가 외부 쿼리에서의 참조와 동일하지 않음을 분명하게 해줍니다.
IN이 있는 하위 쿼리
IN(또는 NOT IN)으로 시작하는 하위 쿼리의 결과는 값이 0 이상인 목록입니다. 하위 쿼리가 결과를 반환하면 외부 쿼리가 결과를 사용합니다.
다음 쿼리는 Adventure Works Cycles에서 만드는 모든 휠 제품의 이름을 찾습니다.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE [Name] = 'Wheels');
GO
결과 집합은 다음과 같습니다.
Name
----------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel
(14 row(s) affected)
이 문은 두 단계로 나누어서 계산됩니다. 먼저 내부 쿼리는 이름이 Wheel(17)과 일치하는 하위 범주 ID 번호를 반환합니다. 둘째, 이 값을 외부 쿼리에 대입하여 Production.Product의 하위 범주 ID 번호와 일치하는 제품 이름을 찾습니다.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN ('17');
GO
이와 유사한 문제에 대해 하위 쿼리가 아닌 조인을 사용하는 경우의 한 가지 차이점은 조인을 통해 결과에 둘 이상의 테이블의 열을 표시할 수 있다는 점입니다. 예를 들어 결과에 제품 하위 범주의 이름을 포함하려면 조인 버전을 사용해야 합니다.
USE AdventureWorks2022;
GO
SELECT p.[Name], s.[Name]
FROM Production.Product p
INNER JOIN Production.ProductSubcategory s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
AND s.[Name] = 'Wheels';
GO
결과 집합은 다음과 같습니다.
Name
LL Mountain Front Wheel Wheels
ML Mountain Front Wheel Wheels
HL Mountain Front Wheel Wheels
LL Road Front Wheel Wheels
ML Road Front Wheel Wheels
HL Road Front Wheel Wheels
Touring Front Wheel Wheels
LL Mountain Rear Wheel Wheels
ML Mountain Rear Wheel Wheels
HL Mountain Rear Wheel Wheels
LL Road Rear Wheel Wheels
ML Road Rear Wheel Wheels
HL Road Rear Wheel Wheels
Touring Rear Wheel Wheels
(14 row(s) affected)
다음 쿼리는 신용 등급이 양호하고 Adventure Works Cycles가 20개 이상의 항목을 주문하고 배달까지의 평균 리드 타임이 16일 미만인 모든 공급업체의 이름을 찾습니다.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Purchasing.Vendor
WHERE CreditRating = 1
AND BusinessEntityID IN
(SELECT BusinessEntityID
FROM Purchasing.ProductVendor
WHERE MinOrderQty >= 20
AND AverageLeadTime < 16);
GO
결과 집합은 다음과 같습니다.
Name
--------------------------------------------------
Compete Enterprises, Inc
International Trek Center
First National Sport Co.
Comfort Road Bicycles
Circuit Cycles
First Rate Bicycles
Jeff's Sporting Goods
Competition Bike Training Systems
Electronic Bike Repair & Supplies
Crowley Sport
Expert Bike Co
Team Athletic Co.
Compete, Inc.
(13 row(s) affected)
내부 쿼리를 계산하여 하위 쿼리 조건을 만족하는 공급업체의 ID를 반환한 후 외부 쿼리를 계산합니다. 내부 쿼리 및 외부 쿼리 모두에 있는 WHERE 절에 둘 이상의 조건을 포함할 수 있습니다.
조인을 사용하면 동일한 쿼리가 다음과 같이 표시됩니다.
USE AdventureWorks2022;
GO
SELECT DISTINCT [Name]
FROM Purchasing.Vendor v
INNER JOIN Purchasing.ProductVendor p
ON v.BusinessEntityID = p.BusinessEntityID
WHERE CreditRating = 1
AND MinOrderQty >= 20
AND AverageLeadTime < 16;
GO
조인은 항상 하위 쿼리로 표현할 수 있습니다. 하위 쿼리를 항상 조인으로 표시할 수는 없습니다. 조인은 대칭이기 때문이며 테이블 A를 B에 순서대로 조인하고 동일한 대답을 얻을 수 있습니다. 그러나 하위 쿼리가 있는 경우는 이에 해당되지 않습니다.
NOT IN이 있는 하위 쿼리
NOT IN 키워드로 시작하는 하위 쿼리도 0개 이상의 값 목록을 반환합니다.
다음 쿼리는 완성된 자전거가 아닌 제품의 이름을 찾습니다.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID NOT IN
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE [Name] = 'Mountain Bikes'
OR [Name] = 'Road Bikes'
OR [Name] = 'Touring Bikes');
GO
이 문은 조인으로 변환할 수 없습니다. 유사하지 않은 조인은 다른 의미를 갖습니다. 완성된 자전거가 아닌 일부 하위 범주에 있는 제품의 이름을 찾습니다.
대신 UPDATE, DELETE, INSERT 문을 사용합니다.
UPDATE, DELETE, INSERT 및 SELECT DML(데이터 조작 언어) 문에 하위 쿼리가 중첩될 수 있습니다.
다음 예에서는 ListPrice 테이블의 Production.Product 열의 값을 두 배로 만듭니다. WHERE 절의 하위 쿼리는 Product 테이블에서 업데이트되는 행을 BusinessEntity 1540이 제공하는 행으로만 제한하여 Purchasing.ProductVendor 테이블을 참조합니다.
USE AdventureWorks2022;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2
WHERE ProductID IN
(SELECT ProductID
FROM Purchasing.ProductVendor
WHERE BusinessEntityID = 1540);
GO
다음은 조인을 사용하는 동일한 UPDATE 문입니다.
USE AdventureWorks2022;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2
FROM Production.Product AS p
INNER JOIN Purchasing.ProductVendor AS pv
ON p.ProductID = pv.ProductID AND BusinessEntityID = 1540;
GO
동일한 테이블이 다른 하위 쿼리에서 참조되는 경우 명확성을 위해 대상 테이블의 별칭을 사용합니다.
USE AdventureWorks2022;
GO
UPDATE p
SET ListPrice = ListPrice * 2
FROM Production.Product AS p
INNER JOIN Purchasing.ProductVendor AS pv
ON p.ProductID = pv.ProductID AND BusinessEntityID = 1540;
GO
비교 연산자가 있는 하위 쿼리
하위 쿼리는 비교 연산자(=, < >, >, > =, <, ! >, ! < 또는 < =) 중 하나로 시작할 수 있습니다.
수정되지 않은 비교 연산자(ANY 또는 ALL가 뒤에 오지 않는 비교 연산자)로 도입된 하위 쿼리는 IN로 도입된 하위 쿼리처럼 값의 목록이 아닌 단일 값을 반환해야 합니다. 이러한 하위 쿼리가 둘 이상의 값을 반환하면 SQL Server는 오류 메시지를 표시합니다.
수정되지 않은 비교 연산자로 도입된 하위 쿼리를 사용하려면 데이터와 문제의 특성에 대해 충분히 잘 알고 있어야 하위 쿼리가 정확히 하나의 값을 반환한다는 것을 알 수 있습니다.
예를 들어 각 영업 담당자가 하나의 영업 지역만 담당한다고 가정하고 Linda Mitchell이 담당하는 지역에 있는 고객을 찾고자 하는 경우 간단한 = 비교 연산자로 도입된 하위 쿼리를 사용하여 문을 작성할 수 있습니다.
USE AdventureWorks2022;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID =
(SELECT TerritoryID
FROM Sales.SalesPerson
WHERE BusinessEntityID = 276);
GO
그러나 Linda Mitchell가 둘 이상의 판매 지역에 적용되는 경우 오류 메시지가 표시됩니다. = 비교 연산자 대신 IN 수식을 사용할 수 있습니다(=ANY도 사용 가능).
수정되지 않은 비교 연산자를 사용하여 도입된 하위 쿼리는 단일 값을 반환하기 때문에 집계 함수를 포함하는 경우가 많습니다. 예를 들어 다음 문은 정가가 평균 정가보다 큰 모든 제품의 이름을 찾습니다.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >
(SELECT AVG (ListPrice)
FROM Production.Product);
GO
수정되지 않은 비교 연산자로 시작하는 하위 쿼리는 단일 값을 반환해야 하므로 GROUP BY 또는 HAVING 절이 단일 값을 반환하지 않으면 하위 쿼리에 GROUP BY 또는 HAVING 절을 포함할 수 없습니다. 예를 들어 다음 쿼리는 ProductSubcategoryID 14에 있는 가장 저렴한 제품보다 가격이 높은 제품을 찾습니다.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >
(SELECT MIN (ListPrice)
FROM Production.Product
GROUP BY ProductSubcategoryID
HAVING ProductSubcategoryID = 14);
GO
ANY, SOME, 또는 ALL에 의해 수정된 비교 연산자
하위 쿼리를 도입하는 비교 연산자는 키워드(keyword) ALL 또는 ANY로 수정할 수 있습니다. SOME는 ANY에 해당하는 ISO 표준입니다. 이러한 비교 연산자에 대한 자세한 내용은 SOME | ANY를 참조하세요.
수정된 비교 연산자를 사용하여 도입된 하위 쿼리는 0개 이상의 값 목록을 반환하며 GROUP BY 또는 HAVING 절을 포함할 수 있습니다. 이러한 하위 쿼리는 EXISTS로 다시 작성할 수 있습니다.
> 비교 연산자를 예로 들면 > ALL는 모든 값보다 큰 값을 의미합니다. 즉, 최대값보다 큰 것을 의미합니다. 예를 들어 > ALL (1, 2, 3)는 3보다 큰 것을 의미합니다. > ANY는 하나 이상의 값, 즉 최소값보다 큰 값을 의미합니다. 따라서 > ANY (1, 2, 3)는 1보다 크다는 의미입니다.
> ALL가 있는 하위 쿼리의 행이 외부 쿼리에 지정된 조건을 충족하려면 하위 쿼리를 소개하는 열의 값이 하위 쿼리에서 반환된 값 목록의 각 값보다 커야 합니다.
마찬가지로 > ANY가 있는 행이 외부 쿼리에 지정된 조건을 만족시키려면 하위 쿼리를 시작하는 열의 값이 하위 쿼리에서 반환되는 값 목록에서 하나 이상의 값보다 커야 합니다.
다음은 ANY로 수정된 비교 연산자로 시작하는 하위 쿼리를 보여 주는 예입니다. 이 쿼리에서는 가격이 제품 하위 범주의 최대 가격보다 크거나 동일한 제품을 찾습니다.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >= ANY
(SELECT MAX (ListPrice)
FROM Production.Product
GROUP BY ProductSubcategoryID);
GO
각 제품 하위 범주에 대해 내부 쿼리는 최대 정가를 찾습니다. 외부 쿼리는 이러한 모든 값을 살펴보고 개별 제품의 정가가 제품 하위 범주의 최대 정가보다 크거나 같은지 결정합니다. ANY를 ALL로 변경하면 가격이 내부 쿼리에서 반환된 모든 가격보다 크거나 동일한 제품만 반환됩니다.
하위 쿼리에서 값을 반환하지 않으면 전체 쿼리에서 값을 반환하지 못합니다.
= ANY 연산자는 IN에 해당합니다. 예를 들어 Adventure Works Cycles가 만드는 모든 휠 제품의 이름을 찾으려면 IN 또는 = ANY를 사용하면 됩니다.
--Using = ANY
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID = ANY
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE Name = 'Wheels');
GO
--Using IN
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE Name = 'Wheels');
GO
각 쿼리의 결과 집합은 다음과 같습니다.
Name
--------------------------------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel
(14 row(s) affected)
<> ANY 연산자, 단 NOT IN과 다름:
<> ANY은 not = a, 또는 not = b, 또는 not = c를 의미함NOT IN은 not = a, not = b, not = c를 의미함<> ALL은NOT IN와 같은 의미
예를 들어 다음 쿼리는 영업 직원이 담당하지 않는 지역에 있는 고객을 찾습니다.
USE AdventureWorks2022;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID <> ANY
(SELECT TerritoryID
FROM Sales.SalesPerson);
GO
결과에는 판매 지역이 NULL인 고객을 제외한 모든 고객이 포함됩니다. 고객에게 할당된 모든 지역은 영업 담당자가 적용하기 때문입니다. 내부 쿼리가 영업 직원의 담당 영업 지역을 모두 찾은 후 외부 쿼리가 각 지역마다 해당 지역에 없는 고객을 찾습니다.
이와 같은 이유로 이 쿼리에서 NOT IN을 사용하면 결과에 아무 고객도 포함되지 않습니다.
<> ALL에 해당하는 NOT IN 연산자를 사용해도 동일한 결과를 얻을 수 있습니다.
EXISTS가 있는 하위 쿼리
키워드(keyword) EXISTS로 하위 쿼리가 도입되면 하위 쿼리는 존재 테스트의 기능을 합니다. 외부 쿼리의 WHERE 절은 하위 쿼리에서 반환된 행이 있는지 여부를 테스트합니다. 하위 쿼리는 실제로 데이터를 생성하지 않고 TRUE 또는 FALSE 값을 반환합니다.
EXISTS도 도입된 하위 쿼리에는 다음 구문이 있습니다. WHERE [NOT] EXISTS (subquery)
다음 쿼리는 Wheels 하위 범주에 있는 모든 제품의 이름을 찾습니다.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE EXISTS
(SELECT *
FROM Production.ProductSubcategory
WHERE ProductSubcategoryID =
Production.Product.ProductSubcategoryID
AND [Name] = 'Wheels');
GO
결과 집합은 다음과 같습니다.
Name
--------------------------------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel
(14 row(s) affected)
이 쿼리의 결과를 이해하려면 각 제품의 이름을 차례로 고려합니다. 이 값을 통해 하위 쿼리에서 하나 이상의 행을 반환하는지, 즉, 쿼리에서 존재 테스트의 결과가 TRUE인지 확인합니다.
EXISTS로 시작하는 하위 쿼리는 다음과 같은 점에서 다른 하위 쿼리와 다릅니다.
- 키워드(keyword)
EXISTS앞에 열 이름, 상수 또는 기타 식이 오지 않습니다. EXISTS에 의해 도입된 하위 쿼리의 선택 목록은 거의 항상 별표(*)로 구성됩니다. 하위 쿼리에 지정된 조건을 충족하는 행이 있는지 테스트하기 때문에 열 이름을 나열할 이유가 없습니다.
EXISTS 키워드가 중요한 이유는 하위 쿼리 없이는 대체할 수 있는 공식이 없는 경우가 많기 때문입니다. EXISTS를 사용하여 만든 일부 쿼리는 다른 방식으로 표시할 수 없지만 IN을 사용하거나 ANY 또는 ALL로 수정된 비교 연산자를 사용하여 유사한 결과를 얻을 수 있는 쿼리가 많습니다.
예를 들어 앞의 쿼리는 IN을 사용하여 다음과 같이 표현할 수 있습니다.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE [Name] = 'Wheels');
GO
NOT EXISTS가 있는 하위 쿼리
NOT EXISTS는 하위 쿼리에서 반환되는 행이 없는 경우에 WHERE 절이 충족된다는 점을 제외하면 EXISTS와 비슷하게 작동합니다.
예를 들어 휠 하위 범주에 없는 제품의 이름을 찾으려면 다음을 수행합니다.
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE NOT EXISTS
(SELECT *
FROM Production.ProductSubcategory
WHERE ProductSubcategoryID =
Production.Product.ProductSubcategoryID
AND [Name] = 'Wheels');
GO
식 대신 사용되는 하위 쿼리
Transact-SQL에서 하위 쿼리는 ORDER BY 목록을 제외하고 SELECT, UPDATE, INSERT 및 DELETE 문에서 식을 사용할 수 있는 모든 곳에서 대체할 수 있습니다.
다음 예제에서는 이 향상된 기능을 사용하는 방법을 보여 줍니다. 다음 쿼리는 모든 산악용 자전거의 가격, 평균 가격 및 각 산악용 자전거의 가격과 평균 가격 간의 차이를 검색합니다.
USE AdventureWorks2022;
GO
SELECT [Name], ListPrice,
(SELECT AVG(ListPrice) FROM Production.Product) AS Average,
ListPrice - (SELECT AVG(ListPrice) FROM Production.Product)
AS Difference
FROM Production.Product
WHERE ProductSubcategoryID = 1;
GO