Database
programmers solution
philo0407
2020. 10. 1. 23:09
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;