- λ ν μ΄λΈμ JOB μ΄ κ°μ κ²μ κΈ°μ€μΌλ‘ LEFT OUTER JOINμ μ§νν©λλ€.
- LEFT OUTER JOINμ μΌμͺ½μ μ€λ ν μ΄λΈμ κΈ°μ€μΌλ‘, μ€λ₯Έμͺ½μ μ€λ ν μ΄λΈκ³Ό λΉκ΅νμ¬ μ‘°κ±΄μ λ§λ κ°μ΄ μμΌλ©΄ JOINνμ¬ κ°μ Έμ€κ³ , κ°μ΄ μμΌλ©΄ null κ°μ νμν©λλ€.
- μ¦ LEFT OUTER JOINμ μ΄μ²λΌ JOIN λ¬Έμ μνν λ, μΌμͺ½μ μλ λ°μ΄ν°λ 무쑰건 κ°μ Έμ€λ©°, μ€λ₯Έμͺ½μ μ€λ ν μ΄λΈκ³Ό JOINμ μννμ¬ μ‘°κ±΄μ λ§λ λ°μ΄ν°κ° μμ μ null λ‘ νμνκ² λ©λλ€.
OUTSμλ μλλ°, INSμλ μλ λλ¬Όλ€ μΆλ ₯νλΌ
SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS OUTS
LEFT OUTER JOIN ANIMAL_INS INS
ON INS.ANIMAL_ID=OUTS.ANIMAL_ID
WHERE INS.ANIMAL_ID is null
μ¬μ§μ μΌμͺ½ μ λκ·ΈλΌλ―Έπ― LEFT OUTER JOIN = ν©μ§ν©
( LEFT OUTER JOIN = LEFT JOIN ) => OUTER μ μλ΅ν ννμ
μ¬μ§μ κ°μ΄ λ° λκ·ΈλΌλ―Έπ― INNER JOIN = κ΅μ§ν©
( INNER JOIN = JOIN ) => κ·Έλ¬λ κ°λ μ±μ μν΄ INNER μ λΆμ¬μ£Όλ κ²μ΄ μ’λ€.
보νΈμμμΌ λ³΄λ€ μ μμΌμ΄ λ λΉ λ₯Έ λλ¬Ό
SELECT OUTS.ANIMAL_ID,OUTS.NAME
FROM ANIMAL_OUTS OUTS
INNER JOIN ANIMAL_INS INS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.DATETIME > OUTS.DATETIME
ORDER BY INS.DATETIME
Datetime λΉκ΅λ₯Ό μ΄λ»κ² νμ§ νλλ° κ·Έλ₯ λΆλ±νΈ μ°λ©΄ λλ€.
μλ κ° < μ΅μ κ°
μ μμ λͺ» κ° λλ¬Ό μ€, κ°μ₯ μ€λλ λλ¬Ό
SELECT INS.NAME, INS.DATETIME
FROM ANIMAL_INS INS
LEFT OUTER JOIN ANIMAL_OUTS OUTS
ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE OUTS.DATETIME is null
ORDER BY DATETIME
limit 3
μ΄μ μ μ€μ±ν μλλλ°, μ΄ν μ€μ±ν λ λλ¬Ό μ°ΎκΈ°
SELECT INS.ANIMAL_ID,INS.ANIMAL_TYPE,INS.NAME
FROM ANIMAL_INS INS
INNER JOIN ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID=OUTS.ANIMAL_ID
WHERE (SEX_UPON_INTAKE = 'Intact Female' OR SEX_UPON_INTAKE = 'Intact Male')
AND (SEX_UPON_OUTCOME = 'Spayed Female' OR SEX_UPON_OUTCOME = 'Neutered Male')
μ°μλλ° λ§μλ€.
Whereμ OR, AND λ±μ μ¬μ©ν μ μλ€.
SELECT I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME
FROM ANIMAL_INS as I JOIN ANIMAL_OUTS as O
WHERE I.ANIMAL_ID = O.ANIMAL_ID AND I.SEX_UPON_INTAKE != O.SEX_UPON_OUTCOME