서브쿼리(Sub Query)

이번에 다룰 주제는 서브 쿼리 또는 하위 쿼리라고 하는 놈을 다루어 보도록 하겠습니다.

서브 쿼리는 SELECT, INSERT, UPDATE 또는 DELETE 문이나 다른 서브 쿼리의 내부에 중첩해서 사용하는 쿼리를 말합 니다.

서브 쿼리를 이용해서 표현하는 구문들은 대부분 서브 쿼리를 이용하지 않고 조인을 이용해서 해결할 수 있습니다. 서브 쿼리를 굳이 이용하지 않더라고 조인을 통해서 서브 쿼리를 대체할 수 있는 경우가 대부분입니다.

그러면 조인을 이용해서 쿼리문을 만들 수 있는데 왜 서브 쿼리를 알아야 할까요? 이론적인 설명은 다 제쳐두고 필자가 서 브 쿼리를 사용해야 하는 이유는 가독성 때문이라고 생각합니다.

어느 정도 규모가 있는 기업체들에서는 대부분 사내에서 ERP나 그 비슷한 관리 프로그램들을 운영하고 있는데 이런 ERP 프로그램들의 데이터베이스는 수백개의 테이블을 기본적으로 사용하고, 자료의 조회를 위해서는 10개 이상의 테이블을 연결해서 데이터를 가져오는 경우가 상당히 빈번하게 일어나게 됩니다. 이럴 경우엔 필히 조인을 이용해야 하는데 이런 조 인을 많이 하면 할수록 코드는 이해하기 어려워지고 가독성이 현저하게 떨어지게 됩니다. 이 경우 서브 쿼리를 이용한다면 조인의 횟수를 많이 줄일수 있고 가독성을 향상할 수 있게 됩니다.

서브 쿼리에서 SELECT 문을 사용할 경우에는 항상 괄호로 묶어서 사용하게 됩니다. 그러면 실제 서브 쿼리를 사용하려면 어떻게 해야 하는 지 예를 들어 설명해 보도록 하겠습니다.

예제로 사용된 관계 설정

 

Customer 테이블은 고객 정보가 들어있고, Orders 테이블은 주문 정보, Order_Detail 테이블은 주문 상세 내역, Product 테이블은 제품에 대한 정보가 들어 있고 Categories 테이블은 생산품의 분류(데스크탑PC, 노트북등)가 들어 있 습니다.

각 테이블들은 서로 1:다의 관계를 가지고 있다는 것도 확인할 수 있습니다.

 

Customer 테이블의 일부 데이터 내용 입니다.

 

Orders 테이블의 일부 데이터 내용 입니다.

 

Order_Details 테이블의 일부 데이터 내용입니다.

 

Products 테이블이 일부 데이터 내용입니다.

예) 2017년 9월1일에 배송된 모든 주문날짜(OrderDate)와 배송날짜(ShipDate), 그리고 주문을 한 고객명을 같이 표시 하시오.

-- 서브쿼리를 사용하지 않고 단순 조인을 통해 출력결과를 나타내기 SELECT (Customers.FirstName + ' ' + Customers.LastName) AS 고객명, Orders.OrderDate, Orders.ShipDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Orders.OrderDate = '2017-09-01'

 

위의 예제는 서브 쿼리를 이용하지 않고 단순 조인을 통해서 출력 결과를 만들어낸 방법입니다.

Customers.LastName FROM Customers Where Customers.CustomerID = Orders.CustomerID) AS 고객명, Orders.OrderDate, Orders.ShipDate FROM Orders WHERE Orders.OrderDate = '2017-09-01'

 

위의 예제는 서브 쿼리를 이용하여 똑같은 결과를 만들어낸 방법입니다. SELECT 문에서 다시 한번 SELECT 문을 사용하 고 괄호를 이용해서 묶어서 내부적으로 쿼리를 사용했기 때문에 위와 같은 쿼리를 서브 쿼리라고 하게 됩니다.

위의 예제를 보면 알 수 있듯이 서브 쿼리를 사용하게 되면 서브 쿼리 안에서 조인을 이용하게 되므로 외부에서는 따로 조 인을 할 필요가 없이 처리가 가능한 경우가 많이 있습니다. 이렇기 때문에 테이블의 연결이 많아질수록 서브 쿼리를 이용 하게 되면 조인의 횟수를 상당히 줄일 수 있게 되는 장점이 많이 있습니다.

하지만 서브 쿼리를 사용하다 보면 서브 쿼리를 사용하는 데 제약들이 있기 때문에 이런 부분은 유의해서 사용해야 합니 다. 위의 쿼리문에서도 서브 쿼리의 출력 결과가 외부 쿼리와 상관이 없는 출력물을 만들어 낸다면 이 쿼리는 작동을 하지 않게 됩니다.

서브 쿼리를 사용하다 보면 마주치는 몇가지 에러 현상들을 알아보도록 하겠습니다.

select orders.OrderNumber, orders.OrderDate, orders.ShipDate, (select customers.FirstName, customers.LastName from Customers where customers.CustomerID=orders.CustomerID) from Orders where orders.OrderDate = '2017-09-01' -- 하위 쿼리를 EXISTS로 정의하지 않은 경우에는 SELECT 목록에서 식을 하나만 지 정할 수 있습니다.

 

위와 같이 서브 쿼리문을 만들고 실행을 하게 되면 쿼리문 아래에서 보는 것과 같은 에러메시지가 나오면서 실행이 되지 않습니다. 그 이유는 비교 연산자로 시작하는 서브 쿼리의 선택 목록에는 식이나 열 이름을 한개만 포함할 수 있기 때문입니다. 위의 쿼리문을 제대로 작동하도록 수정하려면 열을 하나만 지정하거나, 두개 이상의 열을 하나로 묶어서 출력하는 방법을 사용해야 합니다.

select orders.OrderNumber, orders.OrderDate, orders.ShipDate, (select customers.LastName from Customers) from Orders where orders.OrderDate = '2017-09-01' -- 하위 쿼리에서 값을 둘 이상 반환했습니다. -- 하위 쿼리 앞 에 =, !=, <, <=, >, >= 등이 오거나 -- 하위 쿼리가 하나의 식으로 사용된 경우에는 여러 값을 반환할 수 없습니다.

 

위의 쿼리문은 서브 쿼리에서 하나의 열만 포함되도록 작성되어 있습니다. 그런데도 에러메시지가 나오면서 실행은 되지 않습니다. 사실 위의 에러메시지는 정확한 설명이 아닙니다. 실제 에러메시지 나오는 이유는 서브 쿼리가 외부 쿼리와 아 무런 연관성을 가지지 못하기 때문에 발생하는 경우입니다.

이외에도 서브 쿼리를 사용하는 데에는 여러가지 제약들이 있습니다.

▶ GROUP BY 가 포함된 서브 쿼리에는 Distinct 키워드를 사용할 수 없습니다.

▶ 서브 쿼리를 사용하여 만든 뷰는 업데이트할 수 없습니다.

▶ COMPUTE 및 INTO 절은 지정할 수 없습니다.

 

그러면 서브 쿼리를 활용하는 예제를 좀 더 알아보도록 하겠습니다.

-- 고객이름을 선택하고 Customers 테이블의 고객에 대한 주문수를 출력하시오. SELECT (Customers.FirstName + ' '

+ Customers.LastName) AS 고객명, (SELECT COUNT(*) FROM Orders WHERE Orders.CustomerID =

Customers.CustomerID) AS '주문건수' FROM Customers

 

위의 예는 서브 쿼리를 이용해서 각 고객들의 주문 건수(Count(*) 함수를 이용함)를 출력해주는 예제입니다.

-- 고객 이름을 선택하고 Customers 테이블에서 이 고객의 주문테이블에서 가장 최근 주문날짜를 출력하시오. SELECT Customers.FirstName,Customers.LastName, (SELECT MAX(OrderDate) FROM Orders WHERE Orders.CustomerID =Customers.CustomerID) AS LastOrderDate FROM Customers 

 

위의 예는 서브 쿼리를 이용해서 각 고객들의 최근 주문날짜를 출력해주는 예제입니다. 날짜 열에 MAX 함수를 이용하면 가장 최근 날짜를 가져올 수 있다는 것을 확인해 보시기 바랍니다.

 

ANY, SOME, ALL의 사용

 

서브 쿼리를 시작하는 비교 연산자로 ANY, SOME, ALL 등을 사용할 수 있습니다.

비교 연산자 >ALL 은 모든 값보다 크다는 것을 의미합니다. 예를 들어 >ALL (10,11,12,13) 은 13 보다 크다는 것을 의미 합니다.

>ANY 는 적어도 하나의 값보다는 크다는 것을 의미합니다. 따라서 >ANY (10, 11, 12, 13) 은 10 보다는 크다는 것을 의 미합니다.

SOME은 ANY와 동의어입니다. ISO 표준에서는 SOME을 사용하게 됩니다.

-- 노트북 보다 가격이 비싼 모든 데스크탑PC를 찾으시오 SELECT Products.ProductName,Products.RetailPrice FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID WHERE Categories.CyDescription = '데스크탑PC' AND Products.RetailPrice > ALL (SELECT Products.RetailPrice FROM

 

Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID WHERE Categories.CyDescription = '노트북')

 

서브 쿼리의 시작에서 ALL 연산자를 사용했습니다. 위와 같이 서브 쿼리를 사용하게 되면 노트북의 모든 가격 리스트보다 더 비싼 데스크탑PC의 리스트를 출력할 수 있습니다.

 

--데스크탑PC나 노트북을 주문한 고객을 표시하시오. SELECT Customers.CustomerID, Customers.FirstName, Customers.LastName FROM Customers WHERE Customers.CustomerID = ANY (SELECT Orders.CustomerID FROM ((Orders INNER JOIN Order_Details ON Orders.OrderNumber = Order_Details.OrderNumber) INNER JOIN Products ON Products.ProductNumber = Order_Details.ProductNumber) INNER JOIN Categories ON Categories.CategoryID = Products.CategoryID WHERE Categories.CyDescription = '데스크탑PC' OR Categories.CyDescription = '노트북')

 

위의 예제는 ANY 연산자를 이용해서 서브 쿼리를 사용한 방법입니다.

EXISTS의 사용

서브 쿼리가 Exists 키워드로 시작하면 존재 여부를 체크할 수 있습니다. 외부 쿼리의 WHERE 절은 서브 쿼리에서 반환된 행이 있는 지를 테스트 합니다. 이 경우 서브 쿼리는 실제로 데이터를 생성하지 않고 TRUE 또는 FALSE 값을 반환합니다. 그렇기 때문에 TRUE나 FALSE 값으로 평가될 수 없는 서브 쿼리는 에러메시지가 나오면서 실행이 되지 않게 됩니다.

-- 데스크탑PC를 주문한 모든 고객을 찾으시오 SELECT Customers.CustomerID, Customers.FirstName, Customers.LastName FROM Customers WHERE EXISTS (SELECT * FROM (Orders INNER JOIN Order_Details ON Orders.OrderNumber = Order_Details.OrderNumber) INNER JOIN Products ON Products.ProductNumber

= Order_Details.ProductNumber WHERE Products.CategoryID = 1 AND Orders.CustomerID = Customers.CustomerID)

 

위의 예는 EXISTS 키워드를 이용해서 데스크탑PC(CategoryID = 1)를 주문한 모든 고객을 찾는 방법입니다.

 

IN 키워드의 사용

 

IN (NOT IN) 키워드를 사용하여 서브 쿼리의 열을 제한할 수 있습니다.

-- ProudctNumber 3(PC No. 1000)을 주문한 모든 고객을 나열하시오. SELECT Customers.FirstName, Customers.LastName FROM Customers WHERE Customers.CustomerID IN (SELECT CustomerID FROM Orders WHERE Orders.CustomerID IN (SELECT CustomerID FROM Orders INNER JOIN Order_Details ON Order_Details.OrderNumber=Orders.OrderNumber WHERE Order_Details.ProductNumber ='3'))

 

IN 키워드를 중첩 사용해서 서브 쿼리를 사용한 예제입니다.

-- (PC No. 1000)을 주문한 모든 고객을 나열하시오 두번째 방법 SELECT Customers.FirstName, Customers.LastName FROM Customers WHERE Customers.CustomerID IN (SELECT CustomerID FROM

 

(Order_Details INNER JOIN Orders ON Order_Details.OrderNumber=Orders.OrderNumber) INNER JOIN Products ON Products.ProductNumber=Order_Details.ProductNumber WHERE Products.ProductName='PC No. 1000')

 

IN 키워드를 한번만 사용해서 서브 쿼리를 사용한 예제입니다.

위의 예제들을 보면 알 수 있듯이 서브 쿼리 안에서 조인을 사용하게 되면 외부 쿼리에서는 조인을 사용하는 양이 상대적 으로 줄어들 수 있게 됩니다. 테이블의 연결이 많이 없는 쿼리문의 경우라면 서브 쿼리를 사용하는 이점을 잘 못 느낄 수도 있지만, 테이블 연결이 많으면 많을 수록 서브 쿼리를 사용하지 않고 단순히 조인만 이용하게 된다면 아무리 숙련된 개발 자라도 코드를 읽기가 어려워 질 것입니다. 특히 자신이 만든 쿼리문이 아니라 다른 사람이 만들어 놓은 쿼리문을 보게 된 다면 더욱 그런 어려움을 느끼게 될 것입니다.

-- 노트북이나 데스크탑PC를 주문한 고객을 나열하시오. SELECT Customers.FirstName, Customers.LastName FROM Customers WHERE Customers.CustomerID IN (SELECT Orders.CustomerID FROM Orders WHERE Orders.OrderNumber = ANY (SELECT Order_Details.OrderNumber FROM Order_Details WHERE Order_Details.ProductNumber IN ('2', '3')))

 

위의 예제는 IN 키워드와 ANY 연산자를 결합한 예제입니다.

서브 쿼리의 중첩은 32단계까지 중첩을 지원합니다. 하지만 서브 쿼리 역시 내부적으로 조인이 많아지면 코드가 읽기 어 렵게 되는 것은 피할 수가 없기 때문에 중첩을 많이 허용한다고 해서 중첩 쿼리를 너무 많이 사용하는 것은 좋은 습관이 아 닙니다.

사실 조인을 너무 많이 사용해야 되는 쿼리가 있다면 이런 경우의 해결책은 서브 쿼리를 이용하기 보다는 뷰를 이용해서 한번 데이터를 필터링 한 다음에 그 뷰를 이용해서 쿼리문을 만드는 것이 좋습니다.

뷰를 사용하는 방법에 대해서는 차후에 다루도록 하겠고, 위의 경우 실제 품명을 출력문에 나타나게 하려면 마지막 IN 키 워드 안에 한번 더 중첩 쿼리를 사용해서 조인문을 만들면 되겠습니다.

WHERE Order_Details.ProductNumber IN ('이 구문 안에 한번 더 서브 쿼리문을 넣습니다. ) 위와 같이 사용하면 됩니다.

이상으로 서브 쿼리의 사용 예를 알아보았는데, 만약 이 부분에 대해 익히시려는 분이 계셨다면 도움이 되길 바랍니다.