[Programmers] SQL 고득점 Kit - JOIN

없어진 기록 찾기

MySQL / Oracle

SELECT O.ANIMAL_ID, O.NAME
  FROM ANIMAL_INS I RIGHT JOIN ANIMAL_OUTS O
    ON I.ANIMAL_ID = O.ANIMAL_ID
 WHERE I.ANIMAL_ID IS NULL
 ORDER BY O.ANIMAL_ID ASC

있었는데요 없었습니다

MySQL / Oracle

SELECT I.ANIMAL_ID, I.NAME
  FROM ANIMAL_INS I JOIN ANIMAL_OUTS O
    ON I.ANIMAL_ID = O.ANIMAL_ID 
 WHERE I.DATETIME > O.DATETIME
 ORDER BY I.DATETIME ASC

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

MySQL

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

Oracle

SELECT * 
FROM
(
    SELECT I.NAME NAME, I.DATETIME DATETIME
      FROM ANIMAL_INS I LEFT JOIN ANIMAL_OUTS O
        ON I.ANIMAL_ID = O.ANIMAL_ID
     WHERE O.ANIMAL_ID IS NULL
     ORDER BY I.DATETIME
)
WHERE ROWNUM <= 3

보호소에서 중성화한 동물

MySQL / Oracle

SELECT I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME
  FROM ANIMAL_INS I JOIN ANIMAL_OUTS O
    ON I.ANIMAL_ID = O.ANIMAL_ID
 WHERE I.SEX_UPON_INTAKE LIKE 'Intact%'
   AND (O.SEX_UPON_OUTCOME LIKE 'Spayed%' 
    OR O.SEX_UPON_OUTCOME LIKE 'Neutered%')
 ORDER BY I.ANIMAL_ID