본문 바로가기
Database/SQL

SELECT

by curious week 2026. 2. 7. 
SELECT 컬럼명, 집계함수(컬럼명)
FROM 테이블명
WHERE 조건식 -- (선택) 그룹화 전 행 필터링
GROUP BY 컬럼명
HAVING 그룹조건식 -- (선택) 그룹화 후 결과 필터링
ORDER BY 컬럼명; -- (선택) 결과 정렬
실행순서
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

평균 일일 대여 요금 구하기(AVG, ROUND)

CAR_RENTAL_COMPANY_CAR 테이블에서 자동차 종류가 'SUV'인 자동차들의 평균 일일 대여 요금을 출력하는 SQL문을 작성해주세요. 이때 평균 일일 대여 요금은 소수 첫 번째 자리에서 반올림하고, 컬럼명은 AVERAGE_FEE 로 지정해주세요.

SELECT ROUND(AVG(DAILY_FEE), 0) as AVERAGE_FEE 
FROM CAR_RENTAL_COMPANY_CAR 
WHERE CAR_TYPE = 'SUV';

 

  • ROUND(AVG(컬럼), 0) - 평균을 정수(소수점 첫째 자리에서 반올림)로 표시
  • AVG - SELECT 절 또는 HAVING 절에서 사용, NULL 값은 무시

인기있는 아이스크림(ORDER BY)

상반기에 판매된 아이스크림의 맛을 총주문량을 기준으로 내림차순 정렬하고 총주문량이 같다면 출하 번호를 기준으로 오름차순 정렬하여 조회하는 SQL 문을 작성해주세요.

SELECT FLAVOR 
FROM FIRST_HALF 
ORDER BY TOTAL_ORDER DESC;

또는

SELECT FLAVOR 
FROM FIRST_HALF 
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID;
  • ORDER BY는 앞 기준 → 같은 값일 때 다음 기준 순으로 적용됨.

조건에 맞는 도서 리스트 출력하기(DATE)

BOOK 테이블에서 2021년에 출판된 '인문' 카테고리에 속하는 도서 리스트를 찾아서 도서 ID(BOOK_ID), 출판일 (PUBLISHED_DATE)을 출력하는 SQL문을 작성해주세요. 결과는 출판일을 기준으로 오름차순 정렬해주세요.

  • PUBLISHED_DATE의 데이트 포맷이 예시와 동일해야 정답처리 됩니다.
-- MySQL
SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE,'%Y-%m-%d') as PUBLISHED_DATE
FROM BOOK
WHERE PUBLISHED_DATE >= '2021-01-01'
AND PUBLISHED_DATE < '2022-01-01'
AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE;

또는

-- MySQL
SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%M-%D')as PUBLISHED_DATE
FROM BOOK
WHERE YEAR(PUBLISHED_DATE) = 2021
AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE ASC;

Oracle은 MySQL과 포맷지정 방식이 다르고, Date 비교 시 DATE를 써야함.

-- Oracle
SELECT BOOK_ID,
       TO_CHAR(PUBLISHED_DATE, 'YYYY-MM-DD') AS PUBLISHED_DATE
FROM BOOK
WHERE PUBLISHED_DATE >= DATE '2021-01-01'
AND PUBLISHED_DATE <  DATE '2022-01-01'
AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE;

또는

-- Oracle
SELECT BOOK_ID,
       TO_CHAR(PUBLISHED_DATE, 'YYYY-MM-DD') AS PUBLISHED_DATE
FROM BOOK
WHERE EXTRACT(YEAR FROM PUBLISHED_DATE) = 2021
AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE;
  • YEAR(Date Column)이나 EXTRACT(YEAR FROM Date Column) 방식은 성능 저하 우려가 있음.
    • 인덱스 못 탐
    • 대용량에서 느림
  • WHERE + ORDER BY 같은 인덱스일 때 가장 빠르게 정렬 된다.
-- 빠른 정렬 예시
WHERE created_at >= '2025-01-01'
ORDER BY created_at, id
-- (PK로 정렬하더라도) 보다 느리다.
WHERE price > 1000
ORDER BY id

12세 이하인 여자 환자 목록 출력하기(NVL, IFNULL)

PATIENT 테이블에서 12세 이하인 여자환자의 환자이름, 환자번호, 성별코드, 나이, 전화번호를 조회하는 SQL문을 작성해주세요. 이때 전화번호가 없는 경우, 'NONE'으로 출력시켜 주시고 결과는 나이를 기준으로 내림차순 정렬하고, 나이 같다면 환자이름을 기준으로 오름차순 정렬해주세요.

-- 모든 DB 공통
SELECT PT_NAME, PT_NO, GEND_CD, AGE, COALESCE(TLNO, 'NONE') as TLNO 
FROM PATIENT 
WHERE GEND_CD = 'W' AND AGE <= 12 
ORDER BY AGE DESC, PT_NAME;
  • COALESCE
    • COALESCE(값1, 값2, 값3, ...): 전부 NULL이면 NULL 반환, NULL 아닌 첫 값 반환
    • COALESCE(컬럼, 값): NULL이면 '값'으로 바꿈
    • COALESCE(컬럼1값, 컬럼2값, 기본값):
      • SELECT COALESCE(nickname, name, '익명') FROM user; 일 때
      • nickname → 없으면 name → 없으면 '익명'
-- Oracle
SELECT PT_NAME, PT_NO, GEND_CD, AGE, NVL(TLNO, 'NONE') as TLNO 
FROM PATIENT 
WHERE GEND_CD = 'W' AND AGE <= 12 
ORDER BY AGE DESC, PT_NAME;
  • NVL(col, 대체값)
  • NVL2(col, null아님값, null일때값) == CASE WHEN col IS NULL
-- MySQL
SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, 'NONE') as TLNO 
FROM PATIENT 
WHERE GEND_CD = 'W' AND AGE <= 12 
ORDER BY AGE DESC, PT_NAME;
  • IFNULL(col, 대체값)

이외의 NULL 관련 함수

-- Oracle, MySQL
SELECT NULLIF(a, b);
  • 두 값 같으면 NULL 반환
  • a == b → NULL
  • a != b → a
-- 모든 DB
SELECT 
CASE 
    WHEN price IS NULL THEN 0
    ELSE price
END
FROM book;
  • book의 price 컬럼이 NULL이면 0으로 아니면 price로

과일로 만든 아이스크림 고르기(JOIN)

상반기 아이스크림 총주문량이 3,000보다 높으면서 아이스크림의 주 성분이 과일인 아이스크림의 맛을 총주문량이 큰 순서대로 조회하는 SQL 문을 작성해주세요.

-- MySQL
SELECT F.FLAVOR
FROM FIRST_HALF F 
LEFT JOIN ICECREAM_INFO I 
ON F.FLAVOR = I.FLAVOR 
WHERE F.TOTAL_ORDER > 3000 
AND I.INGREDIENT_TYPE = 'fruit_based'
ORDER BY F.TOTAL_ORDER DESC;
-- MySQL, Oracle
SELECT F.FLAVOR
FROM FIRST_HALF F 
JOIN ICECREAM_INFO I 
ON F.FLAVOR = I.FLAVOR 
WHERE F.TOTAL_ORDER > 3000 
AND I.INGREDIENT_TYPE = 'fruit_based'
ORDER BY F.TOTAL_ORDER DESC;
-- 동작 결과는 동일하나 가독성이 나쁘고 OUTER JOIN이 불가능하다.
SELECT F.FLAVOR
FROM FIRST_HALF F, ICECREAM_INFO I
WHERE F.FLAVOR = I.FLAVOR
AND F.TOTAL_ORDER > 3000
AND I.INGREDIENT_TYPE = 'fruit_based'
ORDER BY F.TOTAL_ORDER DESC;

JOIN

SELECT 컬럼
FROM 테이블1
JOIN 테이블2
ON 조인조건
WHERE 조건
ORDER BY 정렬;
  • JOIN은 무조건 FROM 절에서 한다.
  • 실행순서: FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

흉부외과 또는 일반외과 의사 목록 출력하기(OR)

DOCTOR 테이블에서 진료과가 흉부외과(CS)이거나 일반외과(GS)인 의사의 이름, 의사ID, 진료과, 고용일자를 조회하는 SQL문을 작성해주세요. 이때 결과는 고용일자를 기준으로 내림차순 정렬하고, 고용일자가 같다면 이름을 기준으로 오름차순 정렬해주세요.

-- Oracle
SELECT DR_NAME, DR_ID, MCDP_CD, TO_CHAR(HIRE_YMD, 'YYYY-MM-DD')
FROM DOCTOR
WHERE MCDP_CD = 'CS' OR MCDP_CD = 'GS'
ORDER BY HIRE_YMD DESC, DR_NAME;
-- MySQL
SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') as HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD = 'CS' OR MCDP_CD = 'GS'
ORDER BY HIRE_YMD DESC, DR_NAME;
  • MySQL 날짜 포맷 구분하기
    • Y(4자리 전부) y(뒤 2자리)
    • M(영문명) m(숫자)
    • D(ex. 1th) d(01)

3월에 태어난 여성 회원 목록 출력하기(DATE FORMAT)

MEMBER_PROFILE 테이블에서 생일이 3월인 여성 회원의 ID, 이름, 성별, 생년월일을 조회하는 SQL문을 작성해주세요. 이때 전화번호가 NULL인 경우는 출력대상에서 제외시켜 주시고, 결과는 회원ID를 기준으로 오름차순 정렬해주세요.

-- Oracle
SELECT MEMBER_ID, MEMBER_NAME, GENDER, 
	TO_CHAR(DATE_OF_BIRTH, 'YYYY-MM-DD') as DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE TLNO IS NOT NULL 
AND EXTRACT(MONTH FROM DATE_OF_BIRTH) = 3 
AND GENDER = 'W'
ORDER BY MEMBER_ID;
-- MySQL
SELECT MEMBER_ID, MEMBER_NAME, GENDER, D
	ATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') as DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE TLNO IS NOT NULL 
AND MONTH(DATE_OF_BIRTH) = 3 
AND GENDER = 'W'
ORDER BY MEMBER_ID;

또는

-- MySQL
SELECT MEMBER_ID, MEMBER_NAME, GENDER,
       DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE TLNO IS NOT NULL
AND GENDER = 'W'
AND DATE_OF_BIRTH >= '1900-03-01'
AND DATE_OF_BIRTH <  '1900-04-01'
ORDER BY MEMBER_ID;

날짜 관련 Oracle, MySQL 차이

Oracle

  • 문자열 = DATE 가 아니다. 반드시 변환 필요.
  • date >= TO_DATE('20220301','YYYYMMDD')
  • 또는 date >= DATE '2022-03-01'
  • 이런 식으로 비교해야 한다.

MySQL

  • 그냥 문자열(date >= '2022-03-01')로 비교하거나
  • date >= DATE_FORMAT(date,'%Y-%m')
  • 이런 식으로 비교하면 된다.

강원도에 위치한 생산공장 목록 출력하기(LIKE)

FOOD_FACTORY 테이블에서 강원도에 위치한 식품공장의 공장 ID, 공장 이름, 주소를 조회하는 SQL문을 작성해주세요. 이때 결과는 공장 ID를 기준으로 오름차순 정렬해주세요.

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE '%강원도%'
ORDER BY FACTORY_ID;
  • '%강원%'는 앞 % 때문에 인덱스를 탈 수 없어서 느리고, '강원%'는 인덱스를 탈 수 있어서 빠름

LIKE 유사 함수

  • NOT LIKE: 아닌 것
  • IN (정확히 일치하는 값(여러 개))
  • REGEXP: 고급 패턴(정규식) 검색
  • REGEXP_LIKE: 오라클 전용 정규식 함수
  • =: 완전 일치 (성능이 제일 좋음)
  • POSITION / INSTR(문자 포함 여부): 
    • PostgrSQL: POSITION('강원' IN ADDRESS) > 0
    • MySQL/Oracle: INSTR(ADDRESS, '강원') > 0

서울에 위치한 식당 목록 출력하기

REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요.

SELECT R.REST_ID as REST_ID, I.REST_NAME, I.FOOD_TYPE, I.FAVORITES, I.ADDRESS, 
	ROUND(AVG(R.REVIEW_SCORE), 2) as REVIEW_SCORE
FROM REST_INFO I
INNER JOIN REST_REVIEW R
ON I.REST_ID = R.REST_ID
WHERE I.ADDRESS LIKE '서울%'
GROUP BY R.REST_ID, I.REST_NAME, I.FOOD_TYPE, I.FAVORITES, I.ADDRESS
ORDER BY AVG(R.REVIEW_SCORE) DESC, I.FAVORITES DESC;
  • 이 방식은 가독성이 떨어지고 오류가 많이 난다.
SELECT I.REST_ID, I.REST_NAME, I.FOOD_TYPE, I.FAVORITES, I.ADDRESS, R2.SCORE
FROM REST_INFO I
JOIN (
  SELECT REST_ID, ROUND(AVG(REVIEW_SCORE), 2) AS SCORE
  FROM REST_REVIEW
  GROUP BY REST_ID
) R2
ON I.REST_ID = R2.REST_ID
WHERE I.ADDRESS LIKE '서울%'
ORDER BY R2.SCORE DESC, I.FAVORITES DESC;
  • 복잡한 집계(GROUP BY)가 있는 부분을 서브쿼리로 때어내서 가독성이 더 좋다.
WITH A AS (
    SELECT REST_ID, REST_NAME, FOOD_TYPE, FAVORITES, ADDRESS
    FROM REST_INFO
    WHERE ADDRESS LIKE '서울%'
),
B AS (
    SELECT REST_ID, ROUND(AVG(REVIEW_SCORE), 2) AS REVIEW_SCORE
    FROM REST_REVIEW
    GROUP BY REST_ID
)

SELECT a.REST_ID, a.REST_NAME, a.FOOD_TYPE, a.FAVORITES, a.ADDRESS, b.REVIEW_SCORE
FROM A a
JOIN B b
ON a.REST_ID = b.REST_ID
ORDER BY b.REVIEW_SCORE DESC, a.FAVORITES DESC;
  • 오래 걸리지만 가독성이 좋고 재사용이 가능하다.
  • 아래 처럼 조건을 적어두면 결과를 확인하며 작성하기는 가장 좋다.
-- 서울만
-- i식당 ID, i식당 이름, i음식 종류, i즐겨찾기수, i주소, 리뷰 평균 점수
-- 리뷰-내림, 즐겨찾기-내림

GROUP BY

SELECT 컬럼, 집계함수
FROM 테이블
GROUP BY 컬럼;
  • 집계 함수: COUNT(), SUM(), AVG(), MAX(), MIN()
  • 여러 칼럼을 GROUP BY로 묶을 수 있다.
  • HAVING으로 조건을 줄 수 있다.
SELECT FLAVOR, SUM(TOTAL_ORDER)
FROM FIRST_HALF
GROUP BY FLAVOR
HAVING SUM(TOTAL_ORDER) >= 100;
  • HAVING절은 WHERE와 다르게 그룹 후 필터링 한다.

조건에 부합하는 중고거래 댓글 조회하기

USED_GOODS_BOARD와 USED_GOODS_REPLY 테이블에서 2022년 10월에 작성된 게시글 제목, 게시글 ID, 댓글 ID, 댓글 작성자 ID, 댓글 내용, 댓글 작성일을 조회하는 SQL문을 작성해주세요. 결과는 댓글 작성일을 기준으로 오름차순 정렬해주시고, 댓글 작성일이 같다면 게시글 제목을 기준으로 오름차순 정렬해주세요.

-- 2022년 10월 작성
-- BOARD_ID 
-- b.게시글 제목, b.게시글 ID, r.댓글 ID, r.댓글 작성자 ID, r.댓글 내용, r.댓글 작성일
-- 작성일, 게시글 정렬
-- MySQL
SELECT b.TITLE, b.BOARD_ID, r.REPLY_ID, r.WRITER_ID, r.CONTENTS,
       DATE_FORMAT(r.CREATED_DATE, '%Y-%m-%d') as CREATED_DATE
FROM USED_GOODS_REPLY r
JOIN (
    SELECT TITLE, BOARD_ID
    FROM USED_GOODS_BOARD
    WHERE CREATED_DATE >= '2022-10-01'
      AND CREATED_DATE < '2022-11-01'
) b
ON b.BOARD_ID = r.BOARD_ID
ORDER BY r.CREATED_DATE ASC, b.TITLE ASC;
-- Oracle
SELECT b.TITLE, b.BOARD_ID, r.REPLY_ID, r.WRITER_ID, r.CONTENTS,
       TO_CHAR(r.CREATED_DATE, 'YYYY-MM-DD') as CREATED_DATE
FROM USED_GOODS_REPLY r
JOIN (
    SELECT TITLE, BOARD_ID
    FROM USED_GOODS_BOARD
    WHERE CREATED_DATE >= DATE '2022-10-01'
      AND CREATED_DATE <  DATE '2022-11-01'
) b
ON b.BOARD_ID = r.BOARD_ID
ORDER BY r.CREATED_DATE ASC, b.TITLE ASC;
-- Oracle
SELECT B.TITLE, B.BOARD_ID, R.REPLY_ID, R.WRITER_ID, R.CONTENTS, 
    TO_CHAR(R.CREATED_DATE, 'YYYY-MM-DD') AS CREATED_DATE
FROM USED_GOODS_BOARD B
JOIN USED_GOODS_REPLY R
    ON B.BOARD_ID = R.BOARD_ID
WHERE B.CREATED_DATE 
    BETWEEN TO_DATE('20221001','YYYYMMDD') 
    AND TO_DATE('20221031','YYYYMMDD')
ORDER BY R.CREATED_DATE, B.TITLE;
  • (GROUP BY가 없거나) 복잡하지 않다면 서브쿼리를 만들지 않는 것이 좋은 거 같다.

BETWEEN

  • col BETWEEN A AND B: A 이상 AND B 이하
  • 날짜의 경우 시간에 따라 누락이 발생할 수 있으므로 사용하지 않는 게 좋다.
    • 그래서 '날짜값 >= 시작일 AND 날짜값 < 다음달1일'로 표현하는 것이 보편적이다.

재구매가 일어난 상품과 회원 리스트 구하기(HAVING)

ONLINE_SALE 테이블에서 동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성해주세요. 결과는 회원 ID를 기준으로 오름차순 정렬해주시고 회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬해주세요.

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) >= 2
ORDER BY USER_ID ASC, PRODUCT_ID DESC;
  • GROUP BY는 '중복 제거'가 아닌 '같은 값끼리 묶기'라는 것을 생각한다.

재구매한 회원 수만 구하라면?

SELECT COUNT(DISTINCT USER_ID)
FROM (
    SELECT USER_ID
    FROM ONLINE_SALE
    GROUP BY USER_ID, PRODUCT_ID
    HAVING COUNT(*) >= 2
);

모든 레코드 조회하기(*)

동물 보호소에 들어온 모든 동물의 정보를 ANIMAL_ID순으로 조회

SELECT *
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

역순 정렬하기(DESC, ASC)

동물 보호소에 들어온 모든 동물의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 ANIMAL_ID 역순으로 보여주세요. 

SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC

오프라인/온라인 판매 데이터 통합하기(UNION)

ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성해주세요. OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요. 결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬해주세요.

-- Oracle
SELECT
  TO_CHAR(SALES_DATE, 'YYYY-MM-DD') AS SALES_DATE,
  PRODUCT_ID,
  USER_ID,
  SALES_AMOUNT
FROM (
  SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
  FROM ONLINE_SALE
  WHERE SALES_DATE >= DATE '2022-03-01'
    AND SALES_DATE <  DATE '2022-04-01'
  UNION ALL
  SELECT SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
  FROM OFFLINE_SALE
  WHERE SALES_DATE >= DATE '2022-03-01'
    AND SALES_DATE <  DATE '2022-04-01'
)
ORDER BY
  SALES_DATE ASC,
  PRODUCT_ID ASC,
  USER_ID ASC;
-- Oracle
SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM (
  SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
  FROM ONLINE_SALE
  UNION ALL
  SELECT SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
  FROM OFFLINE_SALE
) S
WHERE S.SALES_DATE >= DATE '2022-03-01'
  AND S.SALES_DATE <  DATE '2022-04-01'
ORDER BY S.SALES_DATE, S.PRODUCT_ID, S.USER_ID;
  • 이것보다는 더 처음처럼 먼저 거르는게 효율적이다.
-- MySQL
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d'), PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM (
  SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
  FROM ONLINE_SALE
  UNION ALL
  SELECT SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
  FROM OFFLINE_SALE
) S
WHERE S.SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31'
ORDER BY S.SALES_DATE, S.PRODUCT_ID, S.USER_ID;

 

UNION 계열

  • 선택된 컬럼 개수가 동일해야함.
  • 타입을 일치 시켜야함.
  • ORDER BY는 중간에 들어가면 오류가 발생.

UNION ALL

  • 위아래 결과 그대로 합침
  • 중복 제거 안함
  • 빠름
SELECT ...
UNION ALL
SELECT ...

UNION 

  • 중복 자동 제거
  • 정렬 발생
  • 느림

→ 보통 거의 안 씀

SELECT ...
UNION
SELECT ...

INTERSECT(Oracle)

  • 둘 다 있는 것만.(교집합)
  • Oracle은 지원하지만, MySQL에서는 JOIN으로 대체.

INTERSECT

SELECT ID FROM STUDENT_A
INTERSECT
SELECT ID FROM STUDENT_B;

JOIN으로 표현

SELECT A.ID
FROM STUDENT_A A
JOIN STUDENT_B B
ON A.ID = B.ID;

MINUS(Oracle) / EXCEPT(MySQL 일부, PostgraSQL)

  • A에만 있고 B에는 없는 것. (차집합)
  • Oracle = MINUS, PostgreSQL = EXCEPT, MySQL은 EXCEPT가 일부 버전에만 있음(보통 JOIN으로 대체).

MINUS

SELECT ID FROM STUDENT_A
MINUS
SELECT ID FROM STUDENT_B;

EXCEPT

SELECT ID FROM STUDENT_A
EXCEPT
SELECT ID FROM STUDENT_B;

JOIN으로 표현

SELECT A.ID
FROM STUDENT_A A
LEFT JOIN STUDENT_B B
ON A.ID = B.ID
WHERE B.ID IS NULL;

아픈 동물 찾기(=, IN)

동물 보호소에 들어온 동물 중 아픈 동물1의 아이디와 이름을 조회하는 SQL 문을 작성해주세요. 이때 결과는 아이디 순으로 조회해주세요.

=

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION = 'Sick'
ORDER BY ANIMAL_ID

IN

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION IN ('Sick')
ORDER BY ANIMAL_ID

어린 동물 찾기 (<>, !=, NOT IN)

동물 보호소에 들어온 동물 중 젊은 동물1의 아이디와 이름을 조회하는 SQL 문을 작성해주세요. 이때 결과는 아이디 순으로 조회해주세요.

<> :  같지 않은 값(비트 연산 시 중요, not equal)), = : 정확히 일치하는 값

-- 표준
SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS 
WHERE INTAKE_CONDITION <> 'Aged' 
-- OR INTAKE_CONDITION IS NULL // NULL까지 출력
ORDER BY ANIMAL_ID
  • Oracle, MySQL, PostgreSQL 전부 지원.
  • SQL에서 NULL은 값이 없음 → 비교 자체가 불가능
  • WHERE 같은 비교에서는 TRUE만 통과
[4][2][1]
 1  1  1
 10진수로 7이다.
 VAL & 4 = 1이 참/거짓을 계산해보면
 7 & 4
= 111 & 100
= 100
= 4
FALSE이지만

VAL & 4 <> 0는 
JS식으로 보면 (val & 4) !== 0이라고 할 수 있다.
결국 TRUE가 된다. (!==과 <>는 완전 동일한 표현)

!= / NOT IN

-- 비표준
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'Aged'
-- 또는 INTAKE_CONDITION NOT IN ('Aged') // 두 가지 모두 값이 NULL이면 안나옴.
ORDER BY ANIMAL_ID

동물의 아이디와 이름

동물 보호소에 들어온 모든 동물의 아이디와 이름을 ANIMAL_ID순으로 조회하는 SQL문을 작성해주세요.

SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS 
ORDER BY ANIMAL_ID

여러 기준으로 정렬하기

동물 보호소에 들어온 모든 동물의 아이디와 이름, 보호 시작일을 이름 순으로 조회하는 SQL문을 작성해주세요. 단, 이름이 같은 동물 중에서는 보호를 나중에 시작한 동물을 먼저 보여줘야 합니다.

SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME, DATETIME DESC

상위 n개 레코드

동물 보호소에 가장 먼저 들어온 동물의 이름을 조회하는 SQL 문을 작성해주세요.

-- MySQL
SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1;
-- Oracle
SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
FETCH FIRST 1 ROW ONLY;

Top N 조회 / 행 제한

MySQL / PostgreSQL:

LIMIT 5; -- 상위 5개

건너뛰기 (Pagenation)

LIMIT 10 OFFSET 20;
  • 20개 먼저 건너뛰고 그 다음 10개 가져오기
  • 21번째 ~ 30번째 행
  • OFFSET = 시작 위치, LIMIT = 가져올 개수

Oracle:

FETCH FIRST 10 ROW ONLY;
  • 전체 쿼리 실행 후 상위 10개

건너뛰기 (Pagination)

OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
  • 20개 건너뛰고 다음 10개 가져와라

커서 기반 Pagination (OFFSET 쓰지 않는 법):

  • OFFSET은 모두 읽고 다음 LIMIT를 반환하기 때문에 페이지 뒤로 갈수록 성능이 급격히 나빠짐.
WHERE id > 마지막ID
ORDER BY id
LIMIT 10
  • 이런 식으로 WHERE 조건으로 검색하면
  • 앞부분 스킵 없음
  • 바로 이어서 읽음
  • 훨씬 빠름

조건에 맞는 회원수 구하기

USER_INFO 테이블에서 2021년에 가입한 회원 중 나이가 20세 이상 29세 이하인 회원이 몇 명인지 출력하는 SQL문을 작성해주세요.

-- Oracle
SELECT COUNT(USER_ID)
FROM USER_INFO
WHERE JOINED >= DATE '2021-01-01'
    AND JOINED < DATE '2022-01-01'
    AND AGE >= 20 AND AGE < 30
-- MySQL
SELECT COUNT(USER_ID)
FROM USER_INFO
WHERE JOINED >= '2021-01-01'
    AND JOINED < '2022-01-01'
    AND AGE >= 20 AND AGE < 30

업그레이드 된 아이템 구하기(중첩 JOIN, EXISTS, IN)

아이템의 희귀도가 'RARE'인 아이템들의 모든 다음 업그레이드 아이템의 아이템 ID(ITEM_ID), 아이템 명(ITEM_NAME), 아이템의 희귀도(RARITY)를 출력하는 SQL 문을 작성해 주세요. 이때 결과는 아이템 ID를 기준으로 내림차순 정렬주세요.

WITH A AS (
    SELECT ITEM_ID
    FROM ITEM_INFO
    WHERE RARITY = 'RARE'
)

SELECT I.ITEM_ID, I.ITEM_NAME, I.RARITY 
FROM ITEM_INFO I
JOIN ITEM_TREE T
  ON I.ITEM_ID = T.ITEM_ID
JOIN A
  ON T.PARENT_ITEM_ID = A.ITEM_ID
ORDER BY I.ITEM_ID DESC;
WITH A AS (
  SELECT ITEM_ID
  FROM ITEM_INFO
  WHERE RARITY = 'RARE'
)
SELECT I.ITEM_ID, I.ITEM_NAME, I.RARITY
FROM ITEM_INFO I
JOIN ITEM_TREE T
  ON I.ITEM_ID = T.ITEM_ID
WHERE T.PARENT_ITEM_ID IN (SELECT ITEM_ID FROM A)
ORDER BY I.ITEM_ID DESC;
SELECT
  c.ITEM_ID,
  c.ITEM_NAME,
  c.RARITY
FROM ITEM_TREE t
JOIN ITEM_INFO c
  ON c.ITEM_ID = t.ITEM_ID
WHERE t.PARENT_ITEM_ID IN (
  SELECT ITEM_ID
  FROM ITEM_INFO
  WHERE RARITY = 'RARE'
)
ORDER BY c.ITEM_ID DESC;
SELECT
  child.ITEM_ID,
  child.ITEM_NAME,
  child.RARITY
FROM ITEM_TREE t
JOIN ITEM_INFO child
  ON child.ITEM_ID = t.ITEM_ID
WHERE EXISTS (
  SELECT 1
  FROM ITEM_INFO parent
  WHERE parent.ITEM_ID = t.PARENT_ITEM_ID
    AND parent.RARITY = 'RARE'
)
ORDER BY child.ITEM_ID DESC;

EXISTS

WHERE EXISTS (
    서브쿼리
)
  • 서브쿼리 결과가 1행이라도 나오면 → TRUE, 0행이면 → FALSE
  • 보통 존재만을 확인하고 값을 보지 않으므로 'SELECT 1', 'SELECT *' 등 아무거나 상관 없음. 보통 1을 씀.
  • EXISTS는 값을 찾으면 바로 멈추기 때문에 DISTINCT도 필요 없기 때문에 JOIN 보다 유리한 면이 있음.

NOT EXISTS

  • 존재하지 않는 것 찾기
  • 리뷰 안쓴 회원 찾기
SELECT *
FROM USER u
WHERE NOT EXISTS (
    SELECT 1
    FROM REVIEW r
    WHERE r.user_id = u.user_id
);

IN과 EXISTS

EXISTS: 찾으면 바로 멈춤, 데이터 많으면 EXISTS 유리

WHERE EXISTS (
   SELECT 1
   FROM order o
   WHERE o.user_id = u.user_id
)

IN: 서브쿼리 결과 전부 확인

WHERE user_id IN (
   SELECT user_id FROM order
)

Python 개발자 찾기(IN, EXISTS)

DEVELOPER_INFOS 테이블에서 Python 스킬을 가진 개발자의 정보를 조회하려 합니다. Python 스킬을 가진 개발자의 ID, 이메일, 이름, 성을 조회하는 SQL 문을 작성해 주세요.
결과는 ID를 기준으로 오름차순 정렬해 주세요.

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPER_INFOS
WHERE 'Python' IN (SKILL_1, SKILL_2, SKILL_3)
ORDER BY ID
SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPER_INFOS d
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT SKILL_1 AS SKILL
        UNION ALL
        SELECT SKILL_2
        UNION ALL
        SELECT SKILL_3
    ) s
    WHERE s.SKILL = 'Python'
)
ORDER BY ID;

IN

  • 값이 목록 안에 있으면 TRUE
  • 단순 값 비교에 특화 되어 있지만, 대용량에서는 EXISTS를 쓰는 게 좋다.
col IN (값1, 값2, 값3)
-- 아래와 같다고 볼 수 있다.
col=값1 OR col=값2 OR col=값3

값 IN (컬럼들) 처럼도 가능하고

WHERE 'Python' IN (SKILL_1, SKILL_2, SKILL_3)

칼럼 IN (값들) 처럼도 가능하다.

WHERE SKILL_1 IN ('Python','Java')

조건에 맞는 개발자 찾기(& 비트 연산)

DEVELOPERS 테이블에서 Python이나 C# 스킬을 가진 개발자의 정보를 조회하려 합니다. 조건에 맞는 개발자의 ID, 이메일, 이름, 성을 조회하는 SQL 문을 작성해 주세요. (어떤 개발자의 SKILL_CODE가 400 (=b'110010000')이라면, 이는 SKILLCODES 테이블에서 CODE가 256 (=b'100000000'), 128 (=b'10000000'), 16 (=b'10000') 에 해당하는 스킬을 가졌다는 것을 의미합니다.)
결과는 ID를 기준으로 오름차순 정렬해 주세요.

SELECT DISTINCT d.ID, d.EMAIL, d.FIRST_NAME, d.LAST_NAME
FROM DEVELOPERS d
JOIN 
    (
        SELECT *
        FROM SKILLCODES
        WHERE UPPER(NAME) IN ('C#', 'PYTHON')
    ) s
ON d.SKILL_CODE & s.CODE <> 0
ORDER BY d.ID;
SELECT d.ID, d.EMAIL, d.FIRST_NAME, d.LAST_NAME
FROM DEVELOPERS d
WHERE EXISTS (
    SELECT 1
    FROM SKILLCODES s
    WHERE s.NAME IN ('Python', 'C#')
      AND (d.SKILL_CODE & s.CODE) <> 0
)
ORDER BY d.ID;
SELECT d.ID, d.EMAIL, d.FIRST_NAME, d.LAST_NAME
FROM DEVELOPERS d
WHERE (d.SKILL_CODE & (
    SELECT SUM(CODE)
    FROM SKILLCODES
    WHERE NAME IN ('Python', 'C#')
)) <> 0
ORDER BY d.ID;

위 문제에서 같은 결과를 내는 비트 연산

-- 포함하지 않음
(d.SKILL_CODE & s.CODE) <> 0
-- 0보다 큼
(d.SKILL_CODE & s.CODE) > 0
-- 0의 부정
NOT ((d.SKILL_CODE & s.CODE) = 0)
-- or로 포함 체크
(d.SKILL_CODE | s.CODE) = d.SKILL_CODE

주로 사용하는 비트 연산자

  • & (AND): 둘 다 1인 비트만 1
  • | (OR): 둘 중 하나라도 1이면 1
  • ^ (XOR): 서로 다르면 1 (DB마다 지원 다름)
  • ~ (NOT): 비트 반전 (DB마다/타입에 따라 주의)
  • <<, >> (shift): 비트를 좌/우로 이동 (DB마다 지원 다름)

잔챙이 잡은 수 구하기

잡은 물고기 중 길이가 10cm 이하인 물고기의 수를 출력하는 SQL 문을 작성해주세요.
물고기의 수를 나타내는 컬럼 명은 FISH_COUNT로 해주세요.
단, 잡은 물고기의 길이가 10cm 이하일 경우에는 LENGTH 가 NULL 이며, LENGTH 에 NULL 만 있는 경우는 없습니다.

SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO
WHERE LENGTH < 10 OR LENGTH IS NULL

가장 큰 물고기 10마리 구하기

FISH_INFO 테이블에서 가장 큰 물고기 10마리의 ID와 길이를 출력하는 SQL 문을 작성해주세요. 결과는 길이를 기준으로 내림차순 정렬하고, 길이가 같다면 물고기의 ID에 대해 오름차순 정렬해주세요. 단, 가장 큰 물고기 10마리 중 길이가 10cm 이하인 경우는 없습니다.
ID 컬럼명은 ID, 길이 컬럼명은 LENGTH로 해주세요.

SELECT ID, LENGTH
FROM FISH_INFO
ORDER BY LENGTH DESC, ID
LIMIT 10;

특정 물고기를 잡은 총 수 구하기

FISH_INFO 테이블에서 잡은 BASS와 SNAPPER의 수를 출력하는 SQL 문을 작성해주세요.
컬럼명은 'FISH_COUNT`로 해주세요.

SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO i
JOIN FISH_NAME_INFO n
ON i.FISH_TYPE = n.FISH_TYPE
WHERE UPPER(FISH_NAME) IN ('BASS', 'SNAPPER')
SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO i
WHERE EXISTS (
    SELECT 1
    FROM FISH_NAME_INFO n
    WHERE UPPER(n.FISH_NAME) IN ('BASS', 'SNAPPER')
      AND i.FISH_TYPE = n.FISH_TYPE
)

대장균들의 자식의 수 구하기(LEFT JOIN)

대장균 개체의 ID(ID)와 자식의 수(CHILD_COUNT)를 출력하는 SQL 문을 작성해주세요. 자식이 없다면 자식의 수는 0으로 출력해주세요. 이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요.

WITH CHILD AS (
SELECT PARENT_ID, COUNT(*) AS CHILD_COUNT
FROM ECOLI_DATA
GROUP BY PARENT_ID
)

SELECT D.ID, COALESCE(C.CHILD_COUNT, 0) AS CHILD_COUNT
FROM ECOLI_DATA D
LEFT JOIN CHILD C
ON C.PARENT_ID = D.ID
ORDER BY D.ID
SELECT D.ID, COUNT(C.ID) AS CHILD_COUNT
FROM ECOLI_DATA D
LEFT JOIN ECOLI_DATA C
ON C.PARENT_ID = D.ID
GROUP BY D.ID
ORDER BY D.ID;

JOIN 종류

A 한 행이 B 여러 행과 매칭되면 결과는 A가 여러 줄로 늘어난다.

  • 필요하면 DISTINCT, GROUP BY, 또는 집계로 해결
  • “존재 여부”만 필요하면 EXISTS가 더 깔끔함

INNER JOIN (A에만 있거나 B에만 있는 행은 버림, 교집합)

FROM A
INNER JOIN B
  ON A.key = B.key

LEFT (OUTER) JOIN (A를 전부 살리고, B는 있으면 붙이고 없으면 NULL)

FROM A
LEFT JOIN B
  ON A.key = B.key
  • A는 전부 유지
  • 매칭되는 B가 없으면 B쪽 컬럼이 NULL
  • “없으면 0”, “없으면 미존재” 같은 요구에 필수
-- 자주 쓰는 패턴 (= NOT EXISTS)
FROM A
LEFT JOIN B ON ...
WHERE B.key IS NULL
FROM A
LEFT JOIN B ON A.key = B.key
WHERE B.type = 'X'
--	AND B.type = 'X'
  • 이런 식으로 쓰면 B가 NULL인 행이 다 떨어져서 INNER JOIN처럼 변함.
  • LEFT JOIN 의미를 유지하려면 B 조건은 ON으로 옮겨야 함 (ON ... AND B.type = 'X')

RIGHT (OUTER) JOIN (B를 전부 살리고, A는 있으면 붙이고 없으면 NULL, 거의 안씀)

FROM A
RIGHT JOIN B
  ON A.key = B.key
  • 사실상 LEFT JOIN을 뒤집은 것
  • 실무에서는 가독성 때문에 테이블 순서를 바꿔서 LEFT JOIN으로 씀

FULL OUTER JOIN(A도 전부 + B도 전부, 매칭 없으면 반대편이 NULL, 양쪽 전체 보기)

FROM A
FULL OUTER JOIN B
  ON A.key = B.key
  • 둘 다 모두 살림
  • Oracle/PostgreSQL은 지원 (MySQL은 보통 UNION으로 흉내)

CROSS JOIN (모든 조합 다 붙이기, 조합 생성)

FROM A
CROSS JOIN B
  • 의도 없이 쓰면 멈출 수 있음 (200 * 100 = 20000)

대장균의 크기에 따라 분류하기 1 (CASE, IF)

대장균 개체의 크기가 100 이하라면 'LOW', 100 초과 1000 이하라면 'MEDIUM', 1000 초과라면 'HIGH' 라고 분류합니다. 대장균 개체의 ID(ID) 와 분류(SIZE)를 출력하는 SQL 문을 작성해주세요.이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요.

SELECT
  ID,
  CASE
    WHEN SIZE_OF_COLONY <= 100 THEN 'LOW'
    WHEN SIZE_OF_COLONY <= 1000 THEN 'MEDIUM'
    ELSE 'HIGH'
  END AS SIZE
FROM ECOLI_DATA A
ORDER BY ID
-- MySQL
SELECT
  ID,
  IF(SIZE_OF_COLONY <=100,'LOW', 
     IF(SIZE_OF_COLONY <= 1000,'MEDIUM','HIGH')) 
     AS SIZE
FROM ECOLI_DATA A
ORDER BY ID
  • MySQL에서 IF가 사용 가능하지만 거의 사용하지 않음.
  • 스크립트 언어 if문과 동일하게 먼저 조건이 맞으면 뒤에 값은 비교하지 않는다.

CASE

CASE
 WHEN 조건 THEN 값
 WHEN 조건 THEN 값
 ELSE 값
END
  • 보통 조건(조건 자리에 조건)형을 많이 쓰고, 값 비교형(조건 자리에 값, 값 자리에 결과)은 가능하지만 잘 쓰지 않는다.
  • SELECT에서는 '분류' 만들 때 만이 사용된다. (등급 분류 ,상태값 변환, 조건별 텍스트)
  • ORDER BY에서는 임의의 순서로 정렬할 때 사용할 수 있다.
ORDER BY CASE
 WHEN SIZE='LOW' THEN 1
 WHEN SIZE='MEDIUM' THEN 2
 ELSE 3
END
  • WHERE에서도 사용 가능하지만 보통 조건을 직접 쓴다.
  • GROUP BY에도 사용 가능하다.
SELECT
  CASE
    WHEN PRICE <=100 THEN 'LOW'
    ELSE 'HIGH'
  END AS PRICE_GROUP,
  COUNT(*)
FROM PRODUCT
GROUP BY
  CASE
    WHEN PRICE <=100 THEN 'LOW'
    ELSE 'HIGH'
  END;

특정 형질을 가지는 대장균 찾기(bitmask)

2번 형질이 보유하지 않으면서 1번이나 3번 형질을 보유하고 있는 대장균 개체의 수(COUNT)를 출력하는 SQL 문을 작성해주세요. 1번과 3번 형질을 모두 보유하고 있는 경우도 1번이나 3번 형질을 보유하고 있는 경우에 포함합니다.

SELECT COUNT(*) AS COUNT
FROM ECOLI_DATA
WHERE (GENOTYPE & 2) = 0
    AND (GENOTYPE & 1 <> 0 OR GENOTYPE & 4 <> 0);

비트마스크 공식

1. 특정 비트 있음 (1은 결과 값으로 사용하지 않음.)

(bitmask & 값) <> 0

2. 특정 비트 없음

(bitmask & 값) = 0

3. 특정 비트만 정확히

bitmask = 값

부모의 형질을 모두 가지는 대장균 찾기

부모의 형질을 모두 보유한 대장균의 ID(ID), 대장균의 형질(GENOTYPE), 부모 대장균의 형질(PARENT_GENOTYPE)을 출력하는 SQL 문을 작성해주세요. 이때 결과는 ID에 대해 오름차순 정렬해주세요.

SELECT C.ID, C.GENOTYPE, P.GENOTYPE AS PARENT_GENOTYPE
FROM ECOLI_DATA C
JOIN (
    SELECT ID, GENOTYPE
    FROM ECOLI_DATA
    ) P
ON C.PARENT_ID = P.ID
    AND C.GENOTYPE & P.GENOTYPE = P.GENOTYPE
ORDER BY C.ID
SELECT C.ID, C.GENOTYPE, P.GENOTYPE AS PARENT_GENOTYPE
FROM ECOLI_DATA C
JOIN ECOLI_DATA P
  ON P.ID = C.PARENT_ID
WHERE (C.GENOTYPE & P.GENOTYPE) = P.GENOTYPE
ORDER BY C.ID;

부모 비트가 전부 자식에 포함

-- A (보통은 이렇게 씀.)
(C.GENOTYPE & P.GENOTYPE) = P.GENOTYPE
-- B
(C.GENOTYPE | P.GENOTYPE) = C.GENOTYPE

대장균의 크기에 따라 분류하기 2(WINDOW - RANKING, OVER)

대장균 개체의 크기를 내름차순으로 정렬했을 때 상위 0% ~ 25% 를 'CRITICAL', 26% ~ 50% 를 'HIGH', 51% ~ 75% 를 'MEDIUM', 76% ~ 100% 를 'LOW' 라고 분류합니다. 대장균 개체의 ID(ID) 와 분류된 이름(COLONY_NAME)을 출력하는 SQL 문을 작성해주세요. 이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요 . 단, 총 데이터의 수는 4의 배수이며 같은 사이즈의 대장균 개체가 서로 다른 이름으로 분류되는 경우는 없습니다.

WITH A AS (
    SELECT ID, COUNT(*) OVER() AS CNT, 
    	ROW_NUMBER() OVER (ORDER BY SIZE_OF_COLONY DESC) AS NUM
    FROM ECOLI_DATA
)

SELECT ID, 
    CASE
        WHEN NUM * 100.0 / CNT <= 25 THEN 'CRITICAL'
        WHEN NUM * 100.0 / CNT <= 50 THEN 'HIGH'
        WHEN NUM * 100.0 / CNT <= 75 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS COLONY_NAME
FROM A
ORDER BY ID
  • NUM / CNT * 100을 하면 1 / 8 = 0이 될 수 있으므로 미리 100.0 실수형을 곱해서 오류를 막는다.
  • COUNT(*) OVER()에서 OVER()가 없으면 결과 값이 있는 행만 리턴된다.

WINDOW FUNCTION (RANKING 관련)

  • 행을 줄이지 않고, 그룹 기준으로 계산
  • 집계 함수(GROUP BY)와 달리 각 행을 유지하면서 계산 결과를 함께 보여주는 것
  • 랭킹, 누적합, 이전값/다음값, 그룹별 계산 등에 필수

기본 문법

함수() OVER (
    PARTITION BY 그룹컬럼
    ORDER BY 정렬컬럼
)

OVER()

  • 윈도우(계산 범위)를 지정한다.
  • 괄호에 아무 것도 없으면 전체 행이 하나의 윈도우가 된다.

이때 PARTITION BY로 범위를 지정하기도 한다.

COUNT(*) OVER (PARTITION BY 부서)
  • 부서별 행 개수

RANKING

ROW_NUMBER(): 중복이 있어도 순번이 증가

ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num

RANK(): 공동 순위 있음. 점프 발생(1등이 2명이면 다음은 3등)

RANK() OVER (ORDER BY amount DESC) AS rank_num

PARTITION BY (그룹별 랭킹): GROUP BY처럼 묶지만 행은 유지된다.

RANK() OVER (PARTITION BY 그룹 ORDER BY 값 DESC) AS 그룹_랭킹

특정 세대의 대장균 찾기(재귀 RECURSIVE)

3세대의 대장균의 ID(ID) 를 출력하는 SQL 문을 작성해주세요. 이때 결과는 대장균의 ID 에 대해 오름차순 정렬해주세요.

WITH RECURSIVE TREE AS (

    -- 시작점 (base)
    SELECT ID, PARENT_ID, 0 AS DEPTH
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL

    UNION ALL

    -- 반복 (recursive)
    SELECT C.ID, C.PARENT_ID, T.DEPTH + 1 AS DEPTH
    FROM ECOLI_DATA C
    JOIN TREE T
      ON T.ID = C.PARENT_ID
)
SELECT ID FROM TREE
WHERE DEPTH = 2;
  • 루트에서 내랴가면서 depth 계산

RECURSIVE

WITH RECURSIVE 이름 AS (
   -- 1단계 시작 (base case)
   처음 row

   UNION ALL

   -- 2단계 반복 (recursive case)
   이전 결과 → 다음 row 연결
)
SELECT * FROM 이름;
  • 재귀 SQL 기본
    1. 시작 row 저장
    2. 그 row로 부모 찾음
    3. 찾은 부모 저장
    4. 그 부모로 또 부모 찾음
    5. 반복
    6. 더 없으면 종료
WITH RECURSIVE tree AS (
   시작 row

   UNION ALL

   이전 결과 tree
   → 부모 테이블 join
)

예제:  ID = 4에서 루트까지

WITH RECURSIVE TREE AS (
    -- 시작점 (base)
    SELECT ID, PARENT_ID
    FROM ECOLI_DATA
    WHERE ID = 4

    UNION ALL

    -- 반복 (recursive)
    SELECT P.ID, P.PARENT_ID
    FROM ECOLI_DATA P
    JOIN TREE T
      ON T.PARENT_ID = P.ID
)
SELECT * FROM TREE;

예제:  루트에서 모든 자식

WITH RECURSIVE TREE AS (
    -- 시작 (루트)
    SELECT ID, PARENT_ID
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL

    UNION ALL

    -- 아래로 확장
    SELECT C.ID, C.PARENT_ID
    FROM ECOLI_DATA C
    JOIN TREE T
      ON C.PARENT_ID = T.ID
)
SELECT * FROM TREE;

멸종위기의 대장균 찾기

각 세대별 자식이 없는 개체의 수(COUNT)와 세대(GENERATION)를 출력하는 SQL문을 작성해주세요. 이때 결과는 세대에 대해 오름차순 정렬해주세요. 단, 모든 세대에는 자식이 없는 개체가 적어도 1개체는 존재합니다.

  • node가 아닌 leaf를 찾는 것이 포인트다.
  • TREE의 결과다. (node)
DEPTH	ID	PARENT_ID
    1	1	null
    1	2	null
    2	3	2
    2	4	2
    2	5	2
    3	6	4
    3	7	4
    4	8	6
  • 여기서 PARENT_ID와 위의 TREE ID를 비교하면 결과가 나온다. (leaf)

NOT EXISTS 방법

WITH RECURSIVE TREE AS (
    SELECT 1 AS DEPTH, ID, PARENT_ID
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL

    UNION ALL

    SELECT T.DEPTH + 1 AS DEPTH, C.ID, C.PARENT_ID
    FROM ECOLI_DATA C
    JOIN TREE T
      ON C.PARENT_ID = T.ID
)
SELECT COUNT(*) AS COUNT, DEPTH AS GENERATION
FROM TREE T
WHERE NOT EXISTS (
    SELECT 1
    FROM ECOLI_DATA CH
    WHERE CH.PARENT_ID = T.ID 
)
GROUP BY DEPTH
ORDER BY DEPTH;

LEFT (OUTER) JOIN 방법

WITH RECURSIVE TREE AS (
    SELECT 1 AS DEPTH, ID, PARENT_ID
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL

    UNION ALL

    SELECT T.DEPTH + 1 AS DEPTH, C.ID, C.PARENT_ID
    FROM ECOLI_DATA C
    JOIN TREE T
      ON C.PARENT_ID = T.ID
)
SELECT
    COUNT(*) AS COUNT,
    T.DEPTH AS GENERATION
FROM TREE T
LEFT JOIN ECOLI_DATA CH
  ON CH.PARENT_ID = T.ID
WHERE CH.ID IS NULL
GROUP BY T.DEPTH
ORDER BY T.DEPTH;

 

저작자표시 비영리 동일조건 (새창열림)

'Database > SQL' 카테고리의 다른 글

SUM, MAX, MIN  (0) 2026.02.07
IS NULL  (0) 2026.02.07
[MySQL] Linux에서 My SQL 설치하기 (docker & 일반 설치)  (1) 2025.02.19
[MySQL] MAC에서 MySQL 설치 및 초기화, 설정  (0) 2025.02.13
[Postgres] Docker 위 Postgresql 설치 및 권한, 데이터베이스 설정  (3) 2025.01.02

관련글

티스토리툴바