<프로그래머스 56> 없어진 기록 찾기(Lv.3)

 

프로그래머스에서 제공하는 SQL문제 매일 풀기 챌린지

---

 

문제 설명

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

---

문제

천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 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

 

 

 

본 문제는 Kaggle의 "Austin Animal Center Shelter Intakes and Outcomes"에서 제공하는 데이터를 사용하였으며 ODbL의 적용을 받습니다.

---

문제는 2가지 방식으로 풀 수 있다.

 

1. NOT EXISTS +  EQUI JOIN 
2. LEFT OUTER JOIN을 사용

> 1번과 2번 oracle, Mysql 모두 사용 가능 

---

♥ 정답 : NOT EXISTS 와 EQUI JOIN 사용

select o.animal_id, o.name
    from animal_outs o
    where not exists
    ( select 'x'
        from animal_ins i
        where i.animal_id = o.animal_id )
    order by o.animal_id asc;

 

 

---

■ NOT EXISTS 문

A 테이블에는 존재하는데, B에는 존재하지 않는 데이터를 출력할 때 사용

- 메인쿼리(A)의 데이터를 먼저 읽고 서브쿼리(B)에 그 데이터가 존재하지 않는지 찾아본다.

 

■ EQUI JOIN 사용

두 컬럼에 동일한 데이터를 가지는 컬럼이 존재할 때 사용할 수 있는 조인방법

- 두 테이블에서 동일한 데이터를 가지는 컬럼명을 WHERE절에서 연결조건으로 사용

---

정답 해설

위의 내용을 기반으로 해설해보면,

 

# 1. MAIN QUERY ( ANIMAL_OUTS )

 

유실된 동물 데이터를 찾아야 하기 때문에

ANIMAL_OUTS에는 존재하는데, ANIMAL_INS에는 존재하지 않는 동물의 ID와 이름을 출력해야한다.

왜냐하면, ANIAML_OUTS 테이블에는 존재하는데, ANIMAL_INS 테이블에 존재하지 않는 데이터가 유실된 동물 테이터이기 때문이다.

 

 

-- SELECT절

출력해야할 이름(ANIMAL_ID)와 이름(NAME)을 입력해주는데,

컬럼을 가져올 테이블 명 혹은 테이블의 별칭을

온점(.)을 사용하여 컬렴과 연결해준다.

 

 

-- FROM절

main query로 사용할 테이블명을 기입하고,

컬럼명 앞에 편하게 명시해주기 위해서 별칭을 넣어준다. 

 

 

-- WHERE절

WHERE에 NOT EXISTS를 문과 함께 서브쿼리를 사용해준다. 

 

 

-- ORDER BY 절

ANIMAL_ID를 기준으로 ASC하게 정렬해준다.

 

 

# 2. SUB QUERY ( ANIMAL_OUTS )

 

-- SELECT절

< 방법 1 : 서브쿼리 테이블의 컬럼명을 넣어주기 >

서브쿼리의 from절의 테이블이 가지고 있는 

컬럼명 중 아무거나 넣어주거나,

 

< 방법 2 : 싱글쿼테이션으로 둘러싸여진 원하는 값 넣어주기 >

싱글쿼테이션으로 둘러싸여진 원하는 값을 넣어주면 된다.

단, NULL은 제외하고 아무런 값이나 가능하다.

 

select 'x'

 

위와 같이 'x'를 넣어주었을 경우 수행되는 원리 설명

ex)
1. main 쿼리에 450이라는 animal_Id가 존재
2. id : 450 을 sub 쿼리도 가지고 있는지 확인하기 위해 서브쿼리에 450이라는 값을 넘김
3. 서브쿼리 내에서 450이라는 값이 존재하지 않으면 x가 출력되면서 main query로 넘김
4. 서브쿼리에 없는 450에 대한 데이터가 출력됨

 

-- FROM 절

데이터를 가져올 테이블 명(ANIMAL_INS) 입력

 

-- WHERE 절

ANIMAL_INS 테이블과 ANIMAL_OUTS 테이블을

동일한 데이터를 가진 컬럼명을 연결조건으로 사용해서 JOIN해준다.

---

♥   정답 : LEFT OUTER JOIN 사용

select o.animal_id, o.name
    from animal_outs o left join animal_ins i
    on (i.animal_id = o.animal_id)
    where i.animal_id is null
    order by o.animal_id asc;

 

 

---

■ LEFT OUTER JOIN

LEFT 테이블의 모든 레코드는 유지하고, 

우측 테이블과 일치하는 레코드가 없을 때 

왼쪽 테이블의 레코드만 유지고 있음

- 즉 위의 쿼리를 보면 ANIMAL_OUTS 테이블의 모든 레코드를 유지하고,

ANIMAL_INS 테이블에 해당하는 레코드가 없는 경우에도 NULL로 결과를 반환한다.

 

---

정답 해설

위의 내용을 기반으로 해설해보면,

 

-- ON 절

ANIMAL_INS 테이블과 ANIMAL_OUTS 테이블을 동일한 데이터를 가진 컬럼명을 연결조건으로 사용해서 JOIN해준다.

 

-- WHERE 절

LEFT OUTER JOIN을 사용한 이유를 해석해보면,

ANIMAL_INS 테이블의 ANIMAL_ID 가 ANIMAL_OUTS 테이블에 존재하지 않을 때 NULL 값으로 출력되기 때문이다.

 

따라서 ANIMAL_INS 테이블의 ANIMAL_ID가  IS NULL 인 데이터만 출력한다는 조건을 넣어주면,

결국 유실된 동물의 데이터를 출력해주게 된다. 

유실된 동물의 데이터는 ANIMAL_OUTS 테이블에만 존재하기 때문에 이러한 조건을 사용해주게 된 것이다.

 

 

 

 

 

출처 : 프로그래머스 코딩 테스트 연습, https://school.programmers.co.kr/learn/challenges