[프로그래머스] SQL JOIN문

원본 링크

https://programmers.co.kr/learn/courses/30/parts/17046

Intro

드디어 SQL의 꽃(?)이라 불리는 JOIN연산이 등장했다.
데이터베이스는 데이터를 효율적으로 다루고 중복을 제거하기 위해 정규화를 수행하곤 한다.
이 과정에서 테이블이 분리되는 현상이 자주 발생한다.
따라서 원하는 query를 요청하기 위해서 여러 테이블을 결합시키는 JOIN을 사용하게 된다.

문제는, JOIN 연산이 은근히 헷갈린다는 것이다.
완전히 익숙해지면 모를까, 처음 접하는 사람에겐 다소 혼란스러울 수 있다.
물론 여기선 엄청 상세하게까지 설명하지는 않으나, 필요에 따라선 부분부분 관련 메소드와 과정을 설명할 생각이다.

공통 조건

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

NAME	TYPE	NULLABLE
ANIMAL_ID	VARCHAR(N)	FALSE
ANIMAL_TYPE	VARCHAR(N)	FALSE
DATETIME	DATETIME	FALSE
INTAKE_CONDITION	VARCHAR(N)	FALSE
NAME	VARCHAR(N)	TRUE
SEX_UPON_INTAKE	VARCHAR(N)	FALSE

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다. ANIMAL_OUTS 테이블의 ANIMAL_ID는 ANIMAL_INS의 ANIMAL_ID의 외래 키입니다.

NAME	TYPE	NULLABLE
ANIMAL_ID	VARCHAR(N)	FALSE
ANIMAL_TYPE	VARCHAR(N)	FALSE
DATETIME	DATETIME	FALSE
NAME	VARCHAR(N)	TRUE
SEX_UPON_OUTCOME	VARCHAR(N)	FALSE

잠깐만!

일단 본격적인 문제에 앞서서 위 문제에서 나온 용어 하나만 짚고 넘어가자.
외래 키(Foreign Key; FK)라는 용어가 있는데, 향후 문제를 풀면서 이 속성을 토대로 JOIN연산을 수행할 것이다.
Key에는 크게 주 키(Primary Key;PK) 그리고 외래 키가 있다.
(슈퍼 키, 보조 키, 후보 키 등은 여기선 생략한다. DB강의가 아니니까,)
쉽게 말하면, PK는 가장 메인이 되는 속성을 의미한다.
그리고 FK는 참조를 위해 쓰는 다른 테이블의 메인 속성이라 생각하면 된다.
물론 현재 테이블의 FK는 다른 테이블에서 PK로 쓰인다.

문제 1 (없어진 기록 찾기)

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

예를 들어, ANIMAL_INS 테이블과 ANIMAL_OUTS 테이블이 다음과 같다면

ANIMAL_INS

ANIMAL_ID	ANIMAL_TYPE	DATETIME	INTAKE_CONDITION	NAME	SEX_UPON_INTAKE
A352713	Cat	2017-04-13 16:29:00	Normal	Gia	Spayed Female
A350375	Cat	2017-03-06 15:01:00	Normal	Meo	Neutered Male

ANIMAL_OUTS

ANIMAL_ID	ANIMAL_TYPE	DATETIME	NAME	SEX_UPON_OUTCOME
A349733	Dog	2017-09-27 19:09:00	Allie	Spayed Female
A352713	Cat	2017-04-25 12:25:00	Gia	Spayed Female
A349990	Cat	2018-02-02 14:18:00	Spice	Spayed Female

ANIMAL_OUTS 테이블에서

• Allie의 ID는 ANIMAL_INS에 없으므로, Allie의 데이터는 유실되었습니다.
• Gia의 ID는 ANIMAL_INS에 있으므로, Gia의 데이터는 유실되지 않았습니다.
• Spice의 ID는 ANIMAL_INS에 없으므로, Spice의 데이터는 유실되었습니다.

따라서 SQL문을 실행하면 다음과 같이 나와야 합니다.

ANIMAL_ID	NAME
A349733	Allie
A349990	Spice

나만의 풀이 1

일단, 기준은 ANIMAL_OUTS 테이블임을 잊지 말자.
뽑아야 할 속성은 아이디와 이름이고, 아이디순으로 정렬하도록 조건이 나와있다.
따라서 가장 바깥에 짜는 코드의 형태는 다음의 구조로 만들면 될 것이다.

SELECT ANIMAL_ID, NAME FROM ANIMAL_OUTS
WHERE /* 무언가 조건이 있을 것. */
ORDER BY ANIMAL_ID

그런데 문제가 있다.
ANIMAL_INS에는 없는데, ANIMAL_OUTS에 있는 데이터를 뽑아야 한다.
이를 바로 추출할만한 방법이 없다.
따라서 약간의 트릭을 쓸 생각이다.

만일 우리가 ANIMAL_INS와 ANIMAL_OUTS에 모두 있는 원소를 뽑을 수 있다 하자.
이후 ANIMAL_OUTS에서 해당 원소를 제거해주면 원하는 데이터를 얻어낼 수 있다.
즉, 먼저 ANIMAL_INS와 ANIMAL_OUTS를 JOIN해서 ANIMAL_ID를 뽑아내고, 이후 ANIMAL_OUTS의 데이터 중 앞선 데이터에 없는 친구들만 선택한다.

그러면, 별도로 ANIMAL_INS와 ANIMAL_OUTS에 겹치는 원소들을 추출하는 코드를 짜야 한다.
이는 다음과 같이 작성할 수 있다.

SELECT AI.ANIMAL_ID FROM ANIMAL_INS AI
JOIN ANIMAL_OUTS AO ON AI.ANIMAL_ID = AO.ANIMAL_ID

기본적으로 JOIN구문은 SELECT ~ FROM ~ JOIN ~ ON ~ WHERE ~ 꼴이라 보면 된다.
(무조건 그렇다는 것은 아니고, 위 구조를 참고해서 적절히 변형하면 된다.)
단순히 id만 뽑아낼 생각이니, WHERE는 사용하지 않고, 다음과 같이 구현했다.
이때, 일일이 table 명을 쓰면 귀찮으니, ANIMAL_INS는 AI로, ANIMAL_OUTS는 AO로 바꿔서 사용했다.
따라서, ANIMAL_INS.ANIMAL_ID 대신 AI.ANIMAL_ID로 코드를 짤 수 있다.
그 외에는 약간 해석해보면 금방 알 수 있을 거라 본다.

마지막으로, 위 문장을 앞서 작성한 틀에 끼워넣으면 된다.
이때, 이 데이터에 없는 친구들만 선택해야 하므로, NOT IN을 추가로 넣으면 된다.

내가 구현한 코드는 아래와 같다.

SELECT ANIMAL_ID, NAME FROM ANIMAL_OUTS
WHERE ANIMAL_ID NOT IN (
    SELECT AI.ANIMAL_ID FROM ANIMAL_INS AI
    JOIN ANIMAL_OUTS AO ON AI.ANIMAL_ID = AO.ANIMAL_ID
)
ORDER BY ANIMAL_ID

문제 2 (있었는데요 없었습니다)

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

예를 들어, ANIMAL_INS 테이블과 ANIMAL_OUTS 테이블이 다음과 같다면

ANIMAL_INS

ANIMAL_ID	ANIMAL_TYPE	DATETIME	INTAKE_CONDITION	NAME	SEX_UPON_INTAKE
A350276	Cat	2017-08-13 13:50:00	Normal	Jewel	Spayed Female
A381217	Dog	2017-07-08 09:41:00	Sick	Cherokee	Neutered Male

ANIMAL_OUTS

ANIMAL_ID	ANIMAL_TYPE	DATETIME	NAME	SEX_UPON_OUTCOME
A350276	Cat	2018-01-28 17:51:00	Jewel	Spayed Female
A381217	Dog	2017-06-09 18:51:00	Cherokee	Neutered Male

SQL문을 실행하면 다음과 같이 나와야 합니다.

ANIMAL_ID	NAME
A381217	Cherokee

나만의 풀이 2

아까 문제 1의 코드를 짜는 방법을 이해했으면, 문제 2도 조금만 생각하면 금방 풀 수 있다.
일단 양쪽 table에 모두 정보가 있는 친구들만 JOIN을 써서 뽑아낸다.
이때, 조건으로 ANIMAL_INS의 DATETIME값이 ANIMAL_OUTS의 DATETIME값보다 큰 경우만 추출하면 된다.
보호 시작일보다 입양일이 빠른 케이스만 보여주면 되기 때문이다.
여기선 별도로 괄호를 쓸 필요가 없다.

아래의 전체 코드가 있다.

SELECT AI.ANIMAL_ID, AI.NAME FROM ANIMAL_INS AI
JOIN ANIMAL_OUTS AO ON AI.ANIMAL_ID = AO.ANIMAL_ID
WHERE AI.DATETIME > AO.DATETIME
ORDER BY AI.DATETIME

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

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

예를 들어, ANIMAL_INS 테이블과 ANIMAL_OUTS 테이블이 다음과 같다면

ANIMAL_INS

ANIMAL_ID	ANIMAL_TYPE	DATETIME	INTAKE_CONDITION	NAME	SEX_UPON_INTAKE
A354597	Cat	2014-05-02 12:16:00	Normal	Ariel	Spayed Female
A373687	Dog	2014-03-20 12:31:00	Normal	Rosie	Spayed Female
A412697	Dog	2016-01-03 16:25:00	Normal	Jackie	Neutered Male
A413789	Dog	2016-04-19 13:28:00	Normal	Benji	Spayed Female
A414198	Dog	2015-01-29 15:01:00	Normal	Shelly	Spayed Female
A368930	Dog	2014-06-08 13:20:00	Normal		Spayed Female

ANIMAL_OUTS

ANIMAL_ID	ANIMAL_TYPE	DATETIME	NAME	SEX_UPON_OUTCOME
A354597	Cat	2014-05-02 12:16:00	Ariel	Spayed Female
A373687	Dog	2014-03-20 12:31:00	Rosie	Spayed Female
A368930	Dog	2014-06-13 15:52:00		Spayed Female

SQL문을 실행하면 다음과 같이 나와야 합니다.

NAME	DATETIME
Shelly	2015-01-29 15:01:00
Jackie	2016-01-03 16:25:00
Benji	2016-04-19 13:28:00

※ 입양을 가지 못한 동물이 3마리 이상인 경우만 입력으로 주어집니다.

나만의 풀이 3

이번엔 문제 1과 비슷한 구조로 괄호를 써서 풀어낼 수 있다.
내부에 들어가는 JOIN절은 문제 1과 동일하다.
반면 이번엔 외부 틀이 ANIMAL_INS를 참고하고 있다.
왜냐하면 아직 입양을 못 간 동물을 뽑아내야 하기 때문이다.

그리고, 맨 처음 SELECT문 챕터를 풀면서 나왔던 LIMIT 메소드를 활용해서 상위 3마리만 출력해주면 된다.
즉, 다음과 같이 명령을 짤 수 있다.

SELECT NAME, DATETIME FROM ANIMAL_INS
WHERE ANIMAL_ID NOT IN (
    SELECT AI.ANIMAL_ID FROM ANIMAL_INS AI
    JOIN ANIMAL_OUTS AO ON AI.ANIMAL_ID = AO.ANIMAL_ID
)
ORDER BY DATETIME
LIMIT 3

문제 4 (보호소에서 중성화한 동물)

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

예를 들어, ANIMAL_INS 테이블과 ANIMAL_OUTS 테이블이 다음과 같다면

ANIMAL_INS

ANIMAL_ID	ANIMAL_TYPE	DATETIME	INTAKE_CONDITION	NAME	SEX_UPON_INTAKE
A367438	Dog	2015-09-10 16:01:00	Normal	Cookie	Spayed Female
A382192	Dog	2015-03-13 13:14:00	Normal	Maxwell 2	Intact Male
A405494	Dog	2014-05-16 14:17:00	Normal	Kaila	Spayed Female
A410330	Dog	2016-09-11 14:09:00	Sick	Chewy	Intact Female

ANIMAL_OUTS

ANIMAL_ID	ANIMAL_TYPE	DATETIME	NAME	SEX_UPON_OUTCOME
A367438	Dog	2015-09-12 13:30:00	Cookie	Spayed Female
A382192	Dog	2015-03-16 13:46:00	Maxwell 2	Neutered Male
A405494	Dog	2014-05-20 11:44:00	Kaila	Spayed Female
A410330	Dog	2016-09-13 13:46:00	Chewy	Spayed Female

• Cookie는 보호소에 들어올 당시에 이미 중성화되어있었습니다.
• Maxwell 2는 보호소에 들어온 후 중성화되었습니다.
• Kaila는 보호소에 들어올 당시에 이미 중성화되어있었습니다.
• Chewy는 보호소에 들어온 후 중성화되었습니다.

따라서 SQL문을 실행하면 다음과 같이 나와야 합니다.

ANIMAL_ID	ANIMAL_TYPE	NAME
A382192	Dog	Maxwell 2
A410330	Dog	Chewy

이때, 중성화를 거치지 않은 동물은 성별 및 중성화 여부에 Intact, 중성화를 거친 동물은 Spayed 또는 Neutered라고 표시되어있습니다.

나만의 풀이 4

사실 이 문제는 말이 레벨 4지, JOIN연산과 LIKE연산을 적절히 쓸 줄 안다면 3 이하로 줄어든다고 볼 수 있다.
물론 다른 메소드를 쓴다면 모르겠으나, 나는 저 2가지 연산을 계속 조합해서 구현하였다.

일단, 앞선 문제들처럼 기본 형태는 다음과 같다.

SELECT AI.ANIMAL_ID, AI.ANIMAL_TYPE, AI.NAME FROM ANIMAL_INS AI
JOIN ANIMAL_OUTS AO ON AI.ANIMAL_ID = AO.ANIMAL_ID
WHERE /* 조건 */
ORDER BY AI.ANIMAL_ID

편의상 ANIMAL_INS를 기준으로 조인을 수행했다.
(문제 상에서 테이블 구조가 ANIMAL_INS를 기준으로 되어있는 것도 있고,)

이제, 들어왔을 때 상태에 Intact가 포함되어 있고, 보호소를 나갔을 때 상태엔 Spayed나 Neutered가 있는 경우만 뽑으면 된다.
이를 구현하는 코드는 겉보기엔 복잡해도 생각보다 간단하다.

WHERE AI.SEX_UPON_INTAKE LIKE "%Intact%" AND
(AO.SEX_UPON_OUTCOME LIKE "%Spayed%" OR AO.SEX_UPON_OUTCOME LIKE "%Neutered%")

위 WHERE절이 앞서 말한 동작을 수행하는 파트이다.
나눠서 해석해보겠다.
먼저, 큰 구조는, WHERE (1번 조건) AND (2번 조건) 구조이다.

1번 조건엔 AI.SEX_UPON_INTAKE LIKE "%Intact%"가 들어가 있다.
해석하면, AI.SEX_UPON_INTAKE속성에 Intact가 포함된 것만 추출한다는 의미라 보면 된다.
LIKE 연산은 문자열과 결합해서 일종의 정규식 역할을 한다.
와일드 카드에 해당하는 문자는 퍼센트(%) 이며, 테이터를 봤을 때, 그냥 "Intact%"로 해도 되겠지만, 혹시 몰라서 안전하게 "%Intact%"로 했다.
(양쪽에 뭐가 붙건 중간에 Intact가 있으면 된다는 의미이다.)

2번 조건은 다시 (2-a 조건) OR (2-b 조건)으로 나뉜다.
2-a 조건과 2-b 조건은 위와 같이 Spayed와 Neutered의 포함 여부를 각각 판별한다.
AND와 OR의 역할 정도는 기본적인 내용이니 이에 관한 생략하겠다.

따라서 전체 코드는 다음과 같이 만들어진다.

SELECT AI.ANIMAL_ID, AI.ANIMAL_TYPE, AI.NAME FROM ANIMAL_INS AI
JOIN ANIMAL_OUTS AO ON AI.ANIMAL_ID = AO.ANIMAL_ID
WHERE AI.SEX_UPON_INTAKE LIKE "%Intact%" AND
(AO.SEX_UPON_OUTCOME LIKE "%Spayed%" OR AO.SEX_UPON_OUTCOME LIKE "%Neutered%")
ORDER BY AI.ANIMAL_ID

마치면서

드디어 JOIN연산을 주로 쓰는 문제들에 관한 해설이 끝났다.
아까도 말했지만, JOIN연산은 복잡하지만 자주 쓰는 연산에 속한다.
과거 데이터베이스 강의 들었을 때도 기말고사 때 대부분이 JOIN연산을 써야 문제가 풀리는 정도였으니 실무에선 더 많이 쓸 것으로 예상된다.
(3번 연속 JOIN을 해야 풀리는 문제를 경험했다.) (아니 그런 금융데이터는 어디서 구하신거지)

이제, 프로그래머스에 나와있는 SQL관련 마지막 챕터로 String과 Date 등의 자료형 정도가 남았다.
여기선 몇몇 메소드 정도만 알면 풀 수 있는 수준이며, 참고용으로 알고 있으면 좋은 문제 정도라 볼 수 있다.
아무튼 마무리 관련 내용은 다음 포스트에서 작성할 계획이다.