programmers solution

JOIN > 없어진 기록 찾기

 

SELECT R.ANIMAL_ID, R.NAME
	FROM ANIMAL_INS L
	RIGHT JOIN ANIMAL_OUTS R
	ON L.ANIMAL_ID = R.ANIMAL_ID
WHERE L.ANIMAL_ID IS NULL
ORDER BY R.ANIMAL_ID;

=> 다시 풀었을때

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

 

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

 

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

정렬과 갯수 제한이 들어갔을 때는

정렬이 들어간 테이블을 서브로 넣고

갯수제한을 밖으로 뺀다..

 

 

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