MySQL SELECT 기초 학습노트: WHERE·NULL·LIKE·ORDER BY·LIMIT

2022년 5월 5일에 W3Schools의 MySQL tutorial을 따라가며 번역·정리했던 첫날 기록을 현재 MySQL 8.4 LTS 문서 기준으로 다시 다듬었다. 원래 제목의 ‘W3C’는 web standard body인 W3C와 tutorial site인 W3Schools를 혼동한 표현이었다.

이번에는 명령 목록을 길게 나열하기보다, table에서 원하는 row를 안전하고 재현 가능하게 읽는 흐름에 초점을 맞췄다.

Sample Table

CREATE TABLE customers (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(100),
    score INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

relational database의 table은 column으로 구조를 정의하고 row로 개별 record를 저장한다. table 사이 관계는 primary key와 foreign key 같은 constraint로 표현할 수 있다. 관계가 있다는 말은 단지 이름이 같은 column이 있다는 뜻이 아니라, data model과 constraint가 연결을 정의한다는 뜻이다.

SELECT와 WHERE

SELECT는 읽을 expression을, FROM은 source table을, WHERE는 각 row가 만족해야 할 condition을 정한다.

SELECT id, name, city, score
FROM customers
WHERE score >= 80;

SELECT *는 탐색 단계에서는 편하지만 application query에서는 필요한 column을 명시하는 편이 data contract와 network·I/O 비용을 파악하기 쉽다.

자주 쓰는 condition은 다음처럼 조합한다.

SELECT id, name, city
FROM customers
WHERE city IN ('Seoul', 'Busan')
  AND score BETWEEN 80 AND 100;

• BETWEEN은 양쪽 경계를 포함한다.
• 같음은 =, 같지 않음은 <> 또는 MySQL의 !=를 쓸 수 있다.
• AND가 OR보다 먼저 평가되므로 둘을 섞으면 괄호로 의도를 드러낸다.
• user input은 문자열 결합이 아니라 driver의 parameter binding으로 전달한다.

SQL keyword는 대소문자를 구분하지 않지만, identifier와 문자열 비교까지 언제나 case-insensitive인 것은 아니다. MySQL의 table name 동작은 operating system과 설정의 영향을 받고 문자열 비교는 character set과 collation에 좌우된다.

NULL은 빈 문자열이나 0이 아니다

NULL은 missing 또는 unknown value를 나타낸다. = NULL이나 <> NULL 비교 결과로 원하는 row를 찾을 수 없으므로 IS NULL, IS NOT NULL을 쓴다.

SELECT id, name
FROM customers
WHERE city IS NULL;

column 값을 생략했다고 항상 NULL이 자동 저장되는 것은 아니다. NOT NULL, explicit·implicit default, SQL mode와 statement 형태에 따라 insert가 성공하거나 실패하고 다른 default가 들어갈 수 있다. schema constraint를 먼저 확인해야 한다.

aggregate에서도 NULL 의미를 구분한다.

SELECT
    COUNT(*) AS all_rows,
    COUNT(city) AS rows_with_city,
    AVG(score) AS average_score
FROM customers;

COUNT(*)은 row 수를 세고 COUNT(city)는 city IS NOT NULL인 값만 센다.

LIKE와 Wildcard

LIKE는 pattern matching에 사용한다.

• %: 길이 0을 포함한 임의의 문자열
• _: 문자 하나

SELECT id, name
FROM customers
WHERE name LIKE 'Kim%';

이 query는 Kim으로 시작하는 값을 찾는다. 실제 대소문자·accent 구분은 column collation에 달려 있다. %나 _ 자체를 찾으려면 escape 규칙을 명시하고 client·SQL mode의 backslash 처리까지 확인해야 한다.

앞에 wildcard가 붙은 LIKE '%word'나 LIKE '%word%'는 일반적인 B-tree index의 선두 탐색을 활용하기 어렵다. data가 커지면 EXPLAIN으로 access path를 확인하고 full-text search나 별도 search system이 맞는지 판단한다. database가 row를 찾는 방식은 Full Scan과 B+Tree에서 이어서 볼 수 있다.

ORDER BY와 LIMIT

정렬 기준이 없으면 row 반환 순서를 보장할 수 없다. pagination이나 “최신 N개”처럼 순서가 중요하다면 ORDER BY를 명시하고 동점까지 결정할 tie-breaker를 둔다.

SELECT id, name, created_at
FROM customers
ORDER BY created_at DESC, id DESC
LIMIT 20;

LIMIT은 반환 row 수를 제한하지만 어떤 row가 선택될지는 ordering에 달려 있다. 큰 offset pagination은 앞 row를 건너뛰는 비용이 커질 수 있어, 규모가 커지면 마지막 sort key를 기준으로 이어 읽는 keyset pagination도 검토한다.

INSERT·UPDATE·DELETE를 안전하게 실행하기

INSERT INTO customers (name, city, score)
VALUES ('Ben', 'Seoul', 90);

UPDATE customers
SET score = 95
WHERE id = 1;

DELETE FROM customers
WHERE id = 1;

UPDATE와 DELETE에서 WHERE가 없으면 조건에 맞는 일부가 아니라 대상 table의 모든 row에 영향을 줄 수 있다. 실무에서는 다음 순서를 습관으로 두는 편이 안전하다.

1. 동일한 WHERE로 먼저 SELECT하여 대상과 row 수를 확인한다.
2. transaction과 rollback 가능 여부를 확인한다.
3. primary key나 의도한 index를 쓰는지 EXPLAIN으로 본다.
4. application에서는 영향 row 수를 검사한다.

WHERE를 붙였다는 사실만으로 안전한 것도 아니다. condition이 너무 넓거나 operator precedence를 오해하면 예상보다 많은 row가 바뀔 수 있다.

첫날 기록에서 바로잡은 것

• 비교 operator >와 <가 뒤바뀐 표기를 고쳤다.
• “SQL은 대소문자를 구분하지 않는다”를 keyword·identifier·collation의 문제로 나눴다.
• NULL이 column 생략 시 항상 자동 저장된다는 설명을 constraint와 default 기준으로 교정했다.
• MIN()을 MIX()로 적은 오타를 바로잡고 COUNT(*)과 COUNT(column)을 구분했다.
• ORDER BY 없는 result order와 LIMIT의 비결정성을 추가했다.

다음 범위의 LIKE, wildcard, alias, join 학습 기록은 MySQL 정리 2·3일차로 이어진다.

참고 자료

• MySQL 8.4 Reference Manual: SELECT Statement
• MySQL 8.4 Reference Manual: Working with NULL Values
• MySQL 8.4 Reference Manual: Pattern Matching
• MySQL 8.4 Reference Manual: UPDATE Statement