학습 / 치트 시트
테스터를 위한 SQL 치트 시트
UI나 API 뒤에 있는 데이터를 검증할 때 테스터가 쓰는 SQL입니다. 레코드를 찾고, 값이 바뀌었는지 확인하고, 있어서는 안 되는 중복·고아 레코드·NULL을 찾아냅니다. 문법은 PostgreSQL, MySQL 8, SQLite에서 동작하는 표준 SQL이며, 데이터베이스마다 다른 부분은 노트에 표시했습니다.
표준 SQL. PostgreSQL 16, MySQL 8, SQLite 3에서 검증 기준으로 검증
행 찾기
| 작업 | 코드 |
|---|---|
| 테이블에서 컬럼 조회 | SELECT id, email FROM users; |
| 필터링 | SELECT * FROM orders WHERE status = 'paid' AND total > 100; |
| 여러 값 중 하나 | WHERE status IN ('paid', 'shipped') |
| 범위 (양 끝 포함) | WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31' |
| 패턴 | WHERE email LIKE '%@example.com' |
| 값이 없음 | WHERE phone IS NULL |
| 최신순 정렬 | ORDER BY created_at DESC |
| 처음 10행 | LIMIT 10 |
| 고유한 값 | SELECT DISTINCT status FROM orders; |
SQL Server는 LIMIT 대신 SELECT TOP 10을 씁니다. 타임스탬프에 BETWEEN을 쓰면 종료일은 자정 시각만 포함되므로, 하루 전체를 포함하려면 < 다음 날 조건을 쓰세요.
집계
| 작업 | 코드 |
|---|---|
| 행 개수 세기 | SELECT COUNT(*) FROM orders; |
| NULL이 아닌 값 세기 | SELECT COUNT(shipped_at) FROM orders; |
| 고유한 값 세기 | SELECT COUNT(DISTINCT user_id) FROM orders; |
| 그룹별 합계 | SELECT status, COUNT(*), SUM(total) FROM orders GROUP BY status; |
| 그룹 필터링 | SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 5; |
| 최소, 최대, 평균 | SELECT MIN(total), MAX(total), AVG(total) FROM orders; |
WHERE는 그룹화 전에 행을 거르고, HAVING은 그룹화 후에 그룹을 거릅니다.
조인
| 작업 | 코드 |
|---|---|
| 일치하는 행만 | SELECT o.id, u.email FROM orders o JOIN users u ON u.id = o.user_id; |
| 왼쪽 행은 모두 유지 | SELECT u.email, o.id FROM users u LEFT JOIN orders o ON o.user_id = u.id; |
| 일치하는 행이 없는 행 (안티 조인) | SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL; |
| 같은 결과를 NOT EXISTS로 | SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id); |
일대다 키로 조인하면 "일" 쪽 행이 반복됩니다. 조인 후 SUM()이 너무 크게 나온다면 조인으로 행이 불어난 것입니다.
테스터가 자주 실행하는 데이터 검사
| 작업 | 코드 |
|---|---|
| 중복 | SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1; |
| 고아 레코드 (부모 없는 자식) | SELECT o.* FROM orders o LEFT JOIN users u ON u.id = o.user_id WHERE u.id IS NULL; |
| 필수 필드가 비어 있음 | SELECT * FROM users WHERE email IS NULL OR TRIM(email) = ''; |
| 범위를 벗어난 값 | SELECT * FROM order_items WHERE quantity <= 0 OR unit_price < 0; |
| 헤더 합계 ≠ 품목 합계 | SELECT o.id FROM orders o JOIN order_items i ON i.order_id = o.id GROUP BY o.id, o.total HAVING o.total <> SUM(i.quantity * i.unit_price); |
| UI 조작 후 행이 생성됐는지 | SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 1; |
NULL 함정
| 함정 | 결과 |
|---|---|
| WHERE col = NULL | 절대 참이 되지 않습니다. IS NULL / IS NOT NULL을 쓰세요. |
| WHERE status <> 'paid' | status가 NULL인 행은 빠집니다. 포함하려면 OR status IS NULL을 추가하세요. |
| NOT IN (NULL을 반환하는 서브쿼리) | 행이 하나도 반환되지 않습니다. 대신 NOT EXISTS를 쓰세요. |
| COUNT(col)과 COUNT(*)의 차이 | COUNT(col)은 NULL을 건너뛰고, COUNT(*)는 모든 행을 셉니다. |
| NULL이 포함된 산술 연산 | price + NULL은 NULL입니다. 0이 올바른 기본값이라면 COALESCE(col, 0)로 감싸세요. |
윈도 함수
| 작업 | 코드 |
|---|---|
| 그룹별 행 번호 매기기 | ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) |
| 그룹별 최신 행 | SELECT * FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM orders o) t WHERE rn = 1; |
| 동점을 고려한 순위 | RANK() OVER (ORDER BY total DESC) / DENSE_RANK() … |
| 이전 행의 값 | LAG(status) OVER (PARTITION BY test_name ORDER BY run_at) |
| 누적 합계 | SUM(total) OVER (ORDER BY created_at) |
PostgreSQL, MySQL 8 이상, SQLite 3.25 이상, SQL Server에서 지원합니다. 윈도 함수는 WHERE에서 쓸 수 없으므로 바깥 쿼리에서 필터링하세요.
안전하게 데이터 변경하기
| 작업 | 코드 |
|---|---|
| 삽입 | INSERT INTO users (email, name) VALUES ('qa+1@example.com', 'QA One'); |
| 수정 (항상 WHERE와 함께) | UPDATE orders SET status = 'cancelled' WHERE id = 1043; |
| 삭제 (항상 WHERE와 함께) | DELETE FROM sessions WHERE user_id = 42; |
| 실행해 보고 되돌리기 | BEGIN; UPDATE orders SET status = 'cancelled' WHERE id = 1043; SELECT status FROM orders WHERE id = 1043; ROLLBACK; |
UPDATE나 DELETE 전에 같은 WHERE 조건으로 SELECT를 먼저 실행해 행 개수를 확인하세요.