[Solv:프로그래머스] SQL JOIN - 보호소에서 중성화한 동물

문제설명

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

 

내 풀이

구질구질하게 풀었음.
내가 좋아하는(?) not in 쓰는 이중쿼리와 조인문 둘다 쓴 버전

SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME
FROM ANIMAL_INS INS LEFT JOIN ANIMAL_OUTS OUTS
    ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE 1=1
AND INS.SEX_UPON_INTAKE like 'Intact%'
AND OUTS.SEX_UPON_OUTCOME NOT IN 
    (SELECT SEX_UPON_OUTCOME 
     FROM ANIMAL_OUTS
     WHERE SEX_UPON_OUTCOME LIKE 'Intact%')

 

다른 사람 풀이

둘 다 똑같음. NOT LIKE를 썼느냐 <>를 썼느냐의 차이

SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME
FROM ANIMAL_INS INS JOIN ANIMAL_OUTS OUTS
WHERE INS.ANIMAL_ID = OUTS.ANIMAL_ID AND INS.SEX_UPON_INTAKE NOT LIKE OUTS.SEX_UPON_OUTCOME
ORDER BY INS.ANIMAL_ID;

SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME
FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
WHERE INS.ANIMAL_ID = OUTS.ANIMAL_ID and INS.SEX_UPON_INTAKE <> OUTS.SEX_UPON_OUTCOME
ORDER BY INS.ANIMAL_ID ASC

 

OR절 쓰는거 헷갈려서 서브쿼리 썼는데 OR절로 풀었더라면 이렇게

SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME
FROM ANIMAL_INS INS LEFT JOIN ANIMAL_OUTS OUTS
    ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE 1=1
AND INS.SEX_UPON_INTAKE like 'Intact%'
AND (OUTS.SEX_UPON_INTAKE like 'Neutered%' OR OUTS.SEX_UPON_INTAKE like 'Spayed%')

정규식 사용한 버전

SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME
from ANIMAL_INS A left join ANIMAL_OUTS B
on (A.ANIMAL_ID=B.ANIMAL_ID)
where regexp_like(A.SEX_UPON_INTAKE ,'Intact')
    and regexp_like(B.SEX_UPON_OUTCOME ,'Spayed|Neutered')
order by A.ANIMAL_ID

 

이것도 맘에들어서 퍼옴

SELECT i.animal_id, i.animal_type, i.name 
from animal_ins as i inner join animal_outs as o 
on i.animal_id = o.animal_id
where i.SEX_UPON_INTAKE != o.SEX_UPON_OUTCOME
and i.SEX_UPON_INTAKE Not like 'Intact'
order by i.animal_id