12. [SQL기초] 서브쿼리

하위 쿼리라고도 합니다. 쿼리문을 품고 있는 쿼리문 구조를 표현하기 위해 사용하며, 품고 있는 상위 쿼리에 속해있는 쿼리문을 말합니다.

서브쿼리는 SELECT문으로 선택한 쿼리 결과를 이용해서 추가의 쿼리문을 실행하는 데 사용하는 쿼리문을 말합니다. 지금까지는 테이블 한 개에 질의를 해서 결과를 얻는 것으로 끝났다면, 서브쿼리를 사용하면 테이블 두 개 이상을 연계해서 쿼리문을 실행할 수 있게 됩니다.

서브쿼리는 SELECT문만 사용할 수 있습니다. 서브쿼리로 얻은 결과를 다른 쿼리문을 실행하는데 필요한 조건 값 또는 저장 값으로 사용하기 때문입니다.

서브쿼리의 결과는 단일 값일 수도 있고, 열이 한 개인 테이블, 또는 멀티 컬럼을 가진 테이블이 될 수도 있습니다. 서브쿼리라고 지금까지 배워왔던 쿼리 문과 다른 것은 없습니다. 쿼리 문의 결과를 상위 쿼리의 일부로 사용하는 것만 다릅니다.

서브쿼리 예

Update inventory set stock = stock - (select sum(p_num) from p_order whre p_code = 3) where p_code = 3;

Select * from p_order where userid in (select id from member where age > 2);

서브쿼리 사용을 위해서는 새로운 키워드 IN과 EXISTS를 알아야 합니다. 하위 쿼리 안에 해당하는 값이 있는지 체크하기 위한 키워드입니다. 그리고 하위 쿼리는 반드시 괄호로 감싸서 상위 쿼리와 구분을 해야 합니다.

반환되는 서브쿼리 결과는 3가지가 있으며 반환된 결과에 따라 서브쿼리를 사용할 수 있는 절과 용도가 제한됩니다.

서브쿼리가 사용되는 절 위치에 따라 서브쿼리의 용도를 하나씩 알아보도록 하겠습니다.

SELECT절에 서브쿼리 사용

서브쿼리로 단일 값을 가져오면 SELECT절의 컬럼 값으로 서브쿼리 결과 값을 대입할 수 있습니다. 집계 함수로 특정 상품 2개의 주문 개수를 알고 싶으면 별도의 쿼리문으로 2개의 결과를 따로 얻어야 합니다. 2개의 쿼리문으로 값을 얻어서 각각 사용하는 것보다는 하나의 쿼리문으로 2개의 컬럼에 각각의 값을 가져올 수 있으면 실행 속도도 더 빠른 뿐만 아니라 서버 프로그램에서 처리하기도 훨씬 쉬워집니다.

Select (select count(*) from p_order where p_code = 1) as ‘상품1’, (select count(*) from p_order where p_code = 2) as ‘상품2’;

UPDATE절에 서브쿼리 사용

UPDATE절의 갱신하는 컬럼 값을 서브쿼리 결과 값으로 사용할 수 있습니다. 상품정보(product) 테이블의 판매 가격정보가 변경되어서 주문정보(p_order) 테이블의 같은 상품 판매 가격을 변경하려면 서브쿼리로 다음과 같이 처리할 수 있습니다.

Update p_order set p_sell_price = (select sell_price from product where id = 2) where p_code = 2;

FROM절에 서브쿼리 사용

서브쿼리의 결과를 다시 쿼리를 해서 결과를 얻을 수 있습니다. 서브쿼리의 결과가 테이블처럼 사용된다고 생각하면 됩니다. FROM절에 서브쿼리를 사용할 때는 서브쿼리의 결과가 단일 컬럼 테이블, 또는 멀티 컬럼 테이블이어야 합니다.

단일 값을 반환하는 서브쿼리는 FROM절에 사용하면 상위 쿼리에서 처리할 것이 아무것도 없기 때문에 서브쿼리를 사용하는 것이 의미가 없습니다.

FROM절에 서브쿼리를 사용하는 것은 SELECT문에서만 가능합니다. 주문정보(p_order) 테이블에서 상품 판매가가 높은 주문 3개를 가져와서 최근 주문 일자 순으로 다시 정렬하는 쿼리문을 작성해보겠습니다.

Select * from (select * from p_order order by p_sell_price desc limit 3) a order by wt desc;

먼저 서브쿼리로 판매가 높은 순으로 정렬한 주문 정보에서 상위 3개를 가져옵니다. FROM절에 서브쿼리를 사용할 경우 반드시 서브쿼리 결과에 별칭을 사용해야 합니다. 서브쿼리를 감싼 괄호 오른쪽에 공백으로 띄워서 별칭을 표시합니다.

퀴리문의 “a”는 서브쿼리 결과를 가리키는 별칭입니다. 별칭 이름은 테이블 이름 정의 규칙과 같습니다. 문자열로 시작하고 문자열과 숫자 조합으로 1자 이상이면 됩니다. 별칭은 원하는 것으로 아무것이나 사용해도 되지만, 서브쿼리를 줄여서 “sq”, 또는 알파벳으로 “a” 같이 단순한 것으로 사용하는 것이 좋습니다.

FROM절에 서브쿼리를 사용하는 것은 데이터베이스마다 조금씩 사용방법이 다릅니다. SQL Server는 서브쿼리에 ORDER BY 절을 사용할 수 없습니다. 따라서 앞의 예와 같이 정렬된 결과의 일부를 서브쿼리로 가져오는 것은 SQL Server에서는 할 수 없습니다.

Select * from (select * from p_order order by p_sell_price desc) a order by wt desc;

오라클은 별칭을 사용하지 않아도 서브쿼리를 사용할 수 있습니다.

Select id from (select * from p_order order by p_sell_price desc) order by wt desc;

WHERE절에 서브쿼리 사용

SELECT문에 사용

서브쿼리의 반환 값과 일치하는 값을 가진 행(들)을 선택합니다. 다른 테이블에서 선택한 값으로 쿼리 결과를 얻을 수 있습니다. WHERE절에서 사용할 수 있는 서브쿼리는 여러 개일 수 있습니다.

원산지가 대한민국이고, 제조사가 카카오인 상품 목록을 가져오는 쿼리문을 작성해 보겠습니다. 원산지와 제조사 코드를 모르기 때문에 서브쿼리로 원산지와 제조사 코드를 가져와 WHERE절에서 비교를 합니다.

Select * from product where madein = (select id from madein where country = ‘대한민국’) and maker = (select id from maker where name = ‘카카오’);

DELETE문에 서브쿼리 사용

WHERE절의 서브쿼리에서 반환한 값과 같은 컬럼 값을 가진 행을 삭제합니다. 회원정보(member) 테이블에서 “라이언”을 가진 회원 ID를 서브쿼리로 얻은 후 주문정보(p_order) 테이블에서 해당 회원의 정보를 삭제하는 쿼리문을 작성해 보겠습니다.

Delete from p_order where userid = (select id from member where name = ‘라이언’);

서브쿼리에서 부모 테이블과 같은 테이블을 사용해 결과 값을 생성하는 경우 MySQL에서는 제약이 있습니다. 서브쿼리를 한 번 더 감싸서 쿼리 해서 가상 테이블 형태로 변환해야 쿼리가 가능합니다. 가상 테이블(뷰)에 대해서는 뒤에서 자세히 배웁니다. 여기서는 제약으로 인해 서브쿼리에서 부모 테이블과 같은 테이블을 사용하면 간접적인 구현 방법을 사용해야 한다 정도만 이해하면 됩니다.

특정 리뷰글의 상품 코드와 일치하는 모든 리뷰를 삭제하는 쿼리문을 작성해보겠습니다.

Delete from review where p_code = (select * from (Select p_code from review where id = 1) as a);

괄호로 감싼 서브쿼리는 그 안에 실제 필요한 비교 값을 얻는 서브쿼리가 다시 있습니다. 바깥쪽의 서브쿼리는 가상테이블을 생성해서 “a”라는 이름으로 매핑을 해주기 위한 래퍼(Wrapper) 입니다. 쉽게 포장 박스 역할을 한다고 생각하면 됩니다. 앞의 쿼리문을 래퍼 없이 그냥 단순 서브쿼리로 구현하면 에러가 발생합니다.

Delete from review where p_code = (Select p_code from review where id = 1);

서브쿼리 결과를 단일 값이 아니라 여러 개의 값을 가진 테이블로 가져와서 여러 값에 대해 일괄 삭제를 할 수도 있습니다. 이 방법을 상관 서브쿼리라고 하고 “EXISTS”, “IN” 키워드를 추가로 사용해야 합니다. 상관 서브쿼리에 대해서는 뒤에서 자세히 배웁니다.

UPDATE문에 서브쿼리 사용

서브쿼리의 결과 값과 일치하는 컬럼 값을 가진 행(들)의 컬럼 값을 갱신할 수 있습니다. 값이 일치하는 행이 여러 개면 여러 행이 동시에 갱신됩니다.

상품정보(product) 테이블에서 특정 상품의 변경된 판매가를 가져와서 주문정보 판매가에 반영하는 쿼리문을 작성해보겠습니다. 주문정보(p_order) 테이블에서 “피치핑크” 이름인 회원의 주문을 모두 취소합니다. 주문정보 테이블에는 회원 ID만 저장되어 있으므로, 서브쿼리로 회원정보 테이블에서 회원 이름으로 회원 ID를 검색해 ID값을 가져와야 합니다.

Update p_order set cancel = 'Y' where userid = (select id from member where name = '피치핑크');

UPDATE문 SET절에 서브쿼리 사용

UPDATE문의 SET절에도 서브쿼리를 사용해서 갱신하는 컬럼 값을 서브쿼리의 결과 값으로 저장할 수 있습니다. 이때 서브쿼리의 결과는 반드시 단일 값이어야 합니다.

테이블 컬럼에 값을 갱신할 때 다른 테이블의 값을 서브쿼리를 이용해 가져와 저장할 수 있습니다. 상품정보(product) 테이블의 특정 상품 판매 가격이 변동되었을 때 주문정보의 해당 상품 판매가를 일괄 변경해보겠습니다.

Update p_order set p_sell_price = (Select sell_price from product where id = 2) where p_code = 2;

서브쿼리 결과를 테이블에 저장하기

서브쿼리로 가져온 결과 테이블을 INSERT문으로 저장할 수 있습니다. 서브쿼리로 값을 반환하는 경우 INSERT문의 VALUES절 안에 컬럼 값으로 적용할 수 있습니다. 회원 포인트(mypoint) 테이블에 특정 회원이 주문한 전체 금액의 1%를 적립해서 저장하는 쿼리문을 작성해 보겠습니다.

Insert into mypoint (userid, point, wt) values (2, (select floor(sum(p_sell_price * p_num)/100) from p_order where userid = 2), current_timestamp);

주의할 점이 있습니다. INSERT문으로 저장할 때 열 이름을 명시하지 않으면 자동 증가 값을 꼭 명시해야 합니다. 그렇지 않으면 컬럼 값의 개수가 맞지 않는다는 에러 메시지가 출력됩니다. 자동 증가 값을 수동으로 지정해야 하는 것이 다소 어색할 수 있지만, SQL Server를 제외한 나머지 데이터베이스는 INSERT문에 열 이름 지정이 없으면 자동 증가 값을 표기해야 합니다.

MySQL은 “DEFAULT” 키워드를 사용해 새로운 행에 적용할 자동 증가 값이 자동 입력되도록 할 수 있지만 열 이름을 사용하지 않는 INSERT SELECT문에는 사용할 수 없습니다.

그리고 열 이름을 표시하지 않으면 서브쿼리를 감싼 괄호는 사용할 수 없습니다. 반드시 괄호 없이 서브쿼리를 작성해야 합니다.

Insert into mypoint select 12, userid, floor(sum(p_sell_price * p_num)/100) as point, current_timestamp as wt from p_order where userid = 2;

서브쿼리가 테이블을 반환하는 경우, VALUES 절 전체를 대신해 서브쿼리를 표시하면 서브쿼리로 반환되는 테이블 내용 전체가 저장됩니다. 서브쿼리로 반환된 테이블을 INSERT문으로 저장하는 구조를 줄여서 “INSERT SELECT”라고 구분을 해서 부릅니다.

앞서 작성했던 특정 회원의 주문 합계에 대한 포인트 금액을 저장한 쿼리문을 전체 회원의 포인트를 저장하는 서브쿼리 활용 쿼리문으로 변경해보겠습니다.

Insert into mypoint (userid, point, wt) (select userid, floor(sum(p_sell_price * p_num)/100), current_timestamp from p_order group by userid);

상관 서브쿼리

지금까지 배운 서브쿼리는 서브쿼리 단독으로도 실행할 수 있는 쿼리문입니다. 서브쿼리만 따로 떼어서 실행할 수 있고, 서브쿼리에서 반환된 값이나 테이블을 부모 쿼리에서 사용하는 방식입니다.

상관 서브쿼리는 서브쿼리와 부모 쿼리의 특정 컬럼을 서브쿼리의 WHERE절에서 비교하는 상관관계를 가집니다. 때문에 서브쿼리만 따로 떼어서 실행할 수 없습니다. 확인해보겠습니다.

회원 이름이 “라이언”인 회원의 모든 주문정보에서 전화번호(mobile) 컬럼의 전화번호를 바꾸는 상관 서브쿼리는 다음과 같이 작성합니다.

Update p_order set mobile = '010-2345-6789' where exists (select * from member where name = '라이언' and p_order.userid = id);

여기서 서브쿼리는 WHERE절의 EXISTS 이하 전체 쿼리문이 됩니다. 괄호 안의 서브쿼리를 따로 떼어서 실행하면 당연하지만 에러가 발생합니다. 서브쿼리의 “p_order.userid”는 서브쿼리와 상관관계로 묶인 부모 쿼리를 위한 테이블의 열 이름입니다.

서브쿼리의 WHERE절의 조건식에는 상위 쿼리의 테이블 컬럼이 포함되기 때문에 서브쿼리만 따로 떼어서 실행할 수 없고, 상위 쿼리에 종속된 형태로만 사용됩니다.

그래서 일반 서브쿼리와는 따로 구분해서 상관 서브쿼리라고 합니다.

상관 서브쿼리는 서브쿼리의 결과가 참이 되는 부모 쿼리의 테이블 행에 대해서만 부모 쿼리가 적용됩니다. 상관 하위 쿼리의 결과가 참/거짓이 되기 때문에 저장할 테이블 데이터가 필요한 INSERT문에는 상관 서브쿼리를 사용할 수 없습니다. INSERT문을 제외한 SELECT문, UPDATE문, DELETE문은 사용할 수 있습니다.

앞서 EXISTS 키워드를 사용한 상관 서브쿼리문을 이미 봤지만, 상관 서브쿼리를 위해서는 IN과 EXISTS 두 키워드를 알아야 합니다.

두 키워드는 상관 서브쿼리 조건식을 구성하는 비교 키워드로 상관 서브쿼리의 연산자 역할을 합니다.

EXISTS 상관 서브쿼리

EXISTS 키워드는 비교하는 서브쿼리의 테이블 컬럼 값이 부모 테이블 컬럼 값과 같은 것이 존재하는지를 확인하는 키워드입니다. 상관 서브쿼리에 존재하는 행에는 부모 쿼리를 실행합니다.

기초 구문

EXISTS 부모 쿼리 WHERE EXISTS (서브쿼리);

주문 내역이 있는 회원 이름을 가져오는 상관 서브쿼리를 작성해보겠습니다. 서브쿼리는 주문 테이블에서 회원 아이디로 비교하고, 부모 쿼리는 상관 서브쿼리에 있는 아이디의 이름을 회원 테이블에서 가져옵니다.

Select name from member where exists (select * from p_order where member.id = userid);

서브쿼리의 조건식에서 부모 테이블의 컬럼과 비교를 할 때, 컬럼 앞에 부모 테이블 이름을 붙여서 부모 테이블의 컬럼임을 꼭 명시해야 합니다. 예제에서는 컬럼 이름이 다르지만 컬럼 이름이 같은 수도 있고, 서브쿼리 테이블인 주문정보(p_order) 테이블에도 id 컬럼이 이미 있기 때문에 회원정보(member) 테이블 이름을 앞에 붙여서 구분이 되도록 해야 합니다. 그렇지 않으면 주문정보 테이블의 id 컬럼과 userid 컬럼을 비교하기 때문에 서브쿼리 결과에 존재하는 부모 행이 없게 됩니다.

EXISTS 상관 서브쿼리를 작성할 때 서브쿼리에 존재하지 않는 행에만 부모 쿼리를 실행할 수도 있습니다. EXISTS의 반대인 행을 찾는 것은 NOT EXISTS와 같이 NOT키워드를 공백을 띄워서 앞에 붙여주면 됩니다.

앞의 상관 서브쿼리의 반대로 주문 내역이 없는 회원 이름만 가져오고 싶으면 다음과 같이 수정하면 됩니다.

Select name from member where not exists (select * from p_order where member.id = userid);

IN 상관 쿼리

상위 쿼리의 특정 컬럼 값이 서브쿼리의 결과 집합 안에 있는지를 비교하는 연산자입니다. 서브쿼리의 결과는 집합이거나 컬럼 1개를 가진 테이블이어야 합니다. 상관 서브쿼리는 단독으로 실행할 수 없다고 했지만, IN 키워드를 사용하는 서브쿼리가 컬럼 한 개인 테이블을 반환할 때는 예외적으로 단독으로 실행할 수도 있습니다.

기초 구문

IN 부모 쿼리 WHERE IN (서브쿼리);

IN 키워드 오른쪽에는 집합이 와야 합니다. 따라서 IN 키워드는 집합의 OR 연산자와 같은 역할을 합니다. 부모 테이블의 컬럼 값이 집합 안의 값들 중 어느 하나에 해당되면 참이 되어 부모 쿼리가 실행되는 방식입니다.

EXISTS 키워드와 마찬가지로 서브 쿼리 집합 안에 없으면 참이 되도록 하려면 “NOT IN”과 같이 IN 키워드 앞에 공백을 띄워서 NOT을 표시하면 됩니다.

앞서 작성했던 주문 내역이 없는 회원의 이름을 가져오는 상관 서브쿼리를 수정해서 주문 내역이 없는 회원은 휴면 처리를 하는 쿼리문을 IN 상관 서브쿼리로 작성해보겠습니다.

Update member set dormant = 'Y' where id not in (select userid from p_order where member.id = userid);

EXISTS 상관 서브쿼리와 키워드만 달라졌을 뿐 동일한 상관 서브쿼리라고 봐도 됩니다. 앞서 IN 상관쿼리의 서브쿼리는 단독으로 실행할 수도 있다고 했습니다. 앞의 UPDATE IN 상관 서브쿼리 문은 다음과 같이 수정할 수 있습니다.

Update member set dormant = 'Y' where id not in (select distinct userid from p_order);

앞의 UPDATE 상관 서브쿼리보다 더 간결하고 서브쿼리도 단독으로 실행할 수 있습니다. 서브쿼리를 단독으로 실행할 수 있으면, 서브쿼리가 반환하는 집합, 또는 단일 컬럼 테이블의 결과를 따로 확인해 볼 수 있기 때문에 쿼리문을 확인하는데 도움이 됩니다.

NULL과 상관 서브쿼리

NULL은 항상 예외 처리입니다. NULL은 전용 연산자인 IS로만 비교할 수 있기 때문에 다른 연산자로 비교하면 결과가 참이나 거짓이 아닌 NULL이 됩니다. 상관 서브쿼리 연산자인 IN과 EXISTS 또한 같은 기준으로 처리됩니다.

EXISTS 연산자는 서브쿼리의 “=” 비교 연산자가 NULL을 비교할 수 없기 때문에 항상 거짓이 되고, EXISTS 결과 행에서도 NULL과 비교한 것은 모두 제외가 됩니다. IN 연산자도 NULL을 비교할 수 없는 연산자이기 때문에 “=”과 마찬가지로 오른쪽 결과 집합의 요소 중 NULL과의 비교는 제외가 되고, 나머지 매칭 되는 값만 상위 쿼리가 실행됩니다.

NOT IN은 집합에 포함되었는지를 판단할 때 반대의 경우가 되지만, NULL이 포함된 집합과 비교를 할 때는 반대의 경우가 아니라 참이 되는 경우가 없게 됩니다.

IN 서브 쿼리가 반환하는 것은 집합이 되므로 NULL이 포함된 집합으로 비교를 하는 쿼리문을 다음과 같이 작성할 수 있습니다.

select 1 in (1, null);

회원 ID가 1이 오른쪽 집합 안에 있으므로 1(참)이 반환됩니다.

select 1 not in (1, null);

반대로 없는지를 확인하면 0(거짓)이 반환됩니다. 일치하는 값이 집합 안에 있는 경우에는 참과 거짓으로 반환이 됩니다.

그런데 집합 안에 없는 값을 비교하면 참/거짓이 아닌 다른 결과가 반환됩니다. 다음 두 가지 쿼리문은 모두 NULL을 반환합니다. 비교할 수 없으므로 거짓으로 처리됩니다.

select 2 in (1, null);
select 2 not in (1, null);

앞서 배웠던 주문정보가 있는 회원 이름을 가져오는 IN 상관 서브쿼리를 앞의 주문정보 서브쿼리 대신 고정된 값으로 만든 집합을 이용해서 실행해봅니다.

Select name from member where id in (1, null);

참이 되는 회원정보 행이 있으므로 한 개의 결과를 반환합니다.

반대로 집합에 없는 회원 이름을 가져오는 다음 쿼리문은 아무 이름도 가져오지 못합니다. NULL이 있기 때문에 회원 아이디 “1”과의 비교가 거짓이 되는 것을 제외하고 모두 NULL이 반환되기 때문에 참이 되는 조건이 한 개도 없게 됩니다.

언뜻 앞의 SELECT IN 쿼리문 결과로 반환된 한 개의 이름을 제외한 나머지 이름이 제외될 것 같지만 NULL이 포함된 서브쿼리 결과는 전혀 다르게 됩니다.

Select name from member where id not in (1, null);

서브쿼리 결과에 널이 없으면 예상대로 집합 안에 있는 회원 아이디를 제외한 나머지 모든 회원의 이름을 가져옵니다.

Select name from member where id not in (1);