SQL 중급 완전 정리: ORDER BY, GROUP BY, HAVING, INNER JOIN

SELECT country, ROUND(AVG(budget), 2) AS average_budget
FROM films
GROUP BY country
HAVING AVG(budget) >= 1000000000
ORDER BY average_budget DESC;

이 글에서는 SQL에서 데이터 분석의 핵심 기능인 정렬(ORDER BY), 그룹 분석(GROUP BY), 그룹 필터(HAVING), 테이블 결합(JOIN) 을 정리합니다. 실무 분석·리포트 작성·데이터 전처리에서 가장 중요한 내용들입니다.

1. ORDER BY (정렬)

기본 오름차순 ASC, 내림차순 DESC.

SELECT name 
FROM people
ORDER BY name ASC;

SELECT title, duration
FROM films
ORDER BY duration DESC;

다중 정렬:

SELECT release_year, duration, title
FROM films
ORDER BY release_year, duration;

2. GROUP BY (그룹별 집계)

SELECT release_year, COUNT(*) AS film_count
FROM films
GROUP BY release_year;

SELECT release_year, AVG(duration) AS avg_duration
FROM films
GROUP BY release_year;

복합 그룹:

SELECT release_year, country, MAX(budget) AS max_budget
FROM films
GROUP BY release_year, country
ORDER BY release_year, country;

3. HAVING (그룹 조건 필터링)

WHERE는 개별 행(row) 필터, HAVING은 GROUP BY 결과 필터입니다.

SELECT country, COUNT(DISTINCT certification) AS certification_count
FROM films
GROUP BY country
HAVING COUNT(DISTINCT certification) > 10;

SELECT country, ROUND(AVG(budget), 2) AS average_budget
FROM films
GROUP BY country
HAVING AVG(budget) >= 1000000000
ORDER BY average_budget DESC;

다중 집계 필터:

SELECT release_year, AVG(budget) AS avg_budget, AVG(gross) AS avg_gross
FROM films
WHERE release_year > 1990
GROUP BY release_year
HAVING AVG(budget) > 60000000
ORDER BY avg_gross DESC
LIMIT 1;

4. INNER JOIN (테이블 결합)

두 테이블에서 조건에 맞는 공통 행만 가져옵니다.

기본 형태:

SELECT *
FROM cities
INNER JOIN countries
ON cities.country_code = countries.code;

Alias 사용:

SELECT
  cities.name AS city,
  countries.name AS country,
  countries.region
FROM cities
INNER JOIN countries
ON cities.country_code = countries.code;

Alias + JOIN:

SELECT
  c.code AS country_code,
  name,
  year,
  inflation_rate
FROM countries AS c
INNER JOIN economies AS e
ON c.code = e.code;

USING 사용:

SELECT 
  c.name AS country,
  l.name AS language,
  official
FROM countries AS c
INNER JOIN languages AS l
USING (code);

 

본 글에서는 SQL 분석의 핵심인 ORDER BY, GROUP BY, HAVING, JOIN 을 정리했습니다.
특히 GROUP BY + HAVING은 실무 데이터 분석에서 가장 많이 사용되며, JOIN은 테이블 간 관계를 연결해 데이터 분석의 폭을 넓혀 줍니다.
이제 SQL의 기초와 중급 내용을 모두 익혔으므로, 다음 단계로는 Window Functions(윈도우 함수) 또는 Subquery(서브쿼리)를 학습하면 좋습니다.