JOIN์˜ ์ข…๋ฅ˜

Cartesian Product(๊ณฑ์ง‘ํ•ฉ) : ๊ฐ€๋Šฅํ•œ ๋ชจ๋“  ๋ฐฉ๋ฒ•์œผ๋กœ ๊ฒฐํ•ฉํ•จ.

Equi-join : ๊ฐ ํ…Œ์ด๋ธ”์—์„œ ํ•˜๋‚˜์”ฉ ์นผ๋Ÿผ์„ ์„ ํƒํ•˜์—ฌ ๊ทธ ๊ฐ’๋“ค์ด ๊ฐ™์€ ํ–‰๋“ค๋งŒ ์„œ๋กœ ์—ฐ๊ฒฐ์‹œํ‚ด.

Non-Equi-join : ๋™๋“ฑ ๋น„๊ต ์™ธ์˜ ๋‹ค๋ฅธ ์—ฐ์‚ฐ์ž ์‚ฌ์šฉ.

Self-join : ์ž๊ธฐ ์ž์‹ ๊ณผ ์กฐ์ธ ์ˆ˜ํ–‰.

Outer Join : ์กฐ์ธ ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜์ง€ ์•Š๋Š” ํ–‰๋“ค๋„ ๊ฒฐ๊ณผ์— ํฌํ•จ์‹œํ‚ด. - left, right

 

 

 

 

 

์˜ˆ์ œ

Outer Join

 

https://programmers.co.kr/learn/courses/30/lessons/59042

 

์ฝ”๋”ฉํ…Œ์ŠคํŠธ ์—ฐ์Šต - ์—†์–ด์ง„ ๊ธฐ๋ก ์ฐพ๊ธฐ

ANIMAL_INS ํ…Œ์ด๋ธ”์€ ๋™๋ฌผ ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜จ ๋™๋ฌผ์˜ ์ •๋ณด๋ฅผ ๋‹ด์€ ํ…Œ์ด๋ธ”์ž…๋‹ˆ๋‹ค. ANIMAL_INS ํ…Œ์ด๋ธ” ๊ตฌ์กฐ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์œผ๋ฉฐ, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE๋Š” ๊ฐ๊ฐ ๋™๋ฌผ์˜ ์•„์ด๋””

programmers.co.kr

 

 

 

 

 

์ž…์–‘์„ ๊ฐ„ ๊ธฐ๋ก์€ ์žˆ๋Š”๋ฐ, ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜จ ๊ธฐ๋ก์ด ์—†๋Š” ๋™๋ฌผ์˜ ID์™€ ์ด๋ฆ„์„ ID ์ˆœ์œผ๋กœ ์กฐํšŒํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”.

 

-> ์ง‘ํ•ฉ ๊ฐœ๋…์œผ๋กœ ์ƒ๊ฐํ•ด์„œ B - A๋ฅผ ์ƒ๊ฐํ•˜๋ฉด ๋œ๋‹ค! ๊ทธ๋ž˜์„œ ๋” ํฐ ์ง‘ํ•ฉ์ธ B์ชฝ์œผ๋กœ(์˜ค๋ฅธ์ชฝ์œผ๋กœ) ์กฐ์ธํ•ด์ฃผ๋ฉด ๋œ๋‹ค.

 

 

 

 

 

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

 

 

 

 

 

https://programmers.co.kr/learn/courses/30/lessons/59044

 

์ฝ”๋”ฉํ…Œ์ŠคํŠธ ์—ฐ์Šต - ์˜ค๋žœ ๊ธฐ๊ฐ„ ๋ณดํ˜ธํ•œ ๋™๋ฌผ(1)

ANIMAL_INS ํ…Œ์ด๋ธ”์€ ๋™๋ฌผ ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜จ ๋™๋ฌผ์˜ ์ •๋ณด๋ฅผ ๋‹ด์€ ํ…Œ์ด๋ธ”์ž…๋‹ˆ๋‹ค. ANIMAL_INS ํ…Œ์ด๋ธ” ๊ตฌ์กฐ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์œผ๋ฉฐ, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE๋Š” ๊ฐ๊ฐ ๋™๋ฌผ์˜ ์•„์ด๋””

programmers.co.kr

 

 

 

 

 

์•„์ง ์ž…์–‘์„ ๋ชป ๊ฐ„ ๋™๋ฌผ ์ค‘, ๊ฐ€์žฅ ์˜ค๋ž˜ ๋ณดํ˜ธ์†Œ์— ์žˆ์—ˆ๋˜ ๋™๋ฌผ 3๋งˆ๋ฆฌ์˜ ์ด๋ฆ„๊ณผ ๋ณดํ˜ธ ์‹œ์ž‘์ผ์„ ์กฐํšŒํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”. ์ด๋•Œ ๊ฒฐ๊ณผ๋Š” ๋ณดํ˜ธ ์‹œ์ž‘์ผ ์ˆœ์œผ๋กœ ์กฐํšŒํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

 

-> ์ง‘ํ•ฉ ๊ฐœ๋…์œผ๋กœ ์ƒ๊ฐํ•ด์„œ A - B๋ฅผ ์ƒ๊ฐํ•˜๋ฉด ๋œ๋‹ค! ๊ทธ๋ž˜์„œ ๋” ํฐ ์ง‘ํ•ฉ์ธ A์ชฝ์œผ๋กœ(์™ผ์ชฝ์œผ๋กœ) ์กฐ์ธํ•ด์ฃผ๋ฉด ๋œ๋‹ค.

 

 

 

 

 

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

 

 

 

 

 

https://programmers.co.kr/learn/courses/30/lessons/59411

 

์ฝ”๋”ฉํ…Œ์ŠคํŠธ ์—ฐ์Šต - ์˜ค๋žœ ๊ธฐ๊ฐ„ ๋ณดํ˜ธํ•œ ๋™๋ฌผ(2)

ANIMAL_INS ํ…Œ์ด๋ธ”์€ ๋™๋ฌผ ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜จ ๋™๋ฌผ์˜ ์ •๋ณด๋ฅผ ๋‹ด์€ ํ…Œ์ด๋ธ”์ž…๋‹ˆ๋‹ค. ANIMAL_INS ํ…Œ์ด๋ธ” ๊ตฌ์กฐ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์œผ๋ฉฐ, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE๋Š” ๊ฐ๊ฐ ๋™๋ฌผ์˜ ์•„์ด๋””

programmers.co.kr

 

 

 

 

 

์ž…์–‘์„ ๊ฐ„ ๋™๋ฌผ ์ค‘, ๋ณดํ˜ธ ๊ธฐ๊ฐ„์ด ๊ฐ€์žฅ ๊ธธ์—ˆ๋˜ ๋™๋ฌผ ๋‘ ๋งˆ๋ฆฌ์˜ ์•„์ด๋””์™€ ์ด๋ฆ„์„ ์กฐํšŒํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”. ์ด๋•Œ ๊ฒฐ๊ณผ๋Š” ๋ณดํ˜ธ ๊ธฐ๊ฐ„์ด ๊ธด ์ˆœ์œผ๋กœ ์กฐํšŒํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

 

 

 

 

 

SELECT I.ANIMAL_ID, I.NAME
FROM ANIMAL_INS I LEFT JOIN ANIMAL_OUTS O
    ON I.ANIMAL_ID = O.ANIMAL_ID
ORDER BY I.DATETIME - O.DATETIME
FETCH FIRST 2 ROWS ONLY

 

์ฐธ๊ณ  : https://programmers.co.kr/questions/23807

 

'DB > SQL' ์นดํ…Œ๊ณ ๋ฆฌ์˜ ๋‹ค๋ฅธ ๊ธ€

SQL - UPPER(), ROWER()  (0) 2022.06.17
SQL - ์—ฐ์‚ฐ์ž IN  (0) 2022.06.17
SQL - NVL()  (0) 2022.06.14
SQL - HAVING  (0) 2022.06.13
SQL - ์ง‘๊ณ„ ํ•จ์ˆ˜  (0) 2022.06.13
giraffe_