5.3 최댓값 표시하기 - 값을 반환하는 하위 질의 1
다음과 같은 문제는 과연 어떻게 처리하면 좋을까요?
◼︎ 테이블 tb에서 칼럼 sales의 최대값이 있는 레코드를 표시한다.
'매출정보 테이블 tb에서 매출이 가장 높은(sales가 최대) 사원의 데이터를 표시하시오.' 라는 문제입니다. 칼럼의 최댓값을 구할 때에는 MAX() 함수를 사용합니다. 다음 테이블 tb의 구조를 보고 잠깐 생각해 보겠습니다.
아마도 많은 분들이 다음과 같은 명령문을 생각했을 것입니다.
SELECT * FROM tb WHERE sales=MAX(sales);
하지만, 이 명령문을 실행하면 오류가 발생합니다.
단순하게 MAX(sales)라고 작성해서는 sales의 최댓값을 계산할 수 없습니다. 만약, MAX(sales)를 계산하려면, 먼저 다음 명령을 실행해야 합니다.
SELECT MAX (sales) FROM tb;
최댓값에 해당하는 레코드를 추출하려면, 먼저 이 명령으로 최댓값을 구하고 그 값을 사용해서 테이블 tb에서 다시 값을 추출해야 합니다.
여기에서, 하위 질의가 활약하게 됩니다. 먼저, 1단계에서 해당 질의로 MAX(sales)의 값을 구합니다. 그러고 나서 2단계에서 MAX(sales)로 구한 최댓값을 포함한 칼럼 sales의 레코드를 SELECT합니다.
SELECT * FROM tb WHERE sales IN (1단계에서 처리한 결과)
'~을 포함한다'라는 조건 WHERE ~ IN을 사용하는 방법은 8장 3.3 비교 연산자를 참고합니다. 1단계의 하위 질의는 괄호() 안에 기술합니다. 괄호를 사용하지 않으면 오류가 발생하니 주의하도록 합니다.
그러면 실제로 예를 들어 설명하겠습니다. 테이블 tb에서 칼럼 sales의 값이 가장 큰 레코드를 표시합니다.
다음 명령을 실행합니다.
SELECT *
FROM tb
WHERE sales
IN (SELECT MAX(sales) FROM tb);
IN 사용하기 - 칼럼을 반환하는 하위 질의
이번에는 하위 질의가 칼럼을 반환하는 예입니다. 1단계의 하위 질의에서 조건에 일치하는 칼럼을 반환하고 2단계에서 그 칼러을 포함한 값을 추출합니다. 이 때에는 다음과 같이 IN을 사용합니다.
IN을 사용한 하위 질의
SELECT 표시할_칼럼 FROM 테이블_이름
WHERE 칼럼_이름 IN (SELECT를_이용한_하위_질의로_추출한_칼럼);
1단계의 질의(하위 질의)는 반드시 괄호 안에 기술해야 합니다. 1단계에서 추출한 칼럼에 포함된다는(IN) 조건으로 2단계 질의를 실행합니다. 여기에서는 다음 작업을 실행합니다.
◼︎ 매출이 200 이상인 사원 이름을 표시한다.
이 경우 구체적인 처리는 다음과 같습니다.
◼︎ 매출정보 테이블 tb에서 매출(sales)이 200 이상인 사원번호( number)를 추출하고, 사원정보 테이블 tb1에서 해당 사원 이름(name)을 표시한다.
즉, 1단계에서 실행할 하위 질의는 테이블 tb에서 sales>=200의 조건에 일치하는 사원번호 number를 추출하는 것입니다.
SELECT number FROM tb WHERE sales>=200;
그리고 2단계에서는 1단계에서 추출한 number를 포함하는 레코드를 tb1에서 추출합니다.
SELECT * FROM tb1 WHERE 조건
이 '조건'부분에 1단계에서 추출한 A101과 A102를 포함한다는 내용을 기술합니다. 이때, WHERE ~ IN ~을 사용합니다.
하위 질의를 사용해서 테이블 tb에서 매출(sales)이 200 이상인 number를 추출하고, 테이블 tb1에서 해당하는 레코드를 표시해 보겠습니다.
다음 명령을 실행합니다.
SELECT *
FROM tb1
WHERE number
IN (SELECT number FROM tb WHERE sales>=200);
예상대로 A101과 A102가 표시되었습니다.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
하위 질의와 내부 조인의 실행 결과 - 비슷한 듯 다르다!
하위 질의와 내부 조인은 많이 비슷합니다. 예를 들어, 매출정보 테이블 tb에 존재하는 사원번호 number와 일치하는 사원정보를 테이블 tb1에서 찾아서 사원번호(number)와 이름(name)을 표시하는 경우, 하위 질의와 내부 조인으로 각각 표현하면 다음과 같습니다.
하위 질의
SELECT number, name
FROM tb1
WHERE number
IN (SELECT number FROM tb);
내부 조인
SELECT tb1.number, tb1.name
FROM tb1
JOIN tb
ON tb1.number=tb.number;
실행 결과는 다릅니다. 하위 질의를 사용하면 테이블 tb에 존재하는 number를 먼저 검색하고, 테이블 tb1에서 일치하는 레코드만 표시합니다. 이에 비해 내부 조인은 테이블 tb에 있는 모든 레코드를 표시합니다.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
5.6 IN 대신에 등호를 사용하면 오류가 발생한다?
앞에서 소개한 IN을 사용하지 않고 다음과 같이 등호(=)를 사용해도 되지 않을까 하고 생각한 분도 있을 것입니다.
SELECT *
FROM tb1
WHERE number = (SELECT number FROM tb WHERE sales>=200);
앞의 명령문을 실행하면 'Subquery returns more then 1 row'(하위 질의가 1건 이상 검색되었다!)라는 오류가 표시됩니다. 'number가 ✕ ✕와 정확하게 일치한다.'라는 조건이라면 등호를 사용해도 상관 없지만, 이 예에서는 여러 개의 레코드가 추출되기 때문에, '...중에 어느 것'이라는 의미의 IN을 사용해야 합니다.
물론, 해당 레코드가 1건밖에 없을 때에는 등호를 사용해도 오류가 발생하지는 않습니다. 예를 들어, 다음은 LIMIT를 사용해서 1건만 추출하는 예입니다. 이렇게 하면, 적어도 오류는 피할 수 있습니다.
SELECT *
FROM tb
WHERE number
= (SELECT number FROM tb1 WHERE sales>=200 LIMIT 1);
그러나 이렇게 하면 안타깝게도 A101이 추출될지 A102가 추출될지 알 수 엇어서 LIMIT는 도움이 되지 않습니다.
값이 큰 순서로 정렬해서 맨 앞의 1개만 표시할 수는 있습니다. 다음은 ORDER BY를 사용해서 '매출이 가장 높은 사원'이라는 조건으로 데이터를 추출하는 예입니다. 1단계에서는 테이블 tb에서 sales의 최댓값에 해당하는 number를 검색하고, 2단계에서는 테이블 tb1에서 1단계에서 추출한 값에 해당하는 레코드를 추출하고 있습니다.
SELECT *
FROM tb1
WHERE number
= (SELECT number FROM tb ORDER BY sales DESC LIMIT 1);
LIMIT로 1건만 추출했기 때문에 등호를 사용해도 오류가 발생하지 않습니다.
5.7 EXISTS를 사용해서 존재하는 레코드를 대상으로 검색하기
위에서 설명한 하위 질의는 WHERE ~ IN (SELECT ...) 형태였습니다. 이 형태는 1단계의 하위 질의가 해당하는 칼럼 데이터를 반환하는 것이었습니다. 예를 들면, 위의 예에서는 'A101, A102의 칼럼'이라는 칼럼 데이터가 반환되고, 그 값을 포함하는 레코드가 추출 대상이었습니다.
이에 비해, 특정 칼럼이 아닌 '대상이 되는 레코드가 존재한다.'라는 정보를 반환하는 것이 EXISTS입니다.
실제 예를 들어 설명하겠습니다. 사원정보 테이블 tb1에는 매출정보 테이블 tb에 존재하지 않는 사원번호를 가진 레코드도 포함되어 있습니다. 바로 A105입니다. 즉, A105 사원은 매출이 없다는 뜻입니다.
그러면 사원정보 테이블에서 매출이 있는 사원의 레코드를 하위 질의로 표현해 보겠습니다. 구체적으로는 다음과 같습니다.
◼︎ 테이블 tb에 존재하는 레코드를 추출하고, 테이블 tb1에서 해당하는 레코드를 표시한다.
먼저, 사원정보 테이블 tb1의 레코드를 표시합니다.
SELECT * FROM tb1
그리고, '매출이 있는 사원만'이라는 조건은 사원번호 number가 테이블 tb와 tb1 양쪽에 모두 존재한다는 의미이므로 다음과 같이 표현합니다.
WHERE tb.number=tb1.number
1단계에서는 테이블 tb에서 이 조건에 일치하는 레코드를 검색합니다.
SELECT * FROM tb WHERE tb.number=tb.number
이 명령은 특정 칼럼이 아니라 조건에 맞는 레코드를 추출하는 것입니다. 그리고 2단계에서는 EXISTS를 사용해서 테이블 tb1에 존재하는 해당 헤코드만 표시합니다.
다음 명령을 실행합니다.
SELECT *
FROM tb1
WHERE EXISTS
(SELECT * FROM tb WHERE tb.number=tb1.number);
테이블 tb에는 A105의 number가 존재하지 하지 않기 때문에 표시되지 않습니다.
5.8 NOT EXISTS 사용하기
반대로 NOT EXISTS는 하위 질의로 추출되지 않는 레코드를 대상으로 합니다. 이번에는 '매출이 없는 사원'의 레코드 만을 대상으로 하위 질의를 실행해 보겠습니다.
위에 EXISTS를 NOT EXISTS로 바꾸기만 하면 됩니다. NOT EXISTS를 사용해서 테이블 tb에 존재하지 않는 레코드의 number만 추출하고, 테이블 tb1에서 해당하는 레코드를 표시하겠습니다.
다음 명령을 실행합니다.
SELECT *
FROM tb1
WHERE NOT EXISTS
(SELECT * FROM tb WHERE tb.number=tb1.number);
실행 결과를 통해 매출이 없는 사원은 사원번호 A105, 나이가 35세인 박문수라는 것을 확인했습니다.
5.9 순위 정하기 2
표를 계산할 수 있는 소프트웨어라면 순위를 정하는 것은 간단합니다. 그러나 RDMBS에서 순위를 입력하는 것은 꽤 복잡한 처리라고 설명한 바 있습니다.
그런데 하위 질의를 사용하면 다양한 방법으로 순위를 매길 수 있습니다. 게다가, 셀프 조인보다 쉽게 처리할 수 있습니다. 이번에는 하위 질의를 사용해서 순위를 정하는 방법을 한 가지 소개하겠습니다.
매출정보 테이블 tb를 이용하여 매출 순위를 정해 보겠습니다. 이번에는 다른 테이블을 준비해서 칼럼에 순위를 입력해 보겠습니다. 조금 복잡하긴 하지만 차근차근 따라 해보기 바랍니다. 다음과 같은 순서로 진행하겠습니다.
연속 번호 기능을 설정한 테이블에 sales의 순서대로 정렬한 레코드를 삽입한다.
⬇︎
자동으로 입력되는 연속 번호가 순위가 된다.
구체적으로는 다음과 같은 처리를 합니다.
테이블 tb와 같은 구조의 테이블 tb_rank를 생성한다.
⬇︎
테이블 tb_rank에 연속 번호 기능을 설정한 칼럼 rank를 추가한다.
⬇︎
테이블 tb를 대상으로 sales의 순서대로 SELECT하는 하위 질의를 실행한다.
⬇︎
하위 질의의 결과를 테이블 tb_rank에 INSERT한다.
자, 그럼 순위를 입력하는 질의는 구체적으로 어떻게 작성하면 될까요? 연속 번호 기능과 하위 질의를 사용하는 방법을 다시 한번 떠올려 보기 바랍니다.
테이블 tb의 칼럼의 number와 sales, month로 구성되어 있습니다. 여기에 칼럼 rank를 추가한 테이블 tb_rank를 만들고, 테이블 tb의 레코드를 복사합니다. 그러고 나서 칼ㄹ럼 rank에 sales의 순위를 입력해 보겠습니다.
① 다음 명령을 실행합니다.
CREATE TABLE tb_rank LIKE tb;
② 다음 명령을 실행합니다.
ALTER TABLE tb_rank ADD rank INT AUTO_INCREMENT PRIMARY KEY;
③ 다음 명령을 실행합니다.
INSERT INTO tb_rank
(number, sales, month)
(SELECT
number, sales, month
FROM tb
ORDER BY sales DESC);
앞의 ① ~ ③은 각각 다음과 같은 처리를 합니다.
① 테이블 tb의 칼럼 구조만 복사해서 테이블 tb_rank를 생성한다.
② 테이블 tb_rank에 연속 번호 기능을 설정한 칼럼 rank를 추가한다.
③ 하위 질의를 사용해서 테이블 tb를 칼럼 sales의 내림차순으로 정렬하고, number와 sales, month를 테이블 tb_rank에 추가한다.
③에서 칼럼 rank 에 연속 번호가 자동으로 입력되는데 이 번호가 순위가 됩니다.
다음 명령을 실행합니다.
SELECT * FROM tb_rank;
체크!
★ UNION을 사용해서 여러 개의 테이블로부터 데이터를 SELECT할 수 있다.
★ 키를 지정해서 내부 조인을 한 여러 개의 테이블로부터 데이터를 SELECT할 수 있다.
★ LEFT JOIN이나 RIGHT JOIN을 사용해서 여러 개의 테이블로부터 필요한 데이터를 SELECT 할 수 있다.
★ 하위 질의의 의미를 이해하고 있다.
★ 하위 질의로 얻은 값을 조건으로 원하는 데이터를 추출할 수 있다.
출처: https://recoveryman.tistory.com/179 [회복맨 블로그]
