[MySQL] Programmers Lv.3 전체 문제 풀이

없어진 기록 찾기

-- join 미사용
SELECT ANIMAL_ID, NAME
FROM ANIMAL_OUTS
WHERE ANIMAL_ID NOT IN (
SELECT ANIMAL_ID
FROM ANIMAL_INS)

-- join 사용
SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS AS OUTS
LEFT OUTER JOIN ANIMAL_INS AS INS
ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL
ORDER BY OUTS.ANIMAL_ID

🖍 JOIN 사용할 때는 각 테이블에 이름 붙여서 써주고, ON으로 외래키 맞춰주기

🖍 OUTER JOIN 사용하면 서로 어떤 차이가 있는지 알 수 있음

(https://seolhee2750.tistory.com/175)

[MySQL] INNER, OUTER JOIN 정리

 

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

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

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

 

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

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_INS AS INS
JOIN ANIMAL_OUTS AS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
ORDER BY OUTS.DATETIME - INS.DATETIME DESC
LIMIT 2

 

있었는데요 없었습니다

SELECT INS.ANIMAL_ID, INS.NAME
FROM ANIMAL_INS AS INS
JOIN ANIMAL_OUTS AS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.DATETIME > OUTS.DATETIME
ORDER BY INS.DATETIME

 

헤비 유저가 소유한 장소

SELECT ID, NAME, HOST_ID
FROM PLACES
WHERE HOST_ID IN (
SELECT HOST_ID
FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(HOST_ID) >= 2)
ORDER BY ID