PG SQL πŸ“Œ JOIN

imsΒ·2021λ…„ 2μ›” 25일
0

SQL

λͺ©λ‘ 보기
11/12

πŸ“Œ 없어진 기둝 μ°ΎκΈ°

πŸ”₯ Left Outer Join

  • 두 ν…Œμ΄λΈ”μ˜ JOB 이 같은 것을 κΈ°μ€€μœΌλ‘œ LEFT OUTER JOIN을 μ§„ν–‰ν•©λ‹ˆλ‹€.
  • LEFT OUTER JOIN은 μ™Όμͺ½μ— μ˜€λŠ” ν…Œμ΄λΈ”μ„ κΈ°μ€€μœΌλ‘œ, 였λ₯Έμͺ½μ— μ˜€λŠ” ν…Œμ΄λΈ”κ³Ό λΉ„κ΅ν•˜μ—¬ 쑰건에 λ§žλŠ” 값이 있으면 JOINν•˜μ—¬ κ°€μ Έμ˜€κ³ , 값이 μ—†μœΌλ©΄ null 값을 ν‘œμ‹œν•©λ‹ˆλ‹€.
  • 즉 LEFT OUTER JOIN은 이처럼 JOIN 문을 μˆ˜ν–‰ν•  λ•Œ, μ™Όμͺ½μ— μžˆλŠ” λ°μ΄ν„°λŠ” 무쑰건 κ°€μ Έμ˜€λ©°, 였λ₯Έμͺ½μ— μ˜€λŠ” ν…Œμ΄λΈ”κ³Ό JOIN을 μˆ˜ν–‰ν•˜μ—¬ 쑰건에 λ§žλŠ” 데이터가 없을 μ‹œ null 둜 ν‘œμ‹œν•˜κ²Œ λ©λ‹ˆλ‹€.

    https://chanhuiseok.github.io/posts/db-7/

πŸ—¨ 문제

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

πŸ“Œ μžˆμ—ˆλŠ”λ°μš”, μ—†μ—ˆμŠ΅λ‹ˆλ‹€

πŸ”₯ INNER JOIN

μ‚¬μ§„μ˜ μ™Όμͺ½ μœ„ λ™κ·ΈλΌλ―ΈπŸŽ― 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 비ꡐλ₯Ό μ–΄λ–»κ²Œ ν•˜μ§€ ν–ˆλŠ”λ° κ·Έλƒ₯ λΆ€λ“±ν˜Έ μ“°λ©΄ 됐닀.

  • μ˜›λ‚  κ°’ < μ΅œμ‹  κ°’

πŸ“Œ 였랜 κΈ°κ°„ λ³΄ν˜Έν•œ 동물 (1)

πŸ—¨ 문제

μž…μ–‘μ„ λͺ» κ°„ 동물 쀑, κ°€μž₯ 였래된 동물

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

https://mungto.tistory.com/282

  • 이게 더 κ°„λ‹¨ν•˜λ„€
profile
ν‹°μŠ€ν† λ¦¬λ‘œ μ΄μ‚¬ν–ˆμŠ΅λ‹ˆλ‹€! https://imsfromseoul.tistory.com/ + https://camel-man-ims.tistory.com/

0개의 λŒ“κΈ€