MySQL 8.4 쿼리 문법: ALL·INSERT SELECT·CASE·NULL 처리

2022년에 W3Schools의 MySQL 내용을 번역하며 ALL, INSERT INTO SELECT, CASE, NULL 함수와 연산자를 짧게 메모했다. 다시 보니 문법 이름만 남아 있고 ALL의 두 용법, NULL이 섞인 비교, MySQL 주석의 공백 규칙처럼 실수하기 쉬운 부분이 빠져 있었다.

이번 정리는 장기 지원 계열인 MySQL 8.4 공식 매뉴얼을 기준으로 한다. 예제는 문법을 설명하기 위한 것으로, 운영 데이터베이스에서 직접 실행한 결과는 아니다.

ALL은 두 문맥에서 뜻이 다르다

SELECT ALL은 중복 행을 유지한다

SELECT ALL department
FROM employees;

SELECT ALL은 조건에 맞는 모든 행을 반환하며 중복을 제거하지 않는다. ALL은 기본값이므로 일반적으로 생략한다.

SELECT department
FROM employees;

중복을 제거하려면 DISTINCT를 쓴다.

SELECT DISTINCT department
FROM employees;

비교 연산자 뒤의 ALL은 모든 하위 쿼리 값과 비교한다

SELECT product_id, price
FROM products
WHERE price > ALL (
    SELECT price
    FROM products
    WHERE category = 'clearance'
);

operand 비교연산자 ALL (subquery)는 하위 쿼리가 반환한 모든 값에 대해 비교가 참일 때 참이 된다.

여기에는 두 가지 경계가 있다.

• 하위 쿼리가 빈 집합이면 > ALL (empty set) 같은 조건은 참이 된다.
• 하위 쿼리에 NULL이 섞이면 결과가 UNKNOWN, 즉 SQL의 NULL이 될 수 있다.

예를 들어 비교 대상에 더 큰 값이 하나 있으면 거짓이지만, 거짓을 결정할 값은 없고 NULL만 남으면 참이 아니라 NULL이 될 수 있다. NOT IN도 <> ALL과 연결되므로 하위 쿼리의 NULL을 특히 주의한다.

INSERT INTO SELECT는 조회 결과를 기존 테이블에 추가한다

INSERT INTO archived_orders (id, customer_id, total_amount)
SELECT id, customer_id, total_amount
FROM orders
WHERE created_at < '2025-01-01';

기존 메모에는 두 테이블의 데이터 타입이 일치해야 한다고 적었다. 더 정확하게는 SELECT 결과의 열 수와 순서가 대상 열 목록에 맞아야 하고, 각 값이 대상 열 타입과 호환돼야 한다.

확인할 항목은 다음과 같다.

• 대상 열 목록을 명시했는가?
• 조회 열의 순서와 대상 열의 순서가 같은가?
• 문자열을 숫자로 바꾸는 식의 암묵적 변환이 생기지 않는가?
• NOT NULL, UNIQUE, 외래 키 같은 제약을 만족하는가?
• 기본값과 자동 증가 열을 의도대로 처리하는가?
• 트리거가 실행될 때 추가 부작용이 없는가?

원본 테이블의 기존 행은 이 문장만으로 수정되지 않지만, 대상 테이블에는 새 행이 추가된다. 대량 복사라면 실행 계획, 잠금, 트랜잭션 로그와 롤백 비용을 별도로 검토해야 한다.

운영 환경에서는 먼저 같은 SELECT만 실행해 열과 건수를 확인하고, 필요한 경우 작은 범위와 트랜잭션으로 검증한다. 단순 조회 확인과 실제 삽입 완료는 구분해서 기록한다.

CASE는 처음으로 참인 분기의 값을 반환한다

조건을 순서대로 평가하는 searched CASE는 다음과 같다.

SELECT
    order_id,
    CASE
        WHEN total_amount >= 100000 THEN 'large'
        WHEN total_amount >= 50000 THEN 'medium'
        ELSE 'small'
    END AS order_size
FROM orders;

위에서부터 평가해 처음 참인 WHEN의 결과를 반환한다. 어느 조건도 참이 아니고 ELSE도 없으면 결과는 NULL이다. 범위 조건이 겹칠 때는 더 좁거나 우선해야 하는 조건을 먼저 둔다.

기준값 하나와 비교하는 simple CASE도 있다.

SELECT
    status,
    CASE status
        WHEN 'P' THEN 'pending'
        WHEN 'D' THEN 'done'
        ELSE 'unknown'
    END AS status_label
FROM orders;

CASE 표현식과 저장 프로그램 안의 CASE 문장은 문법과 종료 규칙이 다르므로 공식 문서에서 현재 문맥을 확인한다.

IFNULL과 COALESCE는 인수 개수와 이식성이 다르다

IFNULL(expr1, expr2)는 첫 번째 값이 NULL이면 두 번째 값을 반환한다.

SELECT IFNULL(nickname, name) AS display_name
FROM users;

COALESCE는 여러 인수 가운데 처음으로 NULL이 아닌 값을 반환한다.

SELECT COALESCE(nickname, name, email, 'unknown') AS display_name
FROM users;

단순히 화면에 표시할 대체값을 정하는 데는 편리하지만, 검색 조건의 열에 함수를 감싸면 인덱스 사용 방식이 달라질 수 있다. 성능이 중요한 조건이라면 EXPLAIN으로 실제 실행 계획을 확인한다.

또한 NULL은 빈 문자열이나 0이 아니다. column = NULL이 아니라 IS NULL 또는 IS NOT NULL을 사용한다.

SELECT *
FROM users
WHERE deleted_at IS NULL;

MySQL 주석에서 -- 뒤에는 공백이 필요하다

MySQL은 세 가지 주석 형태를 지원한다.

SELECT 1 + 1;        # 줄 끝까지 주석
SELECT 1 + 1;        -- 줄 끝까지 주석
SELECT 1 /* 인라인 주석 */ + 1;

MySQL에서 -- 주석은 두 번째 하이픈 뒤에 공백이나 제어 문자가 있어야 한다.

SELECT 1--1;   -- 주석이라고 가정하면 안 된다
SELECT 1 -- 설명
;

중첩된 /* ... */ 주석은 지원 대상으로 기대하지 않는다. /*! ... */는 일반 주석처럼 보이지만 MySQL 서버가 내부 내용을 실행할 수 있는 버전별 확장 문법이므로, 외부에서 받은 SQL 파일을 검토할 때 주의한다.

연산자는 모양보다 NULL 동작을 함께 본다

구분	연산자 예	주의점
산술	+, -, *, /, %	0으로 나누기와 타입 변환을 확인한다.
비교	=, <>, !=, <, <=, >, >=	피연산자에 NULL이 있으면 결과가 NULL일 수 있다.
NULL 안전 동등 비교	<=>	두 값이 모두 NULL이면 참을 반환하는 MySQL 연산자다.
논리	AND, OR, NOT	TRUE·FALSE뿐 아니라 UNKNOWN을 고려한다.
비트	&, `	, ^, ~`, <<, >>

복잡한 조건은 연산자 우선순위에 기대기보다 괄호로 의도를 드러낸다. 조건이 맞는지만 보지 말고 NULL, 빈 집합, 중복, 타입 변환을 경계값으로 넣어 확인한다.

참고 자료

• MySQL 8.4: SELECT Statement
• MySQL 8.4: Subqueries with ALL
• MySQL 8.4: INSERT ... SELECT
• MySQL 8.4: Flow Control Functions
• MySQL 8.4: Comparison Functions and Operators
• MySQL 8.4: Comments