[Programmers] SQL > JOIN 풀이 모음

상품 별 오프라인 매출 구하기(Lv.2)
- Answer Code1(23.12.27)
- 문제 풀이
조건에 맞는 도서와 저자 리스트 출력하기(Lv.2)
오랜 기간 보호한 동물(1) (Lv.3)
- Answer Code (25.09.04)
- 문제 풀이
없어진 기록 찾기 (LV.3)
- Answer Code 1(24.01.03)
- Answer Code 1 - 문제 풀이
ID 순으로 조회(ORDER)
- Answer Code 2(25.09.04)
- Answer Code 2 - 문제 풀이
있었는데요 없었습니다. (LV.3)
주문량이 많은 아이스크림들 조회하기
- Answer Code(24.01.04)
- 문제 풀이
Reference

상품 별 오프라인 매출 구하기(Lv.2)

상품 별 오프라인 매출 구하기

문제: PRODUCT 테이블과 OFFLINE_SALE 테이블에서

상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요.

결과는 매출액을 기준으로 내림차순 정렬해주시고

매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.

Answer Code1(23.12.27)

SELECT A.PRODUCT_CODE,
       (A.PRICE * B.TOT_SALES_AMOUNT) AS SALES
FROM PRODUCT A
       LEFT JOIN (
  SELECT PRODUCT_ID
       , SUM(SALES_AMOUNT) TOT_SALES_AMOUNT
  FROM OFFLINE_SALE B
  GROUP BY
    PRODUCT_ID
) B
                 ON  A.PRODUCT_ID = B.PRODUCT_ID
ORDER BY SALES DESC, PRODUCT_CODE ASC

문제 풀이

문제를 풀기 위해서 해야할 작업들

OFFLINE_SALE 테이블에서 PRODUCT_ID별 판매 수량 집계하기(GROUP BY)
PRODUCT 테이블과 조인하기(JOIN)
이때, 매출액은 상품코드 별 매출액(판매가 * 판매량) 합계를 계산(SUM)
매출액을 기준으로 내림차순 정렬, 만약 매출액이 같다면 상품코드를 기준으로 오름차순 정렬(ORDER BY)

첫 번째 단계는 OFFLINE_SALE 테이블에서 PRODUCT_ID 별 판매 수량을 집계한다.
이때 GROUP BY를 집계해서 각 상품별 수량을 산출한다.

SELECT PRODUCT_ID
        , SUM(SALES_AMOUNT) TOT_SALES_AMOUNT
FROM OFFLINE_SALE B
GROUP BY 
    PRODUCT_ID

두 번째 단계는 앞에서 진행한 결과를 서브쿼리로 넣고, PRODUCT 테이블을 기준으로 LEFT JOIN을 해준다.
이 결과를 통해 각 상품별 총 판매개수와 상품별 금액을 알 수 있다.

SELECT *
FROM PRODUCT A
LEFT JOIN (
            SELECT PRODUCT_ID
                 , SUM(SALES_AMOUNT) TOT_SALES_AMOUNT
            FROM OFFLINE_SALE B
            GROUP BY
              PRODUCT_ID
) B
  ON  A.PRODUCT_ID = B.PRODUCT_ID

세 번째 단계는 상품코드와 상품코드별 매출액(판매가 * 판매량) 합계를 산출한다.

SELECT A.PRODUCT_CODE,
       (A.PRICE * B.TOT_SALES_AMOUNT) AS SALES
FROM PRODUCT A
LEFT JOIN (
            SELECT PRODUCT_ID
                 , SUM(SALES_AMOUNT) TOT_SALES_AMOUNT
            FROM OFFLINE_SALE B
            GROUP BY
              PRODUCT_ID
) B
  ON  A.PRODUCT_ID = B.PRODUCT_ID

마지막 단계에서는 매출액을 기준으로 내림차순 정렬, 만약 매출액이 같다면 상품코드를 기준으로 오름차순 정렬(ORDER BY)을 해준다.

SELECT A.PRODUCT_CODE,
       (A.PRICE * B.TOT_SALES_AMOUNT) AS SALES
FROM PRODUCT A
LEFT JOIN (
            SELECT PRODUCT_ID
                 , SUM(SALES_AMOUNT) TOT_SALES_AMOUNT
            FROM OFFLINE_SALE B
            GROUP BY
              PRODUCT_ID
) B
  ON  A.PRODUCT_ID = B.PRODUCT_ID
ORDER BY SALES DESC, PRODUCT_CODE ASC

조건에 맞는 도서와 저자 리스트 출력하기(Lv.2)

조건에 맞는 도서와 저자 리스트 출력하기

문제: '경제' 카테고리에 속하는 도서들의

도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE) 리스트를 출력하는 SQL문을 작성해주세요.

결과는 출판일을 기준으로 오름차순 정렬해주세요.

Answer Code(23.12.27)

SELECT A.BOOK_ID, 
       B.AUTHOR_NAME, 
       DATE_FORMAT(A.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK A
LEFT JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE A.CATEGORY = '경제'
ORDER BY A.PUBLISHED_DATE ASC

문제 풀이

문제를 풀기 위해서 해야할 작업들

‘경제’ 카테고리에 속하는 도서들을 -> WHERE
AUTHOR 테이블과 조인한 뒤 , BOOK_ID, AUTHOR_NAME, PUBLISHED_DATE 조회 -> SELECT
PUBLISHED_DATE 기준으로 오름차순 -> ORDER BY
PUBLISHED_DATE를 예시와 같이 DATE_FORMAT을 사용하여 조회한다.

첫 번째 단계에서는 BOOK 테이블에서 ‘경제’ 카테고리에 속하는 도서들을 조회한다.

SELECT *
FROM BOOK
WHERE CATEGORY = '경제'

두 번째 단계에서는 AUTHOR_NAME을 조회하기 위해 AUTHOR_ID를 기준으로 AUTHOR 테이블과 조인한다.
그런 다음, BOOK_ID, AUTHOR_NAME, PUBLISHED_DATE 조회한다.

SELECT A.BOOK_ID, B.AUTHOR_NAME, A.PUBLISHED_DATE
FROM BOOK A
LEFT JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE A.CATEGORY = '경제'

세 번째 단계에서는 PUBLISHED_DATE 기준으로 오름차순으로 정렬해준다.

SELECT A.BOOK_ID, B.AUTHOR_NAME, A.PUBLISHED_DATE
FROM BOOK A
LEFT JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE A.CATEGORY = '경제'
ORDER BY A.PUBLISHED_DATE ASC

마지막 단계에서는 PUBLISHED_DATE를 예시와 같이 DATE_FORMAT을 사용하여 조회한다.
DATE_FORMAT을() 함수를 이용해 '%Y-%m-%d' 형태로 변경해주면 된다.

SELECT A.BOOK_ID, 
       B.AUTHOR_NAME, 
       DATE_FORMAT(A.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK A
LEFT JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE A.CATEGORY = '경제'
ORDER BY A.PUBLISHED_DATE ASC

Answer Code2(23.12.27)

SELECT  A.BOOK_ID
        , B.AUTHOR_NAME
        , DATE_FORMAT(A.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM  (
      SELECT  *
        FROM  BOOK AS A
       WHERE  A.CATEGORY = '경제'
      ) A
LEFT 
JOIN  AUTHOR AS B
  ON  A.AUTHOR_ID = B.AUTHOR_ID
ORDER
  BY  PUBLISHED_DATE ASC

문제 풀이

두 번재 풀이의 경우, BOOK 테이블과 AUTHOR 테이블을 결합하기 전에 BOOK 테이블에서 CATEGORY 컬럼에서 ‘경제’ 값만 먼저 추출해주는 것이다.
이후 AUTHOR 테이블과 조인을 통해 결합을 했다.

Review

1번째 풀이 방식이 2번째보다 더 효율적이라고 생각한다.
데이터의 양이 많을 경우 1번째 처럼 LEFT JOIN을 먼저 실행하고 WHERE 절을 적용하면, JOIN하는 과정에서 데이터가 더 많아져서 비효율적이라고 본다.
반면에, 2번째 풀이 방식처럼 서브 쿼리가 들어가더라도 필요한 컬럼과 필요한 조건에 해당하는 데이터만 추출하여, 데이터의 크기(size)가 축소한 이후에 JOIN이 이뤄진다면
1번째 보다 더 효율적이라고 생각한다.

오랜 기간 보호한 동물(1) (Lv.3)

오랜 기간 보호한 동물(1)

문제: 아직 입양을 못 간 동물 중,

가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요.

이때 결과는 보호 시작일 순으로 조회해야 합니다.

Answer Code (25.09.04)

SELECT INS.NAME, INS.DATETIME
FROM ANIMAL_INS AS INS
       LEFT JOIN ANIMAL_OUTS AS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE OUTS.DATETIME IS NULL
ORDER BY INS.DATETIME
  LIMIT 3

문제 풀이

ANIMAL_INS - 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부

ANIMAL_OUTS - 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부

문제를 풀기 위해서 해야할 작업들

ANIMAL_ID를 기준으로 ANIMAL_INS 테이블과 ANIMAL_OUTS 테이블을 JOIN
JOIN: ANIMAL_INS 테이블(INS)을 기준으로, ANIMAL_OUTS 테이블(OUTS)과 ANIMAL_ID로 LEFT JOIN 한다.
입양 못 간 동물 조회 (WHERE): LEFT JOIN을 하면 입양 기록이 없는 동물의 OUTS 테이블 정보는 NULL 이 된다. 이 원리를 이용해 WHERE OUTS.DATETIME IS NULL 조건으로 입양 가지 못한 동물만 골라낸다.
정렬 (ORDER BY): 가장 오래 보호소에 있었던 동물’은 ‘보호 시작일이 가장 빠른 동물’ 과 같다. 따라서 보호 시작일 순서대로 정렬한다. (ORDER BY INS.DATETIME)
- 참고로, ORDER BY의 기본 정렬은 오름차순(ASC) 이다.
결과 수 제한 (LIMIT): 정렬된 결과 중 상위 3마리만 선택한다. (LIMIT 3)
최종 조회 (SELECT): 해당 동물의 이름(INS.NAME)과 보호 시작일(INS.DATETIME)을 조회한다.

LIMIT 3가 마지막에 오는 이유

LIMIT가 마지막에 오는 이유는 SQL 쿼리의 실행 순서 때문이다.
보통은 쿼리의 실행 순서는 아래와 같이 진행된다.
- (1) FROM / JOIN: NIMAL_INS와 ANIMAL_OUTS 테이블을 합친다.
- (2) WHERE: 친 데이터에서 입양 간 동물들을 제외한다. (OUTS.DATETIME IS NULL).
- (3) ORDER BY: 은 동물들을 보호 시작일(INS.DATETIME) 순서대로 정렬한다.
- (4) SELECT: 정렬된 결과에서 이름과 보호 시작일을 선택한다.
- (5) LIMIT: 최종적으로 정렬된 결과의 맨 위에서 3개만 잘라낸다.
따라서 원하는 순서대로 모든 데이터를 정렬한 뒤(ORDER BY), 그중에서 원하는 개수만큼만 가져오기 위해 LIMIT는 항상 ORDER BY 뒤에 위치해야 한다.

없어진 기록 찾기 (LV.3)

없어진 기록 찾기

문제:
- 천재지변으로 인해 일부 데이터가 유실되었습니다.
- 입양을 간 기록은 있는데,
- 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.

Answer Code 1(24.01.03)

SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_INS AS I
RIGHT JOIN ANIMAL_OUTS AS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL
ORDER BY O.ANIMAL_ID;

Answer Code 1 - 문제 풀이

ANIMAL_INS

동물 보호소에 들어온 동물의 정보.
동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부

ANIMAL_OUTS

동물 보호소에서 입양 보낸 동물의 정보
각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부

문제를 풀기 위해서 해야할 작업들

ANIMAL_OUTS 테이블 기준으로 ANIMAL_INS 테이블과 ANIMAL_ID으로 RIGHT JOIN
입양일은 존재하나, 보호 시작일이 존재하지 않은 동물 -> ANIMAL_INS 테이블에서 ANIMAL_ID가 없는 경우
ID와 이름을 조회(SELECT)
ID 순으로 조회(ORDER)

위 그림처럼 INNER JOIN은 두 개의 테이블에 모두 속하는 교집합에 대한 결과를 찾아내는 것이고,

OUTER JOIN은 교집합에 대해 포함된 결과와 LEFT, RIGHT 중 하나의 테이블은 무조건 결과에 포함시키는 쿼리이다.
예를 들어, TABLE_A LEFT JOIN TABLE_B -> TABLE_A의 결과는 모두 노출하고 A와 교집합이 있는 B만 추가로 추출한다는 뜻이다.
이 문제에서는 ANIMAL_OUTS 테이블을 기준으로 ID와 이름을 조회할 때 RIGHT JOIN을 사용하면,
오른쪽에는 ANIMAL_OUTS 테이블을 작성하고, 왼쪽인 FROM 절 뒤에는 ANIMAL_OUTS 테이블이 아닌 ANIMAL_INS 테이블을 작성한다.

Answer Code 2(25.09.04)

-- ANIMAL_INS: 동물 보호소에 들어온 동물의 정보
-- ANIMAL_OUTS: 동물 보호소에서 입양 보낸 동물의 정보
-- 보호소에 들어온 기록이 없는 동물의 ID, 이름을 ID순으로 조회
-- Allie -> 입양 보낸 기록은 있는데, 들어온 기록은 없음
-- Spice -> 역시 마찬가지

SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_OUTS AS O
       LEFT JOIN ANIMAL_INS AS I ON O.ANIMAL_ID = I.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL
ORDER BY O.ANIMAL_ID;

Answer Code 2 - 문제 풀이

ANIMAL_OUTS 테이블을 기준으로 ANIMAL_INS 테이블과 ANIMAL_ID로 LEFT JOIN을 수행한다.
입양 기록(ANIMAL_OUTS)은 있지만 보호소에 들어온 기록(ANIMAL_INS)이 없는 동물을 찾는다. (WHERE I.ANIMAL_ID IS NULL 조건)
찾아낸 동물의 ID와 이름을 조회(SELECT)한다.
결과를 ID 순으로 정렬(ORDER BY)한다.

있었는데요 없었습니다. (LV.3)

있었는데요 없었습니다

문제: 관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다.

보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요.

이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.

Answer Code 1(24.01.04)

SELECT ANIMAL_ID, NAME
FROM ANIMAL_OUTS AS A
LEFT JOIN ANIMAL_INS AS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.DATETIME < A.DATETIME
ORDER BY B.DATETIME

Answer Code 1 - 문제 풀이

ANIMAL_INS - 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부

ANIMAL_OUTS - 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부

문제를 풀기 위해서 해야할 작업들

입양일 필드를 가지고 있는 ANIMAL_OUTS 테이블을 기준으로 ANIMAL_INS 테이블과 JOIN
보호 시작일보다 입양일이 더 빠른 동물 -> ANIMAL_INS.DATETIME(보호 시작일) > ANIMAL_OUTS.DATETIME(입양일)
- 더 빠른 요일은 더 작다는 것을 의미하기 때문에, 보호 시작일이 입양일보다 커야 한다.
동물의 아이디와 이름을 조회 -> SELECT
보호 시작일을 기준으로 내림차순 정렬하여 결과를 출력한다.

Answer Code 2(25.09.04)

-- INS: 동물 보호소에 들어온 동물의 정보
-- OUTS: 테이블은 동물 보호소에서 입양 보낸 동물의 정보

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS AS OUTS
       LEFT JOIN ANIMAL_INS AS INS ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE OUTS.DATETIME < INS.DATETIME
ORDER BY INS.DATETIME;

Answer Code 2 - 문제 풀이

보호 시작일(INS)보다 입양일(OUTS)이 더 빠른 동물의 아이디와 이름을 조회하는 문제.
- 더 빠르다는 것은 더 작다는것이므로 => OUTS.DATETIME (입양일) < INS.DATETIME(보호 시작일)
- 결과는 보호 시작일(INS)이 빠른 순 -> 오름차순
날짜와 시간 데이터에서 ‘빠르다’는 것은 ‘더 이전’을 의미하고, 이는 곧 ‘더 작은 숫자’ 에 해당한다.
- 빠른 날짜 (과거): 2023년 1월 1일
- 늦은 날짜 (미래): 2025년 9월 4일
데이터로 변환하면 과거의 날짜가 더 작은 값을 가집니다. 즉, 2023-01-01 < 2025-09-04 이다.

주문량이 많은 아이스크림들 조회하기

주문량이 많은 아이스크림들 조회하기

문제: 7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로

상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.

Answer Code(24.01.04)

SELECT A.FLAVOR
FROM FIRST_HALF AS A
       JOIN JULY AS B 
           ON A.FLAVOR = B.FLAVOR
GROUP BY A.FLAVOR
ORDER BY SUM(A.TOTAL_ORDER) + SUM(B.TOTAL_ORDER) DESC LIMIT 3

문제 풀이

FIRST_HALF : 아이스크림 가게의 상반기 주문 정보 - SHIPMENT_ID(JULY테이블의 SHIPMENT_ID의 외래 키), FLAVOR(기본 키), TOTAL_ORDER

JULY : 7월의 아이스크림 주문 정보 - SHIPMENT_ID(기본 키), FLAVOR(FIRST_HALF 테이블의 FLAVOR의 외래 키), TOTAL_ORDER

문제를 풀기 위해서 해야할 작업들

FLAVOR 이 조회되는 것이므로 해당 기본 키를 갖고 있는 테이블인 FIRST_HALF 테이블을 기준으로 JULY 테이블과 JOIN
7월 아이스크림 총 주문량과 상반기 아이스크림 총 주문량을 더한 값을 구한다.
상위 3개 맛을 조회하기 위해 LIMIT 절을 사용하고, FLAVOR을 기준으로 내림차순 정렬하여 결과를 출력한다.

[1] FLAVOR 이 조회되는 것이므로 해당 기본 키를 갖고 있는 테이블인 FIRST_HALF 테이블을 기준으로 JULY 테이블과 JOIN

FLAVOR를 기준으로 JULY 테이블과 JOIN한다.

SELECT A.FLAVOR
FROM FIRST_HALF AS A 
    JOIN JULY AS B 
        ON A.FLAVOR = B.FLAVOR

[2] 7월 아이스크림 총 주문량과 상반기 아이스크림 총 주문량을 더한 값을 구한다.

상반기 아이스크림과 7월 아이스크림 총 주문량을 각각 계산해서 더해준다.

ORDER BY SUM(A.TOTAL_ORDER) + SUM(B.TOTAL_ORDER)

[3] 상위 3개 맛을 조회하기 위해 LIMIT 절을 사용하고, FLAVOR을 기준으로 내림차순 정렬하여 결과를 출력한다.

2번에서 풀이한 것을 바탕으로 LIMIT 절과 FLAVOR을 기준으로 내림차순 해준다.
이때, FIRST_HALF 테이블의 기본키인 FLAVOR을 기준으로 그룹핍을 해준다.

SELECT A.FLAVOR
FROM FIRST_HALF AS A
       JOIN JULY AS B 
           ON A.FLAVOR = B.FLAVOR
GROUP BY A.FLAVOR
ORDER BY SUM(A.TOTAL_ORDER) + SUM(B.TOTAL_ORDER) DESC LIMIT 3

Reference

https://kkw-da.tistory.com/
https://habiis.tistory.com/118 (JOIN 종류)

[Programmers] SQL > JOIN 풀이 모음

상품 별 오프라인 매출 구하기(Lv.2)

Answer Code1(23.12.27)

문제 풀이

조건에 맞는 도서와 저자 리스트 출력하기(Lv.2)

Answer Code(23.12.27)

문제 풀이

Answer Code2(23.12.27)

문제 풀이

Review

오랜 기간 보호한 동물(1) (Lv.3)

Answer Code (25.09.04)

문제 풀이

없어진 기록 찾기 (LV.3)

Answer Code 1(24.01.03)

Answer Code 1 - 문제 풀이

ID 순으로 조회(ORDER)

Answer Code 2(25.09.04)

Answer Code 2 - 문제 풀이

있었는데요 없었습니다. (LV.3)

Answer Code 1(24.01.04)

Answer Code 1 - 문제 풀이

Answer Code 2(25.09.04)

Answer Code 2 - 문제 풀이

주문량이 많은 아이스크림들 조회하기

Answer Code(24.01.04)

문제 풀이

Reference