SQL 실행 순서 — SELECT 문이 내부에서 처리되는 과정

작성 순서:  SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

실행 순서:  FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

(5) SELECT    column, AGG(column)
(1) FROM      table
(2) WHERE     조건
(3) GROUP BY  column
(4) HAVING    AGG(column) 조건
(6) ORDER BY  column
(7) LIMIT     n

FROM users u
JOIN orders o ON u.id = o.user_id

WHERE u.age > 20 AND o.amount > 1000

-- 에러: WHERE에서 별칭 사용 불가
SELECT price * quantity AS total
FROM orders
WHERE total > 1000;  -- ERROR!

-- 올바른 방법
SELECT price * quantity AS total
FROM orders
WHERE price * quantity > 1000;

GROUP BY u.department

-- MySQL의 ONLY_FULL_GROUP_BY 모드 (기본 활성화)
-- GROUP BY에 없는 컬럼을 SELECT에 쓰면 에러

SELECT department, name, COUNT(*)  -- name은 어떤 값을 보여줘야 하는가?
FROM users
GROUP BY department;  -- ERROR! (ONLY_FULL_GROUP_BY 활성화 시)

-- 올바른 방법
SELECT department, COUNT(*), MAX(name)
FROM users
GROUP BY department;

HAVING COUNT(*) > 5

-- 나쁜 예: HAVING으로 개별 행 조건 처리
SELECT department, COUNT(*)
FROM users
GROUP BY department
HAVING department = 'Engineering';  -- 모든 행을 그룹화한 후 필터링

-- 좋은 예: WHERE로 먼저 필터링
SELECT department, COUNT(*)
FROM users
WHERE department = 'Engineering'    -- 그룹화 전에 필터링
GROUP BY department;

SELECT department, COUNT(*) AS member_count

ORDER BY member_count DESC

-- ORDER BY에서는 별칭 사용 가능
SELECT price * quantity AS total
FROM orders
ORDER BY total DESC;  -- OK!

LIMIT 10 OFFSET 20

-- 느린 페이징: OFFSET이 클수록 느려짐
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;

-- 빠른 페이징: 커서 기반
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

-- 논리적으로는 FROM → WHERE → SELECT
-- 물리적으로 옵티마이저가 인덱스를 사용하면:
EXPLAIN SELECT name FROM users WHERE id = 1;
-- type: const (인덱스로 바로 접근, 풀 스캔 없음)

-- EXPLAIN으로 물리적 실행 계획 확인
EXPLAIN FORMAT=TREE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 20
GROUP BY u.name
HAVING COUNT(o.id) > 3
ORDER BY order_count DESC
LIMIT 10;

-- 에러: WHERE는 SELECT보다 먼저 실행
SELECT YEAR(created_at) AS created_year FROM orders
WHERE created_year = 2026;

-- 수정: 원래 표현식 사용
SELECT YEAR(created_at) AS created_year FROM orders
WHERE YEAR(created_at) = 2026;

-- 더 좋은 방법: 인덱스 활용 가능한 형태
SELECT YEAR(created_at) AS created_year FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- 에러: WHERE 시점에는 그룹화 전
SELECT department, AVG(salary) FROM employees
WHERE AVG(salary) > 5000
GROUP BY department;

-- 수정: HAVING 사용
SELECT department, AVG(salary) FROM employees
GROUP BY department
HAVING AVG(salary) > 5000;

-- sql_mode에 ONLY_FULL_GROUP_BY가 활성화되어 있으면 에러
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;
-- name이 GROUP BY에 없고 집계 함수로 감싸지도 않음

-- 수정 방법 1: GROUP BY에 추가
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department, name;

-- 수정 방법 2: 집계 함수 사용
SELECT department, GROUP_CONCAT(name), COUNT(*)
FROM employees
GROUP BY department;

-- DISTINCT와 함께 SELECT에 없는 컬럼으로 정렬하면 에러
SELECT DISTINCT department FROM employees
ORDER BY salary;  -- department별로 어떤 salary를 기준으로 해야 하는가?

-- FROM절 서브쿼리 (파생 테이블): FROM 단계에서 실행
SELECT * FROM (SELECT department, COUNT(*) AS cnt FROM employees GROUP BY department) sub
WHERE sub.cnt > 5;

-- WHERE절 서브쿼리: WHERE 단계에서 실행
SELECT * FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE active = 1);

-- SELECT절 서브쿼리 (스칼라): SELECT 단계에서 각 행마다 실행 (성능 주의!)
SELECT name,
       (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;

-- 기본 EXPLAIN
EXPLAIN SELECT * FROM users WHERE age > 20;

-- 상세 포맷
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 20;

-- 실제 실행 통계 포함 (MySQL 8.0.18+)
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 20;