[SQL] CASE / UNION 실행 계획 비교

0. 목표

 - CASE / UNION 실행 계획 비교

1. 실습

 1) 테이블 생성

CREATE TABLE sql_test.union_case
(
	MakeYear datetime,
	price1 int,
    price2 int2
);

 2) 데이터 삽입

INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2010-01-01', 100, 0);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2010-01-01', 100, 0);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2010-01-01', 100, 0);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2010-01-01', 100, 0);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2010-01-01', 100, 0);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2010-01-01', 100, 0);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2010-01-01', 100, 0);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2010-01-01', 100, 0);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2010-01-01', 100, 0);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2010-01-01', 100, 0);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2021-01-01', 0, 100);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2021-01-01', 0, 100);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2021-01-01', 0, 100);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2021-01-01', 0, 100);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2021-01-01', 0, 100);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2021-01-01', 0, 100);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2021-01-01', 0, 100);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2021-01-01', 0, 100);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2021-01-01', 0, 100);
INSERT INTO sql_test.union_case(MakeYear, price1, price2) VALUES('2021-01-01', 0, 100);

 3) 데이터 확인

SELECT * FROM sql_test.union_case;

 4) 문제

  - 2020-01-01 이전은 price1 을 price 로 하고, 이후는 price2 를 price 로 지정

  (1) UNION ALL 이용

SELECT
	MakeYear, price1 AS price
FROM sql_test.union_case
WHERE MakeYear < '2020-01-01'

UNION ALL

SELECT
	MakeYear, price2 AS price
FROM sql_test.union_case
WHERE MakeYear >= '2020-01-01';

  (2) CASE 이용

SELECT
	MakeYear,
    CASE
		WHEN MakeYear < '2020-01-01' THEN price1
        WHEN MakeYear >= '2020-01-01' THEN price2
	END AS price

FROM sql_test.union_case;

 5) EXPLAIN 을 통한 실행 계획 확인

  (1) UNION ALL

EXPLAIN
SELECT
	MakeYear, price1 AS price
FROM sql_test.union_case
WHERE MakeYear < '2020-01-01'

UNION ALL

SELECT
	MakeYear, price2 AS price
FROM sql_test.union_case
WHERE MakeYear >= '2020-01-01';

  (2) CASE

EXPLAIN
SELECT
	MakeYear,
    CASE
		WHEN MakeYear < '2020-01-01' THEN price1
        WHEN MakeYear >= '2020-01-01' THEN price2
	END AS price

FROM sql_test.union_case;

2. 참고 

 - SQL 레벨업

 - [MySQL] 실행계획 (explain) 보는법