SQL 뽀개기 - 8 | 서브쿼리

서브쿼리 : 쿼리문 안에 또다른 쿼리문이 포함된 구문

 

단일행 서브쿼리 : 하나의 값만 리턴한다. 스칼라값 하나를 리턴

다중행 서브쿼리 : 여러개의 행을 리턴한다.

다중행 열 서브쿼리 : 여러개의 열로 구성된 여러개의 행인 테이블을 리턴한다

 

1. 단일행 서브쿼리

SELECT MAX(popu), name FROM tCity -- ERROR
SELECT name FROM tCity WHERE popu = MAX(popu); -- ERROR

SELECT name FROM tCity WHERE popu = (SELECT MAX(popu) FROM tCity); -- CORRECT
SELECT item FROM tItem WHERE num = (SELECT MAX(num) FROM tItem); -- CORRECT
SELECT name FROM tStaff WHERE score = (SELECT MAX(score) FROM tStaff);
SELECT name FROM tStaff WHERE salary >= (SELECT AVG(salary) FROM tStaff);
SELECT member, addr FROM tMember WHERE age = (SELECT MAX(age) FROM tMember);

-> 서브쿼리를 감싸는 전체 쿼리를 외부쿼리라고 부른다.

-> 구분을 위해서 서브쿼리를 괄호로 감싼다

-> 서브쿼리를 먼저 실행하고, 그 이후에 외부쿼리를 실행하는 순차적 방식.

- 단일행 서브쿼리는 하나의 결과만 리턴하며, 주로 WHERE, HAVING 등의 조건절에 쓰인다.

-- 1. 따로따로 생각해 놨다가
SELECT category FROM tItem WHERE item = '청바지';
SELECT delivery FROM tCategory WHERE category = '패션';

-- 2. 두개를 합쳐서 표현
SELECT delivery FROM tCategory WHERE category = (SELECT category FROM tItem WHERE item = '청바지');

- 서브쿼리의 중첩 횟수에는 제약이 없다.

SELECT item FROM tItem WHERE price = 70000;

SELECT member FROM tOrder WHERE item = 
(SELECT item FROM tItem WHERE price = 70000);

SELECT age FROM tMember where member = 
(SELECT member FROM tOrder WHERE item = 
(SELECT item FROM tItem WHERE price = 70000));

- 단일행 서브쿼리는 단일값을 리턴함으로 =, <, > 와 같은 연산자와 같이 사용이 가능하다.

 

2. 다중행 서브쿼리

- 다중행 서브쿼리는 여러개의 결과를 리턴하므로 연산자(=, >, < 등..) 와 같이 사용할 수 없다.

- 대신 IN 연산자를 사용

SELECT item, price FROM tItem WHERE item IN
(SELECT item FROM tOrder WHERE member = '향단');

 

3. 다중열 서브쿼리

SELECT * FROM tStaff WHERE (depart, gender) =
(SELECT depart, gender FROM tStaff WHERE name = '윤봉길');

SELECT * FROM tStaff WHERE (depart, salary) IN
(SELECT depart, MAX(salary) FROM tStaff GROUP BY depart);

UPDATE tCity SET (area, popu) = (SELECT area, popu FROM tCity WHERE name = '부산')
WHERE name = '서울';

결과셋이 다중행인 경우 IN 연산자로 비교해야 한다

 

- 일괄 비교가 성립하기 위해서는 비교 대상과 서브쿼리의 컬럼 개수가 같아야 한다!!

 

# 서브쿼리 연산자

IN 연산자는 괄호 안의 결과셋과 순차적으로 비교한다

ANY, ALL 연산자는 결과셋 전체와 비교한다

ANY : 결과셋 중 하나라도 만족하는 값이 있으면 참

ALL :결과셋 모두가 만족해야 참

SELECT name FROM tStaff WHERE salary > ANY
(SELECT salary FROM tStaff WHERE depart = '영업부');
-- ANY | 영업부 "최소" 월급보다 많은 사람  

SELECT name FROM tStaff WHERE salary > ALL
(SELECT salary FROM tStaff WHERE depart = '영업부');
-- ALL | 영업부 "최대" 월급보다 많은 사람

SELECT item, price FROM tItem WHERE item = ANY
(SELECT item FROM tOrder WHERE member = '향단');

= ANY ~~ 는 그중 하나라도 같은 것을 찾는다. 결과적으로는 IN 연산자와 동일한 역할.

 

# 연관 서브쿼리 (Correlated Subquery)

- 위의 서브쿼리들은 모두 단독으로 실행가능한 독립 서브쿼리였다.

- 연관 서브쿼리는 외부 쿼리의 필드값을 참조하며 단독으로 실행할 수 없다.

SELECT member, item, (SELECT price FROM tItem WHERE tItem.item = tOrder.item) price
FROM tOrder;

SELECT item, (SELECT price FROM tCategory WHERE tCategory.category = tItem.category) price
FROM tItem;

# EXISTS

- 결과셋이 있는지 조사하는 연산자

- 단독 실행은 불가능.. IF문이나 WHERE절과 같이 사용한다

SELECT * FROM tMember M WHERE EXISTS
(SELECT * FROM tOrder O WHERE O.member = M.member);

SELECT * FROM tMember M WHERE NOT EXISTS
(SELECT * FROM tOrder O WHERE O.member = M.member);

- IN 연산자로도 구할 수 있으나, EXISTS 연산자는 존재 유무만 체크하므로 속도가 더욱 빠르다

 

* 인라인 뷰

서브쿼리는 주로 WHERE절, SELECT절의 필드목록, FROM절에서 사용된다.

여기서 FROM절에 오는 서브쿼리는 하나의 값이 아닌 테이블을 리턴한다. 

-> FROM절의 서브쿼리를 특별히 인라인 뷰(inline view)라고 정의한다.

- 인라인이 붙은 이유는 CREATE로 영구저장 하지않고 잠시 만들어쓰고 버리는 뷰라는 뜻

 

SELECT * FROM (SELECT * FROM tCity) [별명];

SELECT * FROM (SELECT name, popu, area FROM tCity) A;
SELECT * FROM (SELECT * FROM tCity WHERE metro = 'y') B;

SELECT member, addr FROM (SELECT * FROM tMember WHERE age < 19) A
WHERE A.money > 100000;

SELECT * FROM (SELECT * FROM tStaff WHERE grade = '과장' OR grade = '부장') A
WHERE A.score >= 70;

인라인뷰는 영구적으로 저장되는 객체가 아니기 때문에 쿼리 실행중 이 테이블을 가리키기 위해서는 별명이 있어야한다

 

SELECT * FROM (
    SELECT name, popu * 10000 AS ingu FROM tCity
) A
WHERE A.ingu > 1000000;

SELECT * FROM (
    SELECT name, popu * 10000 AS ingu FROM tCity
) A
WHERE ingu > 1000000;


-- 1중첩 인라인
SELECT name, (popu * 10000 / area) AS dens
    ,CASE
        WHEN (popu * 10000 / area) > 1000 THEN '고밀도'
        WHEN (popu * 10000 / area) > 100 THEN '중밀도'
        ELSE '저밀도'
    END densgrade
FROM tCity;


-- 2중첩 인라인
SELECT name, dens
    ,CASE
        WHEN dens > 1000 THEN '고밀도'
        WHEN dens > 100 THEN '중밀도'
        ELSE '저밀도'
    END densgrade
FROM (
    SELECT name, (popu * 10000 / area) AS dens FROM tCity
) CD;


-- 3중첩 인라인
SELECT name, dens, densgrade
    ,CASE
        WHEN densgrade = '고밀도' then '8차로'
        WHEN densgrade = '중밀도' then '4차로'
        ELSE '2차로'
    END roadplan
FROM (
    SELECT name, dens
        ,CASE
            WHEN dens > 1000 THEN '고밀도'
            WHEN dens > 100 THEN '중밀도'
            ELSE '저밀도'
        END densgrade
    FROM (
        SELECT name, (popu * 10000 / area) AS dens FROM tCity
    )
);

-> 여러번 사용할 계산식을 인라인뷰에서 필드로 정의하고, 그 바깥에서는 필드를 참조하는 방식.

 

# 테이블 연산자

1. UNION

복수개의 결과셋에 대한 합집합을 생성한다.

기본적으로 두 집합 사이 공통적으로 존재하는 메소드가 있다면 하나로 합친다(중복X)

--> UNION ALL 을 사용시 중복을 제거하지 않고 모두 출력한다

SELECT * FROM tItem WHERE category = '식품'
UNION
SELECT * FROM tItem WHERE category = '가전';

SELECT DISTINCT depart FROM tStaff WHERE salary > 400
UNION ALL
SELECT DISTINCT depart FROM tStaff WHERE score > 80;

하나의 테이블에서 조건을 만드려 한다면 OR 연산자를 사용하는 편이 낫다

UNION 연산자는 다른 테이블로부터 읽은 결과셋도 합칠 수 있다는 점에서 다르다

- 하나로 합칠 결과셋은 필드의 개수와 타입이 일치하거나 적어도 호환이 되어야 한다.

- 세개 이상의 테이블을 합칠 수도 있다

 

2. INTERSECT

교집합 연산자, 중복 레코드는 한번만 포함한다 (중복 X)

AND연산자와 다른 점은 역시 다른 테이블 내의 결과셋들을 합칠 수 있다는 점

SELECT name FROM tStaff
INTERSECT
SELECT member FROM tMember;

 

3. MINUS

차집합 연산자. 앞쪽 집합의 원소에서 뒤쪽 집합의 원소를 제외한 집합

개발 중에 테이블의 변화를 관찰하고자 할때 주로 사용

SELECT name FROM tStaff WHERE depart = '영업부'
MINUS
SELECT name FROM tStaff WHERE gender = '여';

SELECT * FROM tStaffNew
MINUS
SELECT * FROM tStaffOld

 

4. MERGE

비교, 삽입, 갱신을 한방에 처리해서 테이블을 병합할 수 있는 명령어(UPSERT라고도 불림)

MERGE INTO tCity T USING tCityNew S ON (S.name = T.name)
WHEN MATCHED THEN
    UPDATE SET T.area = S.area, T.popu = S.popu
    -- DELETE문도 올 수 있다
WHEN NOT MATCHED THEN
    INSERT VALUES (S.name, S.area, S.popu, S.metro, S.region);

ON (S.name = T.name) 부분: 일치조건. 주로 기본키 값으로 비교

두 값이 일치하면 MATCHED THEN 절로 이동, 불일치하면 NOT MATCHED THEN 절로 이동,

 

USING절에 전체 테이블이 아니라 일부 서브쿼리만 와도 된다

MERGE INTO tCity T USING (SELECT * FROM tCityNew WHERE region='경기') S ON (S.name = T.name)
WHEN MATCHED THEN ...
WHEN NOT MATCHED THEN ...