[MySQL] 임시 테이블을 생성하는 with

아래의 코드들은 온라인, 오프라인 매장을 갖고있는 쇼핑몰의 데이터를 예시로 진행한다.

• 온라인 오프라인 고객 모두 이름과 주소, 연락처를 갖고있다.
• 동일한 이름이 같은 지역에 존재해도 핸드폰 번호가 다르다면 다른 사람이다.
• 동일한 이름이 같은 번호를 가질 수 없다.
• 두 테이블의 동일 인물은 반포에사는 "박" 이름을 가진 사람이다.

1. OFF_LINE 테이블에 데이터 삽입
  insert into `OFF_LINE` (offline_id, offline_name, offline_address, offline_phone) 
    values (1, '정', '용산', '010-1234-1234'),
    (2, '김', '마포', '010-1111-1234'),
    (3, '박', '반포', '010-1234-1111'),
    (4, '김', '대구', '010-0000-0101');

2. ON_LINE 테이블에 데이터 삽입
    insert into `ON_LINE` (online_id, online_name, online_address, online_phone) 
    values (1, '정', '김해', '010-5555-6666'),
    (2, '김', '대구', '010-7777-7777'),
    (3, '박', '반포', '010-1234-1111');

오프라인과 온라인 모두 USERS 테이블로 합쳐서 확인하고 싶다면?

WITH USERS AS (
    SELECT 
        offline_name AS Name,
        offline_phone AS Phone,
        offline_address AS Address
    FROM `OFF_LINE`

UNION ALL

    SELECT 
        online_name AS Name,
        online_phone AS Phone,
        online_address AS Address
    FROM `ON_LINE`
)

SELECT * from USERS
ORDER BY Name, Phone; # name, phone 순서로 오름차순 정렬한다.
ORDER BY 1, 2, 3; # 위에 select 구문에서 컬럼을 설정한 순서대로 정렬, 즉 바로 위의 ORDER BY Name, Phone 과 같다.

두 개의 테이블이 합쳐져 나온 것을 확인할 수 있지만 이름, 주소, 연락처 모두 동일한 인물이 존재한다.
이럴 경우, UNION ALL에서 ALL을 지우고 UNION만 사용하면 중복된 데이터를 제외할 수 있다.

join을 사용하여 간단하게 동일인물을 찾을 수도 있을 것이다. 하지만 규모가 크고 복잡한 쿼리의 경우 읽기 쉽고 관리하기 쉽게 만드는 데 유용하다. 참고로 with를 사용해 만든 테이블은 실제 테이블로 생성되진 않고 코드가 실행중에만 메모리에 존재한다.