[프로그래머스/SQL] JOIN (MySQL)

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

프로그래머스

 

없어진 기록 찾기

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS OUTS
LEFT OUTER JOIN ANIMAL_INS INS
ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL
ORDER BY OUTS.ANIMAL_ID;

 

있었는데요 없었습니다

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS OUTS
LEFT OUTER JOIN ANIMAL_INS INS
ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE OUTS.DATETIME < INS.DATETIME
ORDER BY INS.DATETIME;

 

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

SELECT INS.NAME, INS.DATETIME
FROM ANIMAL_INS INS
LEFT OUTER JOIN ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE OUTS.ANIMAL_ID IS NULL
ORDER BY INS.DATETIME
LIMIT 3;

 

보호소에서 중성화한 동물

SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME
FROM ANIMAL_INS INS
LEFT OUTER JOIN ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.SEX_UPON_INTAKE != OUTS.SEX_UPON_OUTCOME
ORDER BY INS.ANIMAL_ID;

-----------------------------------------------------------------------

SELECT OUTS.ANIMAL_ID, OUTS.ANIMAL_TYPE, OUTS.NAME
FROM (SELECT *
      FROM ANIMAL_INS INS
      WHERE SEX_UPON_INTAKE LIKE 'Intact%') SQ, ANIMAL_OUTS OUTS
WHERE SQ.ANIMAL_ID = OUTS.ANIMAL_ID AND OUTS.SEX_UPON_OUTCOME NOT LIKE 'Intact%'
ORDER BY OUTS.ANIMAL_ID;