[코딩테스트 연습- SQL] JOIN

1. 없어진 기록 찾기

-- RIGHT JOIN
SELECT B.ANIMAL_ID, B.NAME
FROM ANIMAL_INS A 
RIGHT JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID=B.ANIMAL_ID
WHERE A.ANIMAL_ID IS NULL
ORDER BY ANIMAL_ID

-- NOT IN
SELECT ANIMAL_ID, NAME
FROM ANIMAL_OUTS
WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_INS)

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

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

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

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

-- NOT IN
SELECT NAME, DATETIME
FROM ANIMAL_INS
WHERE ANIMAL_ID NOT IN(
    SELECT ANIMAL_ID
    FROM ANIMAL_OUTS
)
ORDER BY DATETIME
LIMIT 3

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

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