Mssql where절 서브쿼리 - mssql wherejeol seobeukwoli

오늘은 MSSQL 서브 쿼리(SubQuery) 사용법에 대해서 알아보고
추가로 이해를 돕기 위한 예제까지 다루어 보도록 하겠습니다.

목차
1.MSSQL 서브쿼리(SubQuery) 란?
2.MSSQL 서브쿼리(SubQuery) 문법
3.MSSQL 서브쿼리(SubQuery) 사용법

1.MSSQL 서브쿼리(SubQuery) 란?
하나의 쿼리문 안에 포함되어 있는 또 하나의 쿼리문을 말합니다.
보통 각각의 테이블은 데이터베이스에 접근하는 속도를 향상하기 위해서 
하나의 테이블에 정보를 다 넣지 않고 다른 테이블과 관계를 맺어 만들어집니다.

그러므로 다른 테이블에 있는 정보들을 가져와서 사용하고자 할 때, 서브 쿼리가 필요합니다.
간단하게 쿼리문안에 쿼리문을 또 쓰는 방식입니다.

[주의사항]
서브쿼리 사용 시에는 괄호를 사용해야 한다.
정렬을 하는 order by절에는 사용이 불가하다.
연산자 오른쪽에 써야한다.

2.MSSQL 서브쿼리(SubQuery) 문법
#서브 쿼리를 이용한 조회하기
SELECT 
    (SELECT COUNT(*) FROM 테이블명 1) AS t1,
    (SELECT COUNT(*) FROM 테이블명2) AS t2;
FROM 테이블명

#서브쿼리를 이용한 업데이트
UPDATE 테이블명 SET 열명 = (SELECT MAX(열명) FROM 테이블명);

#서브 쿼리를 이용한 인설트
INSERT INTO 테이블명 VALUES (
    (SELECT COUNT(*) FROM 테이블명 1),
    (SELECT COUNT(*) FROM 테이블명2));

#'열명'에 해당하는 값중 가장 큰 값을 삭제하는 쿼리
DELETE FROM 테이블명 
WHERE 열명 = (SELECT max(열명) FROM 테이블명));

3.MSSQL 서브쿼리(SubQuery) 사용법
#서브 쿼리를 이용한 조회하기(select 절)
SELECT절에서 서브 쿼리를 사용할 경우 서브 쿼리의 결과값은 하나만 출력되어야 한다.
SELECT
name,
age,
(SELECT max(age) FROM bebeya01) AS 
FROM bebeya02

#서브쿼리를 이용한 조회하기(from 절)
From에 테이블 대신 서브 쿼리를 사용하면 서브 쿼리에서 조회된 값을 테이블처럼 사용이 가능합니다.
SELECT
name,
age
FROM
(SELECT * FROM  bebeya01)

#서브쿼리를 이용한 조회하기(where 절)
SELECT
name,
age
FROM bebeya01
WHERE age = (select max(age) from bebeya02)

MSSQL 서브 쿼리(SubQuery) 사용법에 대한 포스팅을 마치겠습니다.

주요 콘텐츠로 건너뛰기

이 브라우저는 더 이상 지원되지 않습니다.

최신 기능, 보안 업데이트, 기술 지원을 이용하려면 Microsoft Edge로 업그레이드하세요.

하위 쿼리(SQL Server)

• 아티클
• 09/26/2022
• 읽는 데 36분 걸림

이 문서의 내용

적용 대상: SQL Server(지원되는 모든 버전) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW)

하위 쿼리는 SELECT, INSERT, UPDATE 또는 DELETE 문이나 다른 하위 쿼리 내부에 중첩된 쿼리입니다.

하위 쿼리는 식이 허용되는 모든 위치에서 사용할 수 있습니다. 이 예제에서 하위 쿼리는 문에서 MaxUnitPrice라는 열 식으로 SELECT 사용됩니다.

USE AdventureWorks2016;
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 AdventureWorks2016;
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 목록 구성 요소가 포함된 일반 SELECT 쿼리
• 하나 이상의 테이블이나 뷰 이름이 포함된 일반 FROM 절
• WHERE 절(옵션)
• GROUP BY 절(옵션)
• HAVING 절(옵션)

하위 쿼리의 SELECT 쿼리는 항상 괄호로 묶습니다. 또는 FOR BROWSE 절을 COMPUTE 포함할 수 없으며 TOP 절도 지정된 경우에만 절을 포함 ORDER BY 할 수 있습니다.

하위 쿼리는 외부 SELECT, INSERT, UPDATE 또는 DELETE 문의 WHERE 또는 HAVING 절이나 다른 하위 쿼리 내부에 중첩될 수 있습니다. 최대 32개 수준까지 중첩이 가능하지만 이 값은 사용 가능한 메모리 및 쿼리에 있는 다른 식의 복잡성에 따라 달라지며 개별 쿼리에서 32개 수준까지 중첩을 지원하지 않을 수도 있습니다. 하위 쿼리는 단일 값을 반환할 경우 식을 사용할 수 있는 모든 위치에 나타날 수 있습니다.

테이블이 외부 쿼리가 아닌 하위 쿼리에만 표시되는 경우 해당 테이블의 열을 출력(외부 쿼리의 선택 목록)에 포함할 수 없습니다.

하위 쿼리가 포함된 문은 다음 중 한 가지 형식을 취합니다.

• 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 가 뒤따를 수 없음)는 포함 및 HAVING 절을 포함 GROUP BY 할 수 ALL없습니다.
• ' DISTINCT GROUP BY'를 포함하는 하위 쿼리에는 키워드를 사용할 수 없습니다.
• COMPUTE 및 INTO 절을 지정할 수 없습니다.
• ORDER BY은 TOP을 함께 지정해야만 지정할 수 있습니다.
• 하위 쿼리를 사용하여 만든 뷰는 업데이트할 수 없습니다.
• EXISTS로 시작하는 하위 쿼리의 선택 목록은 규칙에 따라 단일 열 이름 대신 별표(*)로 구성됩니다. EXISTS로 시작하는 하위 쿼리는 존재 테스트를 만들며 데이터 대신 TRUE 또는 FALSE를 반환하므로 EXISTS로 시작하는 하위 쿼리에 대한 규칙은 표준 선택 목록의 규칙과 동일합니다.

하위 쿼리에서 열 이름 한정

다음 예제에서는 외부 쿼리의 WHERE 절에 있는 BusinessEntityID 열이 외부 쿼리의 FROM 절에 있는 테이블 이름(Sales.Store)으로 암시적으로 한정됩니다. 하위 쿼리의 SELECT 목록에서 CustomerID에 대한 참조는 하위 쿼리의 FROM 절, 즉 Sales.Customer 테이블로 한정됩니다.

USE AdventureWorks2016;
GO
SELECT [Name]
FROM Sales.Store
WHERE BusinessEntityID NOT IN
    (SELECT CustomerID
     FROM Sales.Customer
     WHERE TerritoryID = 5);
GO

일반적으로 문의 열 이름은 같은 수준의 FROM 절에서 참조하는 테이블로 암시적으로 한정됩니다. 하위 쿼리 절에서 FROM 참조하는 테이블에 열이 없는 경우 외부 쿼리의 절에서 FROM 참조되는 테이블에 의해 암시적으로 정규화됩니다.

이러한 암시적 가정이 지정된 쿼리의 모양은 다음과 같습니다.

USE AdventureWorks2016;
GO
SELECT [Name]
FROM Sales.Store
WHERE Sales.Store.BusinessEntityID NOT IN
    (SELECT Sales.Customer.CustomerID
     FROM Sales.Customer
     WHERE TerritoryID = 5);
GO

테이블 이름을 명시적으로 지정하는 것은 잘못된 일이 아닙니다. 명시적 자격으로 테이블 이름에 대한 암시적 가정을 항상 재정의할 수 있습니다.

중요

하위 쿼리에서 참조하는 열이 하위 쿼리의 FROM 절에서 참조하는 테이블에는 없지만 외부 쿼리의 FROM 절에서 참조하는 테이블에는 있는 경우 쿼리가 오류 없이 실행됩니다. SQL Server 하위 쿼리의 열을 외부 쿼리의 테이블 이름으로 암시적으로 한정합니다.

다중 중첩 수준

하위 쿼리에는 하나 이상의 하위 쿼리가 포함될 수 있습니다. 또한 문에 원하는 수만큼 하위 쿼리를 중첩시킬 수 있습니다.

다음 쿼리는 영업 사원이기도 한 직원의 이름을 찾습니다.

USE AdventureWorks2016;
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 AdventureWorks2016;
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 AdventureWorks2016;
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 AdventureWorks2016;
GO
SELECT Bonus
FROM Sales.SalesPerson
WHERE BusinessEntityID = 285;
GO

결과는 0.00(Syed Abbas 영업 사원이 아니므로 보너스를 받지 못함)이므로 외부 쿼리는 다음으로 평가됩니다.

USE AdventureWorks2016;
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 AdventureWorks2016;
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 AdventureWorks2016;
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 AdventureWorks2016;
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 AdventureWorks2016;
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.

USE AdventureWorks2016;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN ('17');
GO

이 경우 하위 쿼리 대신 조인을 사용하면 결과에 둘 이상의 테이블에 포함된 열을 보여 줄 수 있다는 점이 다릅니다. 예를 들어 결과에 제품 하위 범주의 이름을 포함하려면 조인 버전을 사용해야 합니다.

USE AdventureWorks2016;
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)

다음 쿼리는 20개 이상의 Adventure Works Cycles 제품을 주문하고 평균 배달 리드 타임이 16일 미만으로 신용도가 좋은 모든 공급업체의 이름을 검색합니다.

USE AdventureWorks2016;
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 AdventureWorks2016;
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 AdventureWorks2016;
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

이 문을 조인으로 변환할 수 없습니다. 유사하지 않은 조인은 다른 의미를 가집니다. 완성된 자전거가 아닌 일부 하위 범주에 있는 제품의 이름을 찾습니다.

, DELETE및 INSERT 문의 하위 쿼리 UPDATE

UPDATE, DELETE, INSERT 및 SELECT DML(데이터 조작 언어) 문에 하위 쿼리가 중첩될 수 있습니다.

다음 예에서는 Production.Product 테이블의 ListPrice 열 값을 두 배로 만듭니다. WHERE 절의 하위 쿼리는 Product 테이블에서 업데이트되는 행을 BusinessEntity 1540이 제공하는 행으로만 제한하여 Purchasing.ProductVendor 테이블을 참조합니다.

USE AdventureWorks2016;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2
WHERE ProductID IN
    (SELECT ProductID
     FROM Purchasing.ProductVendor
     WHERE BusinessEntityID = 1540);
GO

다음은 조인을 사용하여 동일한 기능을 수행하는 UPDATE 문입니다.

USE AdventureWorks2016;
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 AdventureWorks2016;
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 AdventureWorks2016;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID =
    (SELECT TerritoryID
     FROM Sales.SalesPerson
     WHERE BusinessEntityID = 276);
GO

그러나 Linda Mitchell이 두 개 이상의 판매 지역을 담당할 경우 오류 메시지가 나타납니다. 이런 경우 = 비교 연산자 대신 IN 공식화(또는 =ANY)를 사용할 수 있습니다.

수정되지 않은 비교 연산자로 시작하는 하위 쿼리는 단일 값을 반환하므로 집계 함수가 포함되는 경우가 많습니다. 예를 들어 다음 문은 제품 가격이 평균 가격보다 비싼 모든 제품의 이름을 찾습니다.

USE AdventureWorks2016;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >
    (SELECT AVG (ListPrice)
     FROM Production.Product);
GO

수정되지 않은 비교 연산자를 사용하여 도입된 하위 쿼리는 단일 값을 반환해야 하므로 또는 절 자체가 단일 값을 반환한다는 것을 알지 GROUP BY 못하면 포함하거나 HAVINGHAVING 절을 포함 GROUP BY 할 수 없습니다. 예를 들어 다음 쿼리는 ProductSubcategoryID 14에 있는 최저가 제품보다 가격이 높은 제품을 찾습니다.

USE AdventureWorks2016;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >
    (SELECT MIN (ListPrice)
     FROM Production.Product
     GROUP BY ProductSubcategoryID
     HAVING ProductSubcategoryID = 14);
GO

에 의해 수정된 ANY비교 연산자 또는 SOMEALL

하위 쿼리를 도입하는 비교 연산자는 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 AdventureWorks2016;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >= ANY
    (SELECT MAX (ListPrice)
     FROM Production.Product
     GROUP BY ProductSubcategoryID);
GO

각각의 Product 하위 범주마다 내부 쿼리는 최대 가격을 찾습니다. 외부 쿼리는 이러한 값 모두를 찾고 Product 하위 범주의 최대 가격보다 크거나 동일한 개별 제품의 가격을 확인합니다. ANY를 ALL로 변경하면 가격이 내부 쿼리에서 반환된 모든 가격보다 크거나 동일한 제품만 반환됩니다.

하위 쿼리에서 값을 반환하지 않으면 전체 쿼리에서 값을 반환하지 못합니다.

= ANY 연산자는 IN에 해당합니다. 예를 들어 IN 또는 = ANY를 사용하여 Adventure Works Cycles에서 만드는 모든 바퀴 제품의 이름을 찾을 수 있습니다.

--Using = ANY
USE AdventureWorks2016;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID = ANY
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels');
GO

--Using IN
USE AdventureWorks2016;
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 AdventureWorks2016;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID <> ANY
    (SELECT TerritoryID
     FROM Sales.SalesPerson);
GO

고객에게 할당된 모든 지역을 영업 직원이 담당하기 때문에 결과에는 영업 지역이 NULL인 고객을 제외한 모든 고객이 포함됩니다. 내부 쿼리는 영업 사원이 다루는 모든 판매 지역을 찾은 다음, 각 지역에 대해 외부 쿼리는 하나에 없는 고객을 찾습니다.

이와 같은 이유로 이 쿼리에서 NOT IN을 사용하면 결과에 아무 고객도 포함되지 않습니다.

<> ALL에 해당하는 NOT IN 연산자를 사용해도 동일한 결과를 얻을 수 있습니다.

EXISTS가 있는 하위 쿼리

하위 쿼리가 EXISTS 키워드로 시작하면 존재 여부를 테스트할 수 있습니다. 외부 쿼리의 WHERE 절은 하위 쿼리에서 반환된 행이 있는지 여부를 테스트합니다. 하위 쿼리는 실제로 데이터를 생성하지 않습니다. 의 값을 TRUE 반환합니다 FALSE.

EXISTS로 시작하는 하위 쿼리는 다음 구문을 사용합니다.

WHERE [NOT] EXISTS (subquery)

다음 쿼리는 Wheels 하위 범주에 있는 모든 제품의 이름을 검색합니다.

USE AdventureWorks2016;
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로 시작하는 하위 쿼리는 다음과 같은 점에서 다른 하위 쿼리와 다릅니다.

• 키워드 EXISTS 앞에 열 이름, 상수 또는 기타 식이 없습니다.
• EXISTS로 시작하는 하위 쿼리의 SELECT 목록은 대부분 별표(*)로 구성됩니다. 하위 쿼리에 지정된 조건을 충족하는 행이 있는지 테스트하기 때문에 열 이름을 나열할 이유가 없습니다.

대부분 하위 쿼리를 사용하지 않는 대체 구문이 없으므로 EXISTS 키워드는 중요합니다. EXISTS를 사용하여 만든 일부 쿼리는 다른 방식으로 표현할 수 없지만 많은 쿼리에서 비슷한 결과를 사용하거나 수정한 ANYALL 비교 연산자를 사용할 IN 수 있습니다.

예를 들어 앞의 쿼리는 IN을 사용하여 다음과 같이 표현할 수 있습니다.

USE AdventureWorks2016;
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 AdventureWorks2016;
GO
SELECT [Name]
FROM Production.Product
WHERE NOT EXISTS
    (SELECT *
     FROM Production.ProductSubcategory
     WHERE ProductSubcategoryID =
            Production.Product.ProductSubcategoryID
        AND [Name] = 'Wheels');
GO

식 대신 사용되는 하위 쿼리

Transact-SQL에서 하위 쿼리는 목록을 제외한 식, INSERTUPDATE문 및 DELETE 문에서 사용할 수 있는 SELECTORDER BY 모든 위치에서 대체될 수 있습니다.

다음은 이러한 향상된 기능을 사용하는 방법을 보여 주는 예입니다. 다음 쿼리는 모든 산악용 자전거의 가격, 평균 가격 및 각 산악용 자전거의 가격과 평균 가격 간의 차이를 검색합니다.

USE AdventureWorks2016;
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

참고 항목

Syntax

• IN(Transact-SQL)
• EXISTS(Transact-SQL)
• ALL(Transact-SQL)
• SOME | ANY(Transact-SQL)
• IS [NOT] DISTINCT FROM(Transact-SQL)
• 조인
• 비교 연산자(Transact-SQL)

쿼리 성능 개념

• 쿼리 처리 아키텍처 가이드
• 쿼리 저장소에 대한 모범 사례
• SQL 데이터베이스의 지능형 쿼리 처리
• 카디널리티 추정(SQL Server)

추가 리소스

추가 리소스

이 문서의 내용