문제 출처. [프로그래머스] JOIN 문제
문제
풀이
LEFT JOIN
을 한 뒤에 ANIAML_ID가 일치하는 것을 가져온다.SELECT `OUT`.ANIMAL_ID, `OUT`.NAME
FROM ANIMAL_OUTS AS `OUT`
LEFT JOIN ANIMAL_INS AS `IN`
ON `OUT`.ANIMAL_ID = `IN`.ANIMAL_ID
WHERE `IN`.ANIMAL_ID IS NULL
ORDER BY `OUT`.ANIMAL_ID;
-- JOIN을 사용하지 않는 경우
SELECT `OUT`.ANIMAL_ID, `OUT`.NAME FROM ANIMAL_OUTS AS `OUT`
WHERE `OUT`.ANIMAL_ID NOT IN (
SELECT `IN`.ANIMAL_ID FROM ANIMAL_INS AS `IN`
) ORDER BY `OUT`.ANIMAL_ID;
문제
풀이
INNER JOIN
SELECT `IN`.ANIMAL_ID, `IN`.NAME
FROM ANIMAL_INS AS `IN`
INNER JOIN ANIMAL_OUTS AS `OUT`
ON `IN`.ANIMAL_ID = `OUT`.ANIMAL_ID
WHERE `IN`.DATETIME > `OUT`.DATETIME
ORDER BY `IN`.DATETIME;
문제
풀이
LEFT JOIN
을 했을 때, ANIMAL_OUTS.ANIMAL_ID가 NULL인 레코드이다.ASC
정렬했을 때 LIMIT 3
을 하면 된다.SELECT `IN`.NAME, `IN`.DATETIME
FROM ANIMAL_INS AS `IN`
LEFT JOIN ANIMAL_OUTS AS `OUT`
ON `IN`.ANIMAL_ID = `OUT`.ANIMAL_ID
WHERE `OUT`.ANIMAL_ID IS NULL
ORDER BY `IN`.DATETIME LIMIT 3;
문제
풀이
INNER JOIN
LIKE "Intact%"
조건SELECT `IN`.ANIMAL_ID, `IN`.ANIMAL_TYPE, `IN`.NAME
FROM ANIMAL_INS AS `IN`
INNER JOIN ANIMAL_OUTS AS `OUT`
ON `IN`.ANIMAL_ID = `OUT`.ANIMAL_ID
WHERE `IN`.SEX_UPON_INTAKE LIKE "Intact%"
AND
(`OUT`.SEX_UPON_OUTCOME LIKE "Spayed%"
OR `OUT`.SEX_UPON_OUTCOME LIKE "Neutered%")
ORDER BY `IN`.ANIMAL_ID;