SQL 기초 - 9. 서브쿼리 (서브쿼리 연산자, 인라인 뷰, 테이블 조합, 테이블 병합)

이 글은 '김상형의 SQL 정복'의 예제 코드를 설명하는 방식으로 SQL 쿼리 예제를 소개하고 있습니다.

 

SQL 기초 - 9. 서브쿼리 (서브쿼리 연산자, 인라인 뷰, 테이블 조합, 테이블 병합)

-- MAX 같은 연산자는 집계 연산자라고도 한다.
SELECT MAX(popu), name FROM tCity;

SELECT name FROM tCity WHERE popu = MAX(popu);

SELECT MAX(popu) FROM tCity;

SELECT name FROM tCity WHERE popu = 974;

SELECT name FROM tCity WHERE popu = (SELECT MAX(popu) FROM tCity);

SELECT MAX(num) FROM tItem;

SELECT item FROM tItem WHERE num = 80;

SELECT item FROM tItem WHERE num = (SELECT MAX(num) FROM tItem);

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

-- 2중 서브쿼리도 가능하다
SELECT age FROM tMember WHERE member = 
(SELECT member FROM tOrder WHERE item = 
(SELECT item FROM tItem WHERE price = 70000));

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

-- 서브쿼리로 찾은 것 중 첫 항의 쿼리를 조회한다.
SELECT price FROM tItem WHERE item = 
(SELECT item FROM tOrder WHERE member = '향단' 
ORDER BY item OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY);

-- IN을 사용하면 여러 행을 동시에 탐색하 수 있다.
SELECT item, price FROM tItem WHERE item IN 
(SELECT item FROM tOrder WHERE member = '향단');

SELECT item, price FROM tItem WHERE item IN ('대추', '사과');

SELECT price FROM tItem WHERE item = (SELECT item FROM tOrder WHERE member = '이도령');

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

SELECT * FROM tStaff WHERE depart = '영업부' AND gender = '남';

SELECT * FROM tStaff WHERE depart = 
(SELECT depart FROM tStaff WHERE name = '안중근')
AND gender = (SELECT gender FROM tStaff WHERE name = '안중근');

-- 다중쿼리의 대표적인 예시. (항목1, 항목2) 이런식으로 묶어서 조회해 한번에 일치하는 녀석을 찾는다.
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);

SELECT * FROM tStaff S WHERE salary = 
(SELECT MAX(salary) FROM tStaff WHERE depart = S.depart);

-- 별명을 만들어주면 구분이 편하다.
SELECT T.* FROM tStaff T
INNER JOIN (SELECT depart, MAX(salary) ms FROM tStaff GROUP BY depart) M
ON T.depart = M.depart AND T.salary = M.ms;

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

-- ANY는 하나라도 있는 것을 의미한다. 때로는 MIN이나 MAX로 대체 가능하다
SELECT name FROM tStaff WHERE salary > ANY 
(SELECT salary FROM tStaff WHERE depart = '영업부');
SELECT name FROM tStaff WHERE salary > ALL 
(SELECT salary FROM tStaff WHERE depart = '영업부');

SELECT name FROM tStaff WHERE salary > 
(SELECT MIN(salary) FROM tStaff WHERE depart = '영업부');
SELECT name FROM tStaff WHERE salary > 
(SELECT MAX(salary) FROM tStaff WHERE depart = '영업부');

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

-- 의존 쿼리. item이 그 다음 괄호의 item을 지칭한다.
SELECT member, item, (SELECT price FROM tItem WHERE tItem.item = tOrder.item) price 
FROM tOrder;

-- 헷갈리지 않기 위해 각 테이블 별로 별명을 붙여주는 것이 좋다.
SELECT O.member, O.item, (SELECT price FROM tItem I WHERE I.item = O.item) price 
FROM tOrder O;

-- 조인을 사용하면 더 편하게 표현할 수 있다. (다음 글에서 다룰 예정)
SELECT O.member, O.item, I.price FROM tOrder O JOIN tItem I ON I.item = O.item; 

-- EXISTS는 조건을 만족하는 것이 있으면 TRUE를 반환하는 '표현식'이다. (단독으로 사용 X)
EXISTS (SELECT * FROM tCity WHERE area > 1000)

SELECT name FROM tCity WHERE EXISTS (SELECT * FROM tCity WHERE area > 1000);

SELECT name FROM tCity C WHERE EXISTS (SELECT * FROM tCity WHERE C.area > 1000);

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

SELECT * FROM tMember WHERE member IN (SELECT DISTINCT member FROM tOrder);

....EXISTS (SELECT item FROM tOrder O WHERE O.member = M.member);
....EXISTS (SELECT member FROM tOrder O WHERE O.member = M.member);
....EXISTS (SELECT '얼씨구' FROM tOrder O WHERE O.member = M.member);

-- 오라클에서는 인라인 뷰에 별명을 붙여 주어야 한다.
SELECT * FROM (SELECT * FROM tCity) A;

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 member, addr FROM tMember WHERE age < 19 AND money >= 100000;

-- 인라인 뷰를 이용하면 추출이 더 용이해진다.
SELECT * FROM (SELECT * FROM tStaff WHERE grade = '과장' OR grade = '부장') A 
WHERE A.score >= 70;

SELECT * FROM tStaff WHERE grade = '과장' OR grade = '부장' AND score >= 70;

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

SELECT * FROM (SELECT * FROM tCity WHERE metro = 'y') A;

SELECT * FROM (SELECT * FROM tCity WHERE metro = 'y') AS A;

SELECT name, popu * 10000 AS ingu FROM tCity;

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

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
)
WHERE ingu > 1000000;

SELECT name, (popu * 10000 / area) AS dens FROM tCity;

-- CASE문과 인라인 뷰를 이용해 보자.
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;

-- 이건 에러가 난다! dens와 CASE에서의 참조 레벨이 같기 때문이다.
SELECT name, (popu * 10000 / area) AS dens 
	,CASE 
		WHEN dens > 1000 THEN '고밀도'
		WHEN dens > 100 THEN '중밀도'
		ELSE '저밀도'
	END densgrade
FROM tCity;

-- dens를 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
) CD;

-- 해당 쿼리는 CASE를 반복사용하여 비효율적이다.
SELECT name, dens 
	,CASE 
		WHEN dens > 1000 THEN '고밀도'
		WHEN dens > 100 THEN '중밀도'
		ELSE '저밀도'
	END densgrade
	,
    CASE
    WHEN
        CASE 
            WHEN dens > 1000 THEN '고밀도'
            WHEN dens > 100 THEN '중밀도'
            ELSE '저밀도'
        END = '고밀도' THEN '8차로'
    WHEN
        CASE 
            WHEN dens > 1000 THEN '고밀도'
            WHEN dens > 100 THEN '중밀도'
            ELSE '저밀도'
        END = '중밀도' THEN '4차로'
    ELSE '2차로' 
    END roadplan
FROM
(
	SELECT name, (popu * 10000 / area) AS dens FROM tCity
) CD;

-- 인라인 뷰를 중첩하면 아래와 같이 표현 가능하다.
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
    ) CD
) CR;

-- UNION은 DISTINCT한 것을 제외하고 레코드를 합쳐준다.
SELECT * FROM tItem WHERE category = '식품' 
UNION 
SELECT * FROM tItem WHERE category = '가전';

-- 중복된 요소도 보고 싶다면 UNION ALL을 하자. UNION보다 더 빠르기도 하다.
SELECT DISTINCT depart FROM tStaff WHERE salary > 400 
UNION ALL
SELECT DISTINCT depart FROM tStaff WHERE score > 80;

-- UNION은 OR과 다르게 다른 테이블 끼리도 합치는 것이 가능하다.
SELECT * FROM tItem WHERE category = '식품' OR category = '가전';

SELECT * FROM tMember UNION SELECT * FROM tItem;

SELECT member FROM tMember 
UNION
SELECT name FROM tStaff 
UNION
SELECT name FROM tEmployee;

SELECT * FROM 부산대리점 UNION SELECT * FROM 서울대리점;

-- INTERSECT는 교집합이다.
SELECT name FROM tStaff WHERE depart = '영업부'
INTERSECT 
SELECT name FROM tStaff WHERE gender = '여';

SELECT name FROM tStaff
INTERSECT
SELECT member FROM tMember;

-- MINUS는 차집합이다.
SELECT name FROM tStaff WHERE depart = '영업부'
MINUS 
SELECT name FROM tStaff WHERE gender = '여';

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

-- 테이블을 복사 생성한 후, MERGE를 해보자.
오라클, 마리아 : CREATE TABLE tStaff2 AS SELECT * FROM tStaff;
MSSQL : SELECT * INTO tStaff2 FROM tStaff;

UPDATE tStaff2 SET salary = 500 WHERE name = '안창호';
UPDATE tStaff2 SET depart = '인사과' WHERE name = '성삼문';
DELETE FROM tStaff2 WHERE name = '홍길동';
INSERT INTO tStaff2 VALUES ('어우동', '총무부', '여', '20220401', '신입', 450, 0);

SELECT * FROM tStaff2
MINUS 
SELECT * FROM tStaff;

오라클, 마리아 : CREATE TABLE tCityNew AS SELECT * FROM tCity;
MSSQL : SELECT * INTO tCityNew FROM tCity;

UPDATE tCityNew SET popu = 1000 WHERE name = '서울';
UPDATE tCityNew SET area = 900 WHERE name = '부산';
DELETE FROM tCityNew WHERE name = '춘천';
INSERT INTO tCityNew VALUES ('이천',461,21,'n','경기');

-- 타겟, 소스 순으로 인자를 넣고 ON 절에서 매치하는 것을 체크한다.
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);

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

-- 아래와 같은 ON 절은 에러가 난다. ON절은 매치를 찾는 것이지 병합할 레코드를 추출하는 역할을 하지 않는다.
MERGE INTO tCity T USING tCityNew S ON (S.name = T.name AND S.region = '경기')
....

-- 간단한 MERGE를 위해 만든 테이블.
CREATE TABLE tCityPopu
(
	name CHAR(10) PRIMARY KEY,
	popu INT NULL
);

INSERT INTO tCityPopu VALUES ('서울',1000);
INSERT INTO tCityPopu VALUES ('부산',500);
INSERT INTO tCityPopu VALUES ('춘천',100);

MERGE INTO tCity C USING tCityPopu P ON (C.name = P.name)
WHEN MATCHED THEN UPDATE SET C.popu = P.popu;

UPDATE tCity SET popu = tCityPopu.popu FROM tCityPopu WHERE tCity.name = tCityPopu.name;

UPDATE tCity SET popu = S.popu FROM tCityPopu AS S WHERE tCity.name = S.name;

UPDATE tCity AS T SET T.popu = S.popu FROM tCityPopu AS S WHERE T.name = S.name;

UPDATE tCity SET popu = (SELECT popu FROM tCityPopu P WHERE P.name = tCity.name)
WHERE name IN (SELECT name FROM tCityPopu)