[프로그래머스] [SQL] JOIN 모음

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

프로그래머스

--없어진 기록 찾기
SELECT A.ANIMAL_ID, A.NAME 
FROM ANIMAL_OUTS A
WHERE NOT EXISTS(
    SELECT * FROM ANIMAL_INS B
    WHERE A.ANIMAL_ID = B.ANIMAL_ID
)

SELECT A.ANIMAL_ID, A.NAME 
FROM ANIMAL_OUTS A
LEFT JOIN ANIMAL_INS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.DATETIME IS NOT NULL 
	AND B.DATETIME IS NULL
    
--있었는데요 없었습니다
SELECT A.ANIMAL_ID, A.NAME 
FROM ANIMAL_INS A, ANIMAL_OUTS B 
WHERE A.DATETIME > B.DATETIME 
	AND A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY A.DATETIME ASC

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

--오랜 기간 보호한 동물(1) SUBQUERY
SELECT A.NAME, A.DATETIME 
FROM ANIMAL_INS A
WHERE A.ANIMAL_ID NOT IN (SELECT B.ANIMAL_ID FROM ANIMAL_OUTS B) #입양못간
ORDER BY A.DATETIME ASC
LIMIT 3

--보호소에서 중성화한 동물
SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME FROM ANIMAL_INS A, ANIMAL_OUTS B
WHERE A.SEX_UPON_INTAKE LIKE 'Intact%' 
AND (B.SEX_UPON_OUTCOME LIKE 'Spayed%' OR B.SEX_UPON_OUTCOME LIKE 'Neutered%')
AND A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY A.ANIMAL_ID ASC

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 A.SEX_UPON_INTAKE LIKE "Intact%"
AND (B.SEX_UPON_OUTCOME LIKE "Spayed%" OR B.SEX_UPON_OUTCOME LIKE "Neutered%")
ORDER BY A.ANIMAL_ID