[Database] MySQL - Join

MySQL - Join

MySQL์—์„œ ์‚ฌ์šฉํ•˜๋Š” Join(Inner Join, Equi Join, Outer Join)์„ ์•Œ์•„๋ณด์ž.

Tables

์•„๋ž˜์™€ ๊ฐ™์€ ๋‘ ๊ฐœ์˜ ํ…Œ์ด๋ธ”๋กœ Join์„ ์•Œ์•„๋ณด์ž.

1
2

Inner Join

ํ…Œ์ด๋ธ”์ด ๋‘ ๊ฐœ ์žˆ์„ ๋•Œ, ๋‘ ์ง‘ํ•ฉ์˜ ๊ต์ง‘ํ•ฉ ์˜์—ญ์„ ์ถœ๋ ฅํ•˜๋Š” ๊ฒƒ์ด Inner Join์ด๋‹ค.

๋‹ค์Œ๊ณผ ๊ฐ™์ด ์ž…๋ ฅํ•ด๋ณด์ž

select * from students s join teachers t on s.tid = t.tid;

3

students์™€ teachers ํ…Œ์ด๋ธ”์—์„œ tid๊ฐ€ ๋™์ผํ•œ(๊ต์ง‘ํ•ฉ ์˜์—ญ) ํŠœํ”Œ์„ ์ถœ๋ ฅํ•œ๋‹ค.

Equi Join

Equi Join์€ ๋‘ ํ…Œ์ด๋ธ” ์‚ฌ์ด์—์„œ attribute์˜ ๊ฐ’์ด ๊ฐ™์€ ํŠœํ”Œ์„ ์ถœ๋ ฅํ•˜๋Š” Join์ด๊ณ , ๊ฐ™์ง€ ์•Š์€ ๊ฑธ ์ถœ๋ ฅํ•˜๋Š” ๊ฑด Non-Equi Join์ด๋‹ค.

select * from students s, teachers t where s.tid = t.tid;

4

where์˜ ์กฐ๊ฑด์œผ๋กœ equal์„ ์ด์šฉํ•œ equi join์ด๋‹ค.

Non-Equi Join

select * from students s, teachers t where s.tid != t.tid;

5
non-equi join์ด๋‹ค.

Outer Join

Outer Join์€ Left Join๊ณผ Right Join์œผ๋กœ ๋‚˜๋ˆ ์ง„๋‹ค.
Left Join์€ ๋‘ ์ง‘ํ•ฉ์˜ ๊ต์ง‘ํ•ฉ ์˜์—ญ๊ณผ ์™ผ์ชฝ ๊ทธ๋ž˜ํ”„์˜ ์ผ์น˜ํ•˜์ง€ ์•Š๋Š” ์˜์—ญ๋„ ์ถœ๋ ฅํ•œ๋‹ค.
Right Join์€ ๋‘ ์ง‘ํ•ฉ์˜ ๊ต์ง‘ํ•ฉ ์˜์—ญ๊ณผ ์˜ค๋ฅธ์ชฝ ๊ทธ๋ž˜ํ”„์˜ ์ผ์น˜ํ•˜์ง€ ์•Š๋Š” ์˜์—ญ๋„ ์ถœ๋ ฅํ•œ๋‹ค.

Left Join

select * from students s left join teachers t on s.tid = t.tid;

6

Right Join

select * from students s right join teachers t on s.tid = t.tid;

7

right join์—์„œ ์œ„์™€ ๊ฐ™์ด tid ๊ฐ’์ด 2์ธ students๋Š” ์—†๊ธฐ ๋•Œ๋ฌธ์— null ๊ฐ’์œผ๋กœ ์ถœ๋ ฅ๋œ๋‹ค.