[Level3] JOIN

in 코딩테스트 on SQL




문제를 풀기 전, 늘 헷갈리던 join 종류들을 구분하기 쉽게 그려놓은 이미지가 있어서 첨부!

헷갈릴때마다 참고하기👍

join

없어진 기록 찾기

programmers 


    SELECT O.ANIMAL_ID, O.NAME
     FROM ANIMAL_OUTS O
LEFT JOIN ANIMAL_INS I
       ON O.ANIMAL_ID = I.ANIMAL_ID
    WHERE I.ANIMAL_ID IS NULL


있었는데요 없었습니다

programmers 


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


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

programmers 


   SELECT I.NAME, I.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 DATETIME
    LIMIT 3


헤비 유저가 소유한 장소

programmers 


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



SELECT ID, NAME, HOST_ID
 FROM PLACES A
 WHERE (SELECT COUNT(HOST_ID) AS count
         FROM PLACES B
        WHERE A.HOST_ID=B.HOST_ID) >1;