[SQL 고득점 Kit] Join

💻 프로그래머스 SQL 고득점 Kit 

🧷없어진 기록 찾기

- 방법(1)

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

 

- 방법(2)

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

 

🧷있었는데요 없었습니다

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

 

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

SELECT a.NAME, a.DATETIME
  FROM ANIMAL_INS a 
  LEFT JOIN ANIMAL_OUTS b ON a.ANIMAL_ID = b.ANIMAL_ID
  WHERE b.ANIMAL_ID is NULL
  ORDER BY a.DATETIME LIMIT 3;

 

🧷보호소에서 중성화한 동물

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