여러 과목의 점수가 각각 다른 열에 저장되어 있을 때, 이를 과목명과 점수 형태로 하나의 열에 나열해야 하는 경우가 자주 있습니다. 예를 들어 보고서를 만들거나 데이터를 정규화할 때 유용한 작업인데요. 이럴 때 UNION ALL을 활용하면 간단하게 해결할 수 있습니다.
1. 샘플 테이블 생성하기
먼저 학생 정보와 과목별 점수를 저장할 테이블을 생성합니다.
mysql> create table DemoTable729 (
StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
StudentName varchar(100),
MySQLMarks int,
CMarks int,
JavaMarks int
);
Query OK, 0 rows affected (0.40 sec)2. 테스트 데이터 삽입하기
INSERT 문을 사용해 학생별 점수 데이터를 입력합니다.
mysql> insert into DemoTable729(StudentName,MySQLMarks,CMarks,JavaMarks) values('Chris',94,67,75);
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable729(StudentName,MySQLMarks,CMarks,JavaMarks) values('Robert',45,99,54);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable729(StudentName,MySQLMarks,CMarks,JavaMarks) values('David',57,89,43);
Query OK, 1 row affected (0.12 sec)3. 저장된 데이터 확인하기
SELECT 문으로 테이블의 전체 레코드를 조회해 보겠습니다.
mysql> select *from DemoTable729;
실행 결과는 다음과 같습니다. 현재는 과목별 점수가 세 개의 열(MySQLMarks, CMarks, JavaMarks)에 나누어 저장되어 있습니다.
+-----------+-------------+------------+--------+-----------+ | StudentId | StudentName | MySQLMarks | CMarks | JavaMarks | +-----------+-------------+------------+--------+-----------+ | 1 | Chris | 94 | 67 | 75 | | 2 | Robert | 45 | 99 | 54 | | 3 | David | 57 | 89 | 43 | +-----------+-------------+------------+--------+-----------+ 3 rows in set (0.00 sec)
4. UNION ALL로 점수를 단일 열로 변환하기
핵심은 같은 테이블을 세 번 조회하면서, 각각의 SELECT 문에서 과목명을 문자열 리터럴로 지정하고 해당 과목의 점수 열만 선택하는 것입니다. 그런 다음 UNION ALL로 세 결과 집합을 위아래로 연결하면 됩니다.
mysql> select StudentId,StudentName,'MySQL' as SubjectName, MySQLMarks as Score from DemoTable729 UNION ALL select StudentId,StudentName, 'C' as SubjectName, CMarks as Score from DemoTable729 UNION ALL select StudentId,StudentName, 'Java' as SubjectName, JavaMarks as Score from DemoTable729;
실행 결과, 모든 과목의 점수가 SubjectName과 Score 두 개의 열로 정리되어 출력됩니다.
+-----------+-------------+-------------+-------+ | StudentId | StudentName | SubjectName | Score | +-----------+-------------+-------------+-------+ | 1 | Chris | MySQL | 94 | | 2 | Robert | MySQL | 45 | | 3 | David | MySQL | 57 | | 1 | Chris | C | 67 | | 2 | Robert | C | 99 | | 3 | David | C | 89 | | 1 | Chris | Java | 75 | | 2 | Robert | Java | 54 | | 3 | David | Java | 43 | +-----------+-------------+-------------+-------+ 9 rows in set (0.00 sec)
정리 및 참고 사항
이처럼 UNION ALL을 사용하면 넓은 형식(wide format)의 테이블을 긴 형식(long format)으로 손쉽게 변환할 수 있습니다. 몇 가지 주의할 점은 다음과 같습니다.
- UNION 대신 UNION ALL을 사용하면 중복 제거 작업을 생략하므로 성능상 더 유리합니다.
- 각 SELECT 문에서 첫 번째 SELECT의 별칭(SubjectName, Score)이 최종 결과의 열 이름으로 사용됩니다.
- 과목 수가 많다면 SELECT 문을 과목 수만큼 추가하면 되며, 필요에 따라 ORDER BY StudentId, SubjectName을 붙여 원하는 순서로 정렬할 수도 있습니다.