Amenable 2023. 4. 18. 00:09

1. ๊ฐœ๋… ๐Ÿช

  ๋ณดํ†ต ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋Š” ํ•˜๋‚˜์˜ ํ…Œ์ด๋ธ”์— ๋งŽ์€ ๋ฐ์ดํ„ฐ๋ฅผ ์ €์žฅํ•˜์ง€ ์•Š๊ณ  ๋ช‡ ๊ฐœ์˜ ํ…Œ์ด๋ธ”๋กœ ๋‚˜๋ˆ„์–ด ์ €์žฅํ•œ๋‹ค. ์ด์ฒ˜๋Ÿผ ์—ฌ๋Ÿฌ ๊ฐœ๋กœ ๋‚˜๋ˆ„์–ด์ง„ ๋ฐ์ดํ„ฐ๋ฅผ ํ•˜๋‚˜๋กœ ๋ฌถ์–ด ๊ฒฐ๊ณผ๋ฅผ ๋‚ด๊ธฐ ์œ„ํ•˜์—ฌ JOIN์„ ์‚ฌ์šฉํ•œ๋‹ค.

  JOIN์€ ํฌ๊ฒŒ 

  • INNER JOIN
  • OUTER JOIN
  • SELF JOIN

์œผ๋กœ ๋‚˜๋ˆ„์–ด์ง„๋‹ค. ํ•˜๋‚˜์”ฉ ์‚ดํŽด๋ณด๋„๋ก ํ•˜์ž.

 

2. INNER JOIN ๐Ÿซ

  ๊ฐ€์žฅ ์ผ๋ฐ˜์ ์ธ JOIN์˜ ์ข…๋ฅ˜์ด๋ฉฐ ๊ต์ง‘ํ•ฉ์„ ํ‘œํ˜„ํ•œ๋‹ค.

  INNER JOIN์€ ์–ด๋А ํ…Œ์ด๋ธ”์„ ๋จผ์ € ์ฝ๋“  ์กฐ์ธ ๊ด€๊ณ„์— ๋ถ€ํ•ฉ๋˜๋Š” ๋ ˆ์ฝ”๋“œ๋ฅผ ๋ชจ๋‘ ๊ฐ€์ง€๊ฒŒ ๋œ๋‹ค. ์กฐ์ธ์˜ ์ˆœ์„œ๊ฐ€ ์ค‘์š”ํ•˜์ง€ ์•Š๋‹ค. INNER JOIN์€ ์•„๋ž˜์˜ 4๊ฐ€์ง€ ๋ฐฉ๋ฒ•์œผ๋กœ ํ‘œํ˜„ํ•  ์ˆ˜ ์žˆ๋‹ค.

1. ON ์‚ฌ์šฉ

SELECT a.a1, b.b1
FROM a_table a 
INNER JOIN b_table b ON a.a3 = b.b3

2. USING ์‚ฌ์šฉ

SELECT a.a1, b.b1
FROM a_table a 
INNER JOIN b_table b USING (๊ณตํ†ตcolumn)

  ๊ผญ ๊ด„ํ˜ธ๋ฅผ ์‚ฌ์šฉํ•ด์•ผ ํ•œ๋‹ค. ๊ทธ๋ฆฌ๊ณ  USING์•ˆ์— ๊ณตํ†ตcolumn์„ ๋ช…์‹œํ•  ๋•Œ a.๊ณตํ†ตcolumn๊ณผ ๊ฐ™์ด alias๋ฅผ ๋ช…์‹œํ•˜๋ฉด ์•ˆ ๋œ๋‹ค.

3. NATURAL JOIN ์‚ฌ์šฉ

SELECT a.a1, b.b1
FROM a_table a 
NATURAL JOIN b_table b

  ์ด๋•Œ, ๊ณตํ†ตcolumn์ด ํ•˜๋‚˜์—ฌ์•ผ๋งŒ ํ•œ๋‹ค. ๋‘ ๊ฐœ ์ด์ƒ์ด๋ฉด ์•ˆ๋œ๋‹ค.

4. ์ฝค๋งˆ(,) ์‚ฌ์šฉ

SELECT a.a1, b.b1
FROM a_table a, b_table b
WHERE a.a3 = b.b3

 

3. OUTER JOIN ๐Ÿฆฅ

  OUTER JOIN์€ ๋‘ ํ…Œ์ด๋ธ”์ด ํ•ฉ์ณ์งˆ ๋•Œ ๊ธฐ์ค€(์™ผ์ชฝ ํ…Œ์ด๋ธ” ๋˜๋Š” ์˜ค๋ฅธ์ชฝ ํ…Œ์ด๋ธ”)์— ๋”ฐ๋ผ์„œ ๊ธฐ์ค€ ํ…Œ์ด๋ธ”์ด ๋ชจ๋‘ ์ถœ๋ ฅ๋˜๋Š” ๊ฒƒ์ด๋‹ค. INNER JOIN๊ณผ ๋‹ฌ๋ฆฌ OUTER JOIN์€ ์ˆœ์„œ๊ฐ€ ์ค‘์š”ํ•˜๋‹ค. ์–ด๋–ค ์ˆœ์„œ๋กœ ํ…Œ์ด๋ธ”์„ ์กฐ์ธํ• ์ง€์— ๋”ฐ๋ผ ๊ฒฐ๊ณผ ํ…Œ์ด๋ธ”์— ์กฐํšŒ๋˜๋Š” ํ–‰์˜ ๊ฐœ์ˆ˜์™€ ๊ตฌ์„ฑ์ด ๋‹ฌ๋ผ์ง€๊ธฐ ๋•Œ๋ฌธ์ด๋‹ค.

  ์–ด๋А ํ•œ์ชฝ ํ…Œ์ด๋ธ”์— ํ•ด๋‹นํ•˜๋Š” ๋ฐ์ดํ„ฐ๊ฐ€ ์กด์žฌํ•˜๋Š”๋ฐ ๋‹ค๋ฅธ ์ชฝ ํ…Œ์ด๋ธ”์—๋Š” ๋ฐ์ดํ„ฐ๊ฐ€ ์กด์žฌํ•˜์ง€ ์•Š์„ ๊ฒฝ์šฐ ๊ทธ ๋ฐ์ดํ„ฐ๊ฐ€ ๊ฒ€์ƒ‰๋˜์ง€ ์•Š๋Š” ๋ฌธ์ œ๋ฅผ ํ•ด๊ฒฐํ•˜๊ธฐ ์œ„ํ•ด ์‚ฌ์šฉ๋œ๋‹ค. OUTER JOIN์€ ์•„๋ž˜์™€ ๊ฐ™์ด 3๊ฐ€์ง€ ์ข…๋ฅ˜๊ฐ€ ์žˆ๋‹ค. (ON๊ณผ USING์ด ๋‘˜ ๋‹ค ์‚ฌ์šฉ๊ฐ€๋Šฅํ•˜๋ฏ€๋กœ ๋ฒˆ๊ฐˆ์•„ ๊ฐ€๋ฉด์„œ ์‚ฌ์šฉํ•˜๊ฒ ๋‹ค.)

1. LEFT OUTER JOIN

SELECT a.a1, b.b1
FROM a_table a 
LEFT OUTER JOIN b_table b ON a.a3 = b.b3

2. RIGHT OUTER JOIN

SELECT a.a1, b.b1
FROM a_table a 
RIGHT OUTER JOIN b_table b USING (๊ณตํ†ตcolumn)

3. FULL OUTER JOIN

SELECT a.a1, b.b1
FROM a_table a 
FULL OUTER JOIN b_table b ON a.a3 = b.b3

  ํ˜•์‹์€ ์œ„์™€ ๊ฐ™์ง€๋งŒ ๋Œ€๋ถ€๋ถ„์˜ DB๋Š” FULL OUTER JOIN์„ ์ง€์›ํ•˜์ง€ ์•Š๋Š”๋‹ค. ๊ทธ๋Ÿฌ๋ฏ€๋กœ UNION์„ ์‚ฌ์šฉํ•˜์—ฌ ์•„๋ž˜์™€ ๊ฐ™์ด ๊ตฌํ˜„ํ•ด์•ผ ํ•œ๋‹ค.

(SELECT a.a1, b.b1
FROM a_table a 
LEFT OUTER JOIN b_table b ON a.a3 = b.b3)
UNION
(SELECT a.a1, b.b1
FROM a_table a 
RIGHT OUTER JOIN b_table b ON a.a3 = b.b3)

  UNION์€ DISTINCT๋ฅผ ์ž๋™์œผ๋กœ ํฌํ•จํ•˜๊ณ  ์žˆ๊ธฐ ๋•Œ๋ฌธ์— ์ž๋™์œผ๋กœ ์ค‘๋ณต์„ ์ œ๊ฑฐํ•ด ์ค€๋‹ค.

 

4. SELF JOIN ๐Ÿฆ…

  SELF JOIN์€ ์ž๊ธฐ ์ž์‹ ๊ณผ JOIN์„ ํ•˜๋Š” ๊ฒƒ์ด๋‹ค. ์‚ฌ์› ํ…Œ์ด๋ธ”์—์„œ ์‚ฌ์›์˜ ์ด๋ฆ„๊ณผ ์ง์† ์ƒ์‚ฌ์˜ ์ด๋ฆ„์„ ์–ป๋Š” ๊ฒฝ์šฐ์—์„œ ์‚ฌ์šฉ๋œ๋‹ค.

SELECT e.name AS ์‚ฌ์›, m.name AS ์ง์† ์ƒ์‚ฌ
FROM employees e 
INNER JOIN employees m ON e.manager_id = m.employee_id

  ON ์กฐ๊ฑด์—์„œ ํ™•์ธํ•  ์ˆ˜ ์žˆ๋“ฏ์ด ๋น„๋ก ์ปฌ๋Ÿผ ์ด๋ฆ„์ด ๊ฐ™๋”๋ผ๋„ ๋‹ค๋ฅธ ์ปฌ๋Ÿผ์„ ์‚ฌ์šฉํ•˜๋Š” ๊ฒฝ์šฐ์—๋Š” USING์„ ์‚ฌ์šฉํ•˜์ง€ ์•Š๋„๋ก ์ฃผ์˜ํ•˜์ž.

 

ํ•ด๋‹น ๊ธ€์€
Inpa ๋‹˜์˜ '[MYSQL] ํ…Œ์ด๋ธ” ์กฐ์ธ(JOIN) - ๊ทธ๋ฆผ์œผ๋กœ ์•Œ๊ธฐ ์‰ฝ๊ฒŒ ์ •๋ฆฌ',
์•„์‚ฌ์ด ์•„์ธ ์‹œ ๋‹˜์˜ 'SQL ์ฒซ๊ฑธ์Œ'
์„ ์ฐธ๊ณ ํ•˜์˜€์Šต๋‹ˆ๋‹ค.