5. JOIN

문제

1) 없어진 기록 찾기

SELECT B.ANIMAL_ID, B.NAME 
    FROM ANIMAL_INS A right outer join ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
    WHERE A.ANIMAL_ID IS NULL

JOIN을 다루는 문제에서는 pandas의 merge에 대한 개념을 정말 비슷하게 다루고 있다. 먼저 1번은 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 문제이다.

즉, 보호소에 들어오는 기록의 테이블인 animal_ins와 입양을 가는 기록의 테이블인 animal_outs의 병합을 통해 animal_out에는 기록은 있지만 animal_ins에서 기록이 없는 동물을 조회하는 문제이다.

간단하게 입양을 간 기록이 존재하는 동물을 알기 위해서 입양 기록이 존재하는 동물의 animal_id를 활용해 보호소 테이블를 병합했다. right outer join을 사용하게 되면 입양간 기록에 대한 동물에 따른 보호소 테이블의 정보가 병합된다. 여기서 데이터 유실로 발생하게 된 보호소 테이블의 Null값은 animal_ins의 id가 Null인 조건을 조회하면 된다.

2) 있었는데요 없었습니다

SELECT A.ANIMAL_ID, A.NAME FROM ANIMAL_INS A JOIN ANIMAL_OUTS B
    ON A.ANIMAL_ID = B.ANIMAL_ID
    WHERE A.DATETIME > B.DATETIME
    ORDER BY A.DATETIME;

2번은 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 문제이다. 이번 문제는 보호소 테이블과 입양 테이블의 데이터가 모두 존재하는 데이터를 일단 확보해야하기 때문에 inner join을 적용하게 되었다.

여기서 animal_ins의 datetime이 animal_outs의 datetime보다 큰 경우, 보호 시작일이 더 늦은 경우를 조회하면 된다.

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

SELECT A.NAME, A.DATETIME FROM ANIMAL_INS A LEFT JOIN ANIMAL_OUTS B
    ON A.ANIMAL_ID = B.ANIMAL_ID
    WHERE B.DATETIME IS NULL
    ORDER BY A.DATETIME ASC
    LIMIT 3;

3번은 아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 문제이다. 이번 문제 에서는 입양 기록이 없는 아직 없는 동물을 조회하기 위해서 animal_ins의 전체 동물 중에서 animal_outs의 animal_id가 Null인 동물만 조회하면 된다.

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

SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME FROM ANIMAL_INS A JOIN ANIMAL_OUTS B
    ON A.ANIMAL_ID = B.ANIMAL_ID
    WHERE A.SEX_UPON_INTAKE LIKE 'Intact%' AND B.SEX_UPON_OUTCOME REGEXP 'Neutered|Spayed';

4번은 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 문제이다. 먼저 중성화되지 않은 경우는 intact, 중성화된 경우는 Spayed 또는 Neutered라고 표시된다.

보호소 테이블과 입양 테이블에 모두 존재하는 동물을 조회하기 위해서 inner join을 사용했고 보호소 테이블에서는 intact인, 입양 테이블에서는 Spayed or Neutered 조건을 사용하면 된다. python에서는 str.contains()를 사용해서 해당 문구가 포함되어 있는 행을 추출할 수 있지만 mysql에서는 like를 먼저 떠올릴 수 있다. 그런데 like의 경우에는 Spayed와 Neutered를 모두 조건에 적용하려면 각각 적용을 해줘야한다. 하지만 REGEXP의 경우에는 '|'를 사용해서 한번에 조건을 적용할 수 있다.