[SQL] 05. JOIN 6문제 정리

JOIN 문제 KEY POINT 🔑

☝️ JOIN 종류별 특징

1) join = inner join

select * from A join B on A.ID = B.ID

• 기준 테이블(A)과 조인 테이블(B)의 컬럼에 해당하는 값이 모두 존재하는 경우에만 데이터가 조회된다.

☝️ 따라서, A 테이블에는 있지만, B 테이블에 없는 경우 B 테이블에 존재하지 않기 때문에 조회되지 않는다.

• JOIN 결과

ID	이름	나이	학교	나라
1	홍길동	24	한국대학교	대한민국
2	백설공주	17	유럽대학교	유럽

• 이때, 테이블 B에 ID 3가 없기 때문에 JOIN 결과 신데렐라 관련된 정보는 조회할 수 없다.

2) left join = left outer join

select * from A left join B on A.ID = B.ID

• 'LEFT'로 기준 테이블을 지정한다. 이때 기준 테이블을 A로 정한다.

☝️ 아우터 조인의 경우, 조인 테이블(B)에 데이터가 없어도 기준 테이블의 모든 데이터가 조회되고, 조인 테이블(A)에 데이터가 존재할 경우 해당 데이터를 참조할 수 있다.

• JOIN 결과

ID	이름	나이	학교	나라
1	홍길동	24	한국대학교	대한민국
2	백설공주	17	유럽대학교	유럽
3	신데렐라	10	NULL	NULL

• 이때, 테이블 B에 ID 3가 없으므로 신데렐라의 학교, 나라는 NULL이다.

3) right join = right outer join

select * from A right join B on A.ID = B.ID

• RIGHT에 해당하는 테이블(B)이 기준 테이블이 된다. LEFT JOIN의 결과와 다르게 테이블(B)의 모든 데이터가 조회되고 조인 테이블(A)의 테이블에 데이터가 있을 경우 해당 값을 표시한다.

• JOIN 결과

ID	이름	나이	학교	나라
1	홍길동	24	한국대학교	대한민국
2	백설공주	17	유럽대학교	유럽
3	NULL	NULL	NULL	NULL

참고 자료 : https://gent.tistory.com/376

---

1. 보호소에서 중성화한 동물 🐾

난이도 : LEVEL 4

보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.

문제 풀이

1️⃣ 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물을 조회한다.

• where A.SEX_UPON_INTAKE like "Intact%" and (B.SEX_UPON_OUTCOME like "Spayed%" or B.SEX_UPON_OUTCOME like "Neutered%")

2️⃣ 아이디 순으로 조회한다.

• order by B.ANIMAL_ID

* WHERE에서 AND, OR 우선순위 : AND 우선순위 > OR 우선순위

• 따라서 or을 먼저 수행하고 싶으면 ( ) 사용!

SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME
from ANIMAL_INS as A
join ANIMAL_OUTS as B on A.ANIMAL_ID = B.ANIMAL_ID
where A.SEX_UPON_INTAKE like "Intact%" and 
(B.SEX_UPON_OUTCOME like "Spayed%" or B.SEX_UPON_OUTCOME like "Neutered%")
order by B.ANIMAL_ID

참고 자료 : https://goguri.tistory.com/460

---

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

난이도 : LEVEL 3

아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.

문제 풀이

1️⃣ 아직 입양을 못 간 동물을 조회한다.

• 아직 입양을 못 간 동물은 ANIMAL_INS 테이블에는 데이터가 있지만, ANIMAL_OUTS 테이블에 데이터가 NULL이다.
• where B.DATETIME IS NULL

2️⃣ 가장 오래 보호소에 있었던 동물 3마리을 조회한다.

• 보호 시작일이 오래된 순서대로 정렬한 뒤에 3마리만 선택한다.
• order by A.DATETIME limit 3

SELECT A.NAME, A.DATETIME
from ANIMAL_INS as A
left join ANIMAL_OUTS as B on A.ANIMAL_ID = B.ANIMAL_ID
where B.DATETIME IS NULL
order by A.DATETIME
limit 3

---

3. 있었는데요 없었습니다 🌫️

난이도 : LEVEL 3

관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.

문제 풀이

1️⃣ 보호 시작일보다 입양일이 더 빠른 동물을 조회한다.

• where ANIMAL_INS.DATETIME > ANIMAL_OUTS.DATETIME

2️⃣ 보호 시작일이 빠른 순으로 조회한다.

• order by ANIMAL_INS.DATETIME

SELECT A.ANIMAL_ID, A.NAME
from ANIMAL_INS as A
join ANIMAL_OUTS as B on A.ANIMAL_ID = B.ANIMAL_ID
where A.DATETIME > B.DATETIME
order by A.DATETIME

---

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

난이도 : LEVEL 2

PRODUCT 테이블과 OFFLINE_SALE 테이블에서 상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요. 결과는 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.

문제 풀이

1️⃣ 상품코드 별 매출액(판매가 * 판매량) 합계를 출력한다.

• 이때, 상품코드 별로 묶어서 합계를 구해야 하므로 group by와 SUM( )을 한다.
  ➡️ group by에 대한 자세한 내용은 다음 게시글 참고 🔥
• SUM(PRODUCT.PRICE*OFFLINE_SALE.SALES_AMOUNT)
• group by PRODUCT.PRODUCT_CODE

2️⃣ 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬한다.

• 정렬 순위 : 1순위 = 매출액, 2순위 = 상품 코드
• order by SALES desc, PRODUCT.PRODUCT_CODE

SELECT A.PRODUCT_CODE, SUM(A.PRICE*B.SALES_AMOUNT) as SALES
from PRODUCT as A
join OFFLINE_SALE as B on A.PRODUCT_ID = B.PRODUCT_ID
group by A.PRODUCT_CODE
order by SALES desc, A.PRODUCT_CODE

---

5. 없어진 기록 찾기 🗑️

난이도 : LEVEL 3

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

문제 풀이

1️⃣ 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물을 조회한다.

• 보호소에 들어온 기록 테이블 (ANIMAL_INS)을 A라고 하고, 입양을 간 기록 테이블 (ANIMAL_OUTS)을 B라고 하자!
• 그러면, 입양을 간 기록은 있는데 보호소에 들어온 기록이 없는 경우 B에는 있지만 A에는 없으므로 위의 그림처럼 색칠된 부분을 가리킨다.
  ➡️ 따라서, right join을 사용하여 B를 기준 테이블로 선정하면, B에는 있지만 A에는 없는 경우 A 관련 열은 NULL이 된다.
• from ANIMAL_INS as A right join ANIMAL_OUTS as B on A.ANIMAL_ID = B.ANIMAL_ID where A.ANIMAL_ID IS NULL

2️⃣ ID 순으로 조회한다.

• order by B.ANIMAL_ID

* right join : B 테이블을 기준으로 join 한다.

SELECT B.ANIMAL_ID, B.NAME
from ANIMAL_INS as A
right join ANIMAL_OUTS as B on A.ANIMAL_ID = B.ANIMAL_ID
where A.ANIMAL_ID IS NULL
order by B.ANIMAL_ID

---

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

난이도 : LEVEL 2

'경제' 카테고리에 속하는 도서들의 도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 출판일을 기준으로 오름차순 정렬해주세요.

문제 풀이

1️⃣ '경제' 카테고리에 속하는 도서들을 출력한다.

• where BOOK.CATEGORY like "경제"

2️⃣ 출판일을 기준으로 오름차순 정렬한다.

• order by PUBLISHED_DATE

SELECT A.BOOK_ID, B.AUTHOR_NAME, 
DATE_FORMAT(A.PUBLISHED_DATE,"%Y-%m-%d") as PUBLISHED_DATE
from BOOK as A
join AUTHOR as B on A.AUTHOR_ID = B.AUTHOR_ID
where A.CATEGORY like "경제"
order by PUBLISHED_DATE