SQL - JOIN

jaeeeun93ยท2021๋…„ 3์›” 5์ผ
0

Database

๋ชฉ๋ก ๋ณด๊ธฐ
2/2

โŒจ [Database SQL] JOIN

๐Ÿ“Œ JOIN์ด๋ž€?

๋‘ ๊ฐœ ์ด์ƒ์˜ ํ…Œ์ด๋ธ”์ด๋‚˜ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์—ฐ๊ฒฐํ•˜์—ฌ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฒ€์ƒ‰ํ•˜๋Š” ๋ฐฉ๋ฒ•

ํ…Œ์ด๋ธ”์„ ์—ฐ๊ฒฐํ•˜๋ ค๋ฉด, ์ ์–ด๋„ ํ•˜๋‚˜์˜ ์นผ๋Ÿผ์„ ์„œ๋กœ ๊ณต์œ ํ•˜๊ณ  ์žˆ์–ด์•ผ ํ•˜๋ฏ€๋กœ ์ด๋ฅผ ์ด์šฉํ•˜์—ฌ ๋ฐ์ดํ„ฐ ๊ฒ€์ƒ‰์— ํ™œ์šฉํ•œ๋‹ค.

๐Ÿ“Œ JOIN ์ข…๋ฅ˜

  • INNER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • SELF JOIN

๐Ÿ“Œ INNER JOIN


๊ต์ง‘ํ•ฉ์œผ๋กœ, ๊ธฐ์ค€ ํ…Œ์ด๋ธ”๊ณผ join ํ…Œ์ด๋ธ”์˜ ์ค‘๋ณต๋œ ๊ฐ’์„ ๋ณด์—ฌ์ค€๋‹ค.

SELECT
A.NAME, B.AGE
FROM EX_TABLE A
INNER JOIN JOIN_TABLE B ON A.NO_EMP = B.NO_EMP

๐Ÿ“Œ LEFT OUTER JOIN


๊ธฐ์ค€ ํ…Œ์ด๋ธ”๊ฐ’๊ณผ JOIN ํ…Œ์ด๋ธ”๊ณผ ์ค‘๋ณต๋œ ๊ฐ’์„ ๋ณด์—ฌ์ค€๋‹ค.
์™ผ์ชฝํ…Œ์ด๋ธ” ๊ธฐ์ค€์œผ๋กœ JOIN์„ ํ•œ๋‹ค๊ณ  ์ƒ๊ฐํ•˜๋ฉด ํŽธํ•˜๋‹ค.

SELECT
A.NAME, B.AGE
FROM EX_TABLE A
LEFT OUTER JOIN JOIN_TABLE B ON A.NO_EMP = B.NO_EMP

๐Ÿ“Œ RIGHT OUTER JOIN


LEFT OUTER JOIN๊ณผ๋Š” ๋ฐ˜๋Œ€๋กœ ์˜ค๋ฅธ์ชฝ ํ…Œ์ด๋ธ” ๊ธฐ์ค€์œผ๋กœ JOINํ•˜๋Š” ๊ฒƒ์ด๋‹ค.

SELECT
A.NAME, B.AGE
FROM EX_TABLE A
RIGHT OUTER JOIN JOIN_TABLE B ON A.NO_EMP = B.NO_EMP

๐Ÿ“Œ FULL OUTER JOIN


ํ•ฉ์ง‘ํ•ฉ์„ ๋งํ•œ๋‹ค. A์™€ Bํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ๋ฐ์ดํ„ฐ๊ฐ€ ๊ฒ€์ƒ‰๋œ๋‹ค.

SELECT
A.NAME, B.AGE
FROM EX_TABLE A
FULL OUTER JOIN JOIN_TABLE B ON A.NO_EMP = B.NO_EMP

๐Ÿ“Œ CROSS JOIN


๋ชจ๋“  ๊ฒฝ์šฐ์˜ ์ˆ˜๋ฅผ ์ „๋ถ€ ํ‘œํ˜„ํ•ด์ฃผ๋Š” ๋ฐฉ์‹์ด๋‹ค.
A๊ฐ€ 3๊ฐœ, B๊ฐ€ 4๊ฐœ๋ฉด ์ด 3*4 = 12๊ฐœ์˜ ๋ฐ์ดํ„ฐ๊ฐ€ ๊ฒ€์ƒ‰๋œ๋‹ค.

SELECT
A.NAME, B.AGE
FROM EX_TABLE A
CROSS JOIN JOIN_TABLE B

๐Ÿ“Œ SELF JOIN


์ž๊ธฐ์ž์‹ ๊ณผ ์ž๊ธฐ์ž์‹ ์„ ์กฐ์ธํ•˜๋Š” ๊ฒƒ์ด๋‹ค.
ํ•˜๋‚˜์˜ ํ…Œ์ด๋ธ”์„ ์—ฌ๋Ÿฌ๋ฒˆ ๋ณต์‚ฌํ•ด์„œ ์กฐ์ธํ•œ๋‹ค๊ณ  ์ƒ๊ฐํ•˜๋ฉด ํŽธํ•˜๋‹ค.
์ž์‹ ์ด ๊ฐ–๊ณ  ์žˆ๋Š” ์นผ๋Ÿผ์„ ๋‹ค์–‘ํ•˜๊ฒŒ ๋ณ€ํ˜•์‹œ์ผœ ํ™œ์šฉํ•  ๋•Œ ์ž์ฃผ ์‚ฌ์šฉํ•œ๋‹ค.

๋”ฐ๋ผ์„œ FROM ์ ˆ์— ๋™์ผ ํ…Œ์ด๋ธ”์ด ๋‘ ๋ฒˆ ์ด์ƒ ๋‚˜ํƒ€๋‚œ๋‹ค.
๋™์ผ ํ…Œ์ด๋ธ” ์‚ฌ์ด์˜ JOIN์„ ์ˆ˜ํ–‰ํ•˜๋ฉด ํ…Œ์ด๋ธ”๊ณผ ์ปฌ๋Ÿผ ์ด๋ฆ„์ด ๋ชจ๋‘ ๋™์ผํ•˜๊ธฐ ๋•Œ๋ฌธ์— ์‹๋ณ„์„ ์œ„ํ•ด ๋ฐ˜๋“œ์‹œ ํ…Œ์ด๋ธ” ๋ณ„์นญ(Alias)์„ ์‚ฌ์šฉํ•ด์•ผ ํ•œ๋‹ค.
๊ทธ๋ฆฌ๊ณ  ์ปฌ๋Ÿผ์—๋„ ๋ชจ๋‘ ํ…Œ์ด๋ธ” ๋ณ„์นญ์„ ์‚ฌ์šฉํ•ด์„œ ์–ด๋Š ํ…Œ์ด๋ธ”์˜ ์ปฌ๋Ÿผ์ธ์ง€ ์‹๋ณ„ํ•ด์ค˜์•ผ ํ•œ๋‹ค.

SELECT
ALIAS๋ช…1.์ปฌ๋Ÿผ๋ช…,ALIAS๋ช…2.์ปฌ๋Ÿผ๋ช…
FROM ํ…Œ์ด๋ธ”1 ALIAS๋ช…1, ํ…Œ์ด๋ธ”2 ALIAS๋ช…2
WHERE ALIAS๋ช…1.์ปฌ๋Ÿผ๋ช…2 = ALIAS๋ช…2.์ปฌ๋Ÿผ๋ช…1;

๐Ÿ“Œ Reference & Additional Resources

https://github.com/gyoogle/tech-interview-for-developer/blob/master/Computer%20Science/Database/%5BDatabase%20SQL%5D%20JOIN.md

https://tychejin.tistory.com/108

profile
๊ธฐ์–ต๋ณด๋‹จ ๊ธฐ๋ก์„

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