SELECT (ํ
์ด๋ธ ์กฐํ)
1. ํ
์ด๋ธ ๊ฒฐํฉ
๐ก๋ ๊ฐ ์ด์์ ํ
์ด๋ธ์ ์๋ก ์ฐ๊ฒฐํ์ฌ ๋ฐ์ดํฐ๋ฅผ ์กฐํํ ๋ ์ฌ์ฉํ๋ ๋ฐฉ๋ฒ
[table1] test.information
no | name | age |
---|---|---|
1 | Gildong | 16 |
2 | Kongjwi | 17 |
3 | Michael | 14 |
4 | Ariel | 16 |
[table2] test.score
no | name | subject | score |
---|---|---|---|
1 | Ariel | Math | 95 |
2 | Gildong | Korean | 40 |
1-1. Left join
๐ก๊ธฐ์ค ํ
์ด๋ธ๊ณผ ์ผ์นํ๋ ๊ฐ๋ง join ํ
์ด๋ธ์์ ๊ฒฐํฉ๋๋ฉฐ ์๋ ๊ฐ์ null ๊ฐ์ผ๋ก ์ฑ์์ ธ ๊ฒฐํฉ
-- Left join
-- information(A)์ score(B) ์ ๋ณด ๊ฒฐํฉ(key: name)
select
t1.*
, t2.subject
, t2.score
from test.information as t1
left join test.score as t2 on t1.name = t2.name
[๊ฒฐ๊ณผ] Left join
no | name | age | subject | score |
---|---|---|---|---|
1 | Gildong | 16 | Korean | 40 |
2 | Kongjwi | 17 | ||
3 | Michael | 14 | ||
4 | Ariel | 16 | Math | 95 |
1-2. Right join
๐กjoin ํ
์ด๋ธ๊ณผ ์ผ์นํ๋ ๊ฐ๋ง ๊ธฐ๋ณธ ํ
์ด๋ธ์์ ๊ฒฐํฉ๋๋ฉฐ ์๋ ๊ฐ์ null ๊ฐ์ผ๋ก ์ฑ์์ ธ ๊ฒฐํฉ
-- Right join
-- score(B)์ information(A) ์ ๋ณด ๊ฒฐํฉ(key: name)
select
t2.*
, t2.age
from test.information as t1
right join test.score as t2 on t1.name = t2.name
[๊ฒฐ๊ณผ] Right join
no | name | subject | score | age |
---|---|---|---|---|
2 | Gildong | Korean | 40 | 16 |
1 | Ariel | Math | 95 | 16 |
1-3. Inner join
๐ก๊ธฐ์ค ํ
์ด๋ธ๊ณผ join ํ
์ด๋ธ ๋ชจ๋ ๋ฐ์ดํฐ๊ฐ ์กด์ฌํ๋ ๊ฐ๋ง ๊ฒฐํฉ
-- Inner join
-- information(A)์ score(B)์ ๊ต์งํฉ(key: name)
select
t1.*
, t2.subject
, t2.score
from test.information as t1
inner join test.score as t2 on t1.name = t2.name
[๊ฒฐ๊ณผ] Inner join
no | name | age | subject | score |
---|---|---|---|---|
1 | Gildong | 16 | Korean | 40 |
4 | Ariel | 16 | Math | 95 |
1-4. Full outer join
๐ก๊ฒฐํฉํ๋ ค๊ณ ํ๋ ํ
์ด๋ธ๋ค์ ์๋ ๊ฐ ๋ชจ๋ ๊ฒฐํฉ
-- Full outer join
-- information(A)์ score(B)์ ํฉ์งํฉ(key: name)
select
t1.*
, t2.subject
, t2.score
from test.information as t1
full outer join test.score as t2 on t1.name = t2.name
[๊ฒฐ๊ณผ] Full outer join
no | name | age | subject | score |
---|---|---|---|---|
1 | Gildong | 16 | Korean | 40 |
2 | Kongjwi | 17 | ||
3 | Michael | 14 | ||
4 | Ariel | 16 | Math | 95 |
1-5. Union & Union all
๐ก๋ ๊ฐ ์ด์์ ํ
์ด๋ธ์ ์๋ก ์ฐ๊ฒฐํ์ฌ ๋ฐ์ดํฐ๋ฅผ ์กฐํํ ๋ ์ฌ์ฉํ๋ ๋ฐฉ๋ฒ
๐กUnion์ ์ค๋ณต ์ ๊ฑฐ ํ ๊ฒฐํฉ, Union all์ ์ค๋ณต ํ์ฉ ํ ๊ฒฐํฉํ๋ ๋ฐฉ๋ฒ
๐ก๊ฒฐํฉ ์, ๋ชจ๋ ์ฟผ๋ฆฌ๋ ์ปฌ๋ผ์ ๊ฐฏ์์ ๋ฐ์ดํฐ ํ์
์ด ์ผ์นํด์ผํจ
-- Union
-- information๋ฅผ Union ๊ฒฐํฉ
select *
from test.information
union
select *
from test.information
[๊ฒฐ๊ณผ] Union (์ค๋ณต์ ๊ฑฐ)
no | name | age |
---|---|---|
1 | Gildong | 16 |
2 | Kongjwi | 17 |
3 | Michael | 14 |
4 | Ariel | 16 |
-- Union all
-- information๋ฅผ Union all ๊ฒฐํฉ
select *
from test.information
union all
select *
from test.information
[๊ฒฐ๊ณผ] Union all (์ค๋ณตํ์ฉ)
no | name | age |
---|---|---|
1 | Gildong | 16 |
2 | Kongjwi | 17 |
3 | Michael | 14 |
4 | Ariel | 16 |
1 | Gildong | 16 |
2 | Kongjwi | 17 |
3 | Michael | 14 |
4 | Ariel | 16 |