[MySQL] Join(inner, outer) & EER diagram

sua_ahnยท2023๋…„ 1์›” 9์ผ
0

DBMS

๋ชฉ๋ก ๋ณด๊ธฐ
3/11
post-thumbnail

Join

: ๋‘˜ ์ด์ƒ์˜ ํ…Œ์ด๋ธ”์„ ์—ฐ๊ฒฐ(๊ฒฐํ•ฉ)

๐Ÿฃ Inner join

: ๊ฒฐํ•ฉ ์กฐ๊ฑด์„ ๋งŒ์กฑํ•œ ๋ฐ์ดํ„ฐ๋“ค๋งŒ ํ…Œ์ด๋ธ”๋กœ ๊ฒฐํ•ฉ

  • MySQL ๋ฌธ๋ฒ•
select ์กฐํšŒ ์ปฌ๋Ÿผ
from ํ…Œ์ด๋ธ”1, ํ…Œ์ด๋ธ”2
where ํ…Œ์ด๋ธ”1.์ปฌ๋Ÿผ = ํ…Œ์ด๋ธ”2.์ปฌ๋Ÿผ; -- ๊ฒฐํ•ฉ ์กฐ๊ฑด
  • ANSI ๋ฌธ๋ฒ• (American National Standards Institute์˜ ํ‘œ์ค€ SQL๋ฌธ)
select ์กฐํšŒ ์ปฌ๋Ÿผ
from ํ…Œ์ด๋ธ”1 inner join ํ…Œ์ด๋ธ”2
	on ๊ฒฐํ•ฉ ์กฐ๊ฑด;

ย  ย  โ†’ join = inner join = cross join

Cross join

: ์—ฐ๊ฒฐํ•˜๋Š” ํ…Œ์ด๋ธ” ๋ฐ์ดํ„ฐ์˜ ๋ชจ๋“  ์กฐํ•ฉ

select ์กฐํšŒ ์ปฌ๋Ÿผ
from ํ…Œ์ด๋ธ”1 cross join ํ…Œ์ด๋ธ”2;

Self join

: ๋™์ผํ•œ ํ…Œ์ด๋ธ”์„ ์—ฐ๊ฒฐ

select ์กฐํšŒ ์ปฌ๋Ÿผ
from ํ…Œ์ด๋ธ”1 a, ํ…Œ์ด๋ธ”1 b
where ๊ฒฐํ•ฉ ์กฐ๊ฑด;



๐Ÿฅ Outer join

: ํ•œ์ชฝ ํ…Œ์ด๋ธ”์—๋งŒ ์žˆ๋Š” ๋ฐ์ดํ„ฐ๋„ ๊ฒฐํ•ฉ

Left outer join

select ์กฐํšŒ ์ปฌ๋Ÿผ
from ํ…Œ์ด๋ธ”1 left [outer] join ํ…Œ์ด๋ธ”2
	on ๊ฒฐํ•ฉ ์กฐ๊ฑด;

Right outer join

select ์กฐํšŒ ์ปฌ๋Ÿผ
from ํ…Œ์ด๋ธ”1 right [outer] join ํ…Œ์ด๋ธ”2
	on ๊ฒฐํ•ฉ ์กฐ๊ฑด;

Full outer join

select ์กฐํšŒ ์ปฌ๋Ÿผ
from ํ…Œ์ด๋ธ”1 left outer join ํ…Œ์ด๋ธ”2
	on ๊ฒฐํ•ฉ ์กฐ๊ฑด;
union
select ์กฐํšŒ ์ปฌ๋Ÿผ
from ํ…Œ์ด๋ธ”1 right outer join ํ…Œ์ด๋ธ”2
	on ๊ฒฐํ•ฉ ์กฐ๊ฑด;
    
    
    
    
    

DB Modeling

MySQL์—์„œ๋Š” EER diagram์„ ํ†ตํ•ด ๋ฐ์ดํ„ฐ๋ฅผ ์‹œ๊ฐ์ ์œผ๋กœ ๊ทธ๋ ค๋ณผ ์ˆ˜ ์žˆ์Œ

Enhanced Entity-Relationship(EER) diagram ์ƒ์„ฑ ๋ฐฉ๋ฒ•

๋ฉ”๋‰ด Database โ†’ Reverse Engineer โ†’ "Stored Connection:" ์„ ํƒ โ†’ Next

โ†’ Password ์ž…๋ ฅ โ†’ Next โ†’ Next โ†’ Table Objects๋งŒ ์„ ํƒ โ†’ Execute

โ†’ Next โ†’ Finish

โ‡’ EER Diagram ์ฐฝ์„ ํ†ตํ•ด table๊ณผ key, join ํ™•์ธ ๊ฐ€๋Šฅ

profile
ํ•ด๋ณด์ž๊ตฌ

0๊ฐœ์˜ ๋Œ“๊ธ€