20230511 TIL - SQL JOIN

ohyujeongยท2023๋…„ 5์›” 11์ผ
0

TIL

๋ชฉ๋ก ๋ณด๊ธฐ
20/27
post-thumbnail

๐Ÿ“– ์˜ค๋Š˜์˜ ํ•™์Šต

  • SQL : JOIN, Booleanํƒ€์ž…

JOIN

JOIN ์€ 2๊ฐœ ์ด์ƒ์˜ ํ…Œ์ด๋ธ”๋“ค์„ mergeํ•˜๋Š”๋ฐ ์‚ฌ์šฉ๋œ๋‹ค. ๋‹จ, ๊ณตํ†ต๋œ ํ•„๋“œ๋ฅผ ๊ฐ€์ง€๊ณ  ์žˆ์–ด์•ผ ํ•˜๊ณ  ์ด ๊ณตํ†ต๋œ ํ•„๋“œ๋ฅผ ์—ฐ๊ฒฐํ•จ์œผ๋กœ์จ JOIN ์ด ์ด๋ฃจ์–ด์ง„๋‹ค.

Star schema ๋กœ ๊ตฌ์„ฑ๋œ ํ…Œ์ด๋ธ”๋“ค๋กœ ๋ถ„์‚ฐ๋˜์–ด ์žˆ๋˜ ์ •๋ณด๋ฅผ ํ†ตํ•ฉํ•˜๋Š”๋ฐ ์‚ฌ์šฉ๋œ๋‹ค.

์™ผ์ชฝ ํ…Œ์ด๋ธ”์„ LEFT๋ผ๊ณ  ํ•˜๊ณ  ์˜ค๋ฅธ์ชฝ ํ…Œ์ด๋ธ”์„ RIGHT์ด๋ผ๊ณ  ํ•  ๋•Œ, JOIN์˜ ๊ฒฐ๊ณผ๋Š” ๋ฐฉ์‹์— ์ƒ๊ด€์—†์ด ์–‘์ชฝ์˜ ํ•„๋“œ๋ฅผ ๋ชจ๋‘ ๊ฐ€์ง„ ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์„ ๋งŒ๋“ค์–ด๋‚ด๊ฒŒ ๋œ๋‹ค.

์กฐ์ธ์˜ ๋ฐฉ์‹์— ๋”ฐ๋ผ ๋‹ค์Œ ๋‘ ๊ฐ€์ง€๊ฐ€ ๋‹ฌ๋ผ์ง„๋‹ค:
1. ์–ด๋–ค ๋ ˆ์ฝ”๋“œ๋“ค์ด ์„ ํƒ๋˜๋Š”์ง€?
2. ์–ด๋–ค ํ•„๋“œ๋“ค์ด ์ฑ„์›Œ์ง€๋Š”์ง€?

JOIN ๋ฌธ๋ฒ•

____ ์— JOIN ์ข…๋ฅ˜์ธ INNER, FULL, LEFT, RIGHT, CROSS ๊ฐ€ ๋“ค์–ด๊ฐˆ ์ˆ˜ ์žˆ๋‹ค.

SELECT A.*, B.*
FROM raw_data.table1 A
____ JOIN raw_data.table2 B ON A.key1 = B.key1 and A.key2 = B.key2
WHERE A.ts >= '2019-01-01';

๋‹ค์–‘ํ•œ ์ข…๋ฅ˜์˜ ์กฐ์ธ

1. INNER JOIN

์–‘์ชฝ ํ…Œ์ด๋ธ”์—์„œ ๋งค์น˜๊ฐ€ ๋˜๋Š” ๋ ˆ์ฝ”๋“œ๋“ค๋งŒ ๋ฆฌํ„ดํ•œ๋‹ค. ์ฆ‰, A์˜ key๊ฐ€ B์—๋„ ์žˆ๋Š” ํ•ญ๋ชฉ๋“ค๋งŒ ๋ฆฌํ„ด๋œ๋‹ค.

SELECT <select_list> 
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key

2. FULL JOIN (=FULL OUTER JOIN)


์™ผ์ชฝ ํ…Œ์ด๋ธ”๊ณผ ์˜ค๋ฅธ์ชฝ ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ๋ ˆ์ฝ”๋“œ๋“ค์„ ๋ฆฌํ„ดํ•œ๋‹ค.
๋งค์นญ์ด ๋˜๋Š” ๊ฒฝ์šฐ์—๋งŒ ์–‘์ชฝ ํ…Œ์ด๋ธ”๋“ค์˜ ๋ชจ๋“  ํ•„๋“œ๋“ค์ด ์ฑ„์›Œ์ง„ ์ƒํƒœ๋กœ ๋ฆฌํ„ด๋˜๊ณ  ์•„๋‹ˆ๋ผ๋ฉด NULL ๊ฐ’์œผ๋กœ ๋Œ€์ฒด๋˜์–ด ๋ฆฌํ„ด๋œ๋‹ค.

SELECT <select_list>
FROM Table_A A
FULL JOIN Table_B B
ON A.Key = B.Key

3. CROSS JOIN

์–‘์ชฝ ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ๊ฐ€๋Šฅํ•œ ์กฐํ•ฉ์„ ๋ฆฌํ„ดํ•œ๋‹ค. ๊ทธ๋ ‡๊ธฐ ๋•Œ๋ฌธ์— ON ์œผ๋กœ ๋งค์นญํ•  ํ•„๋“œ๋ฅผ ์„ค์ •ํ•ด ์ค„ ํ•„์š”๊ฐ€ ์—†๋‹ค.

SELECT <select_list>
FROM Table_A A
CROSS JOIN Table_B B

4. LEFT JOIN

์™ผ์ชฝ ํ…Œ์ด๋ธ”(Base๊ฐ€ ๋˜๋Š” ํ…Œ์ด๋ธ”)์˜ ๋ชจ๋“  ๋ ˆ์ฝ”๋“œ๋ฅผ ๋ฆฌํ„ดํ•œ๋‹ค. ์กฐ์ธํ•œ ์˜ค๋ฅธ์ชฝ ํ…Œ์ด๋ธ”์˜ ํ•„๋“œ๋Š” ์™ผ์ชฝ ๋ ˆ์ฝ”๋“œ์™€ ๋งค์นญ๋˜๋Š” ๊ฒฝ์šฐ์—๋งŒ ์ฑ„์›Œ์ง„ ์ƒํƒœ๋กœ ๋ฆฌํ„ด๋œ๋‹ค. (์•„๋‹ˆ๋ผ๋ฉด NULL ๋กœ ์ฑ„์›Œ์ง)

SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key

5. RIGHT JOIN


LEFT JOIN ๊ณผ ๊ฐ™์€ ๋ฐฉ์‹์œผ๋กœ ์ž‘๋™ํ•˜๋Š”๋ฐ Base ํ…Œ์ด๋ธ”๋งŒ ์˜ค๋ฅธ์ชฝ ํ…Œ์ด๋ธ”๋กœ ๋ณ€๊ฒฝ๋œ ๊ฒƒ์ด๋‹ค.

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key

6. SELF JOIN

๋™์ผํ•œ ํ…Œ์ด๋ธ”์„ 2๊ฐœ๋ฅผ alias๋ฅผ ๋‹ฌ๋ฆฌํ•˜์—ฌ ์กฐ์ธํ•œ๋‹ค.

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

JOIN์‹œ ๊ณ ๋ คํ•ด์•ผํ•  ์ 

  • ์ค‘๋ณต ๋ ˆ์ฝ”๋“œ๊ฐ€ ์—†๋Š”๊ฐ€?
  • Primary Key์˜ uniqueness๊ฐ€ ๋ณด์žฅ๋˜์—ˆ๋Š”๊ฐ€?
  • ์กฐ์ธํ•˜๋Š” ํ…Œ์ด๋ธ”๋“ค๊ฐ„์˜ ๊ด€๊ณ„๋ฅผ ๋ช…ํ™•ํ•˜๊ฒŒ ์ •์˜
    * One to one
    ์™„์ „ํ•œ one to one: user_session_channel & session_timestamp
    ํ•œ์ชฝ์ด ๋ถ€๋ถ„์ง‘ํ•ฉ์ด ๋˜๋Š” one to one: user_session_channel & session_transaction
    * One to many (order vs order_items)
    ์ด ๊ฒฝ์šฐ ์ค‘๋ณต์ด ๋” ํฐ ๋ฌธ์ œ๋จ -> ์ฆํญ!!
    * Many to one
    ๋ฐฉํ–ฅ๋งŒ ๋ฐ”๊พธ๋ฉด One to many๋กœ ๋ณด๋Š” ๊ฒƒ๊ณผ ์‚ฌ์‹ค์ƒ ๋™์ผ.
    * Many to many
    ์ด๋Ÿฐ ๊ฒฝ์šฐ๋Š” ๋งŽ์ง€ ์•Š์œผ๋ฉฐ ์ด๋Š” one to one์ด๋‚˜ one to many๋กœ ๋ฐ”๊พธ๋Š” ๊ฒƒ์ด ๊ฐ€๋Šฅํ•˜๋‹ค๋ฉด
    ๋ณ€ํ™˜ํ•˜์—ฌ ์กฐ์ธํ•˜๋Š” ๊ฒƒ์ด ๋œ ์œ„ํ—˜
  • ์–ด๋Š ํ…Œ์ด๋ธ”์„ ๋ฒ ์ด์Šค๋กœ ์žก์„์ง€ ๊ฒฐ์ •

SQL์˜ Bool ํƒ€์ž…

True ํ˜น์€ False ๊ฐ’์„ ๊ฐ€์ง€๋Š” ๋ฐ์ดํ„ฐํ˜•์ด๋‹ค. ํ•˜์ง€๋งŒ NULL ๊ฐ’์„ ๊ฐ€์งˆ ์ˆ˜ ์žˆ๊ธฐ ๋•Œ๋ฌธ์— ์กฐ๊ฑด์„ ๊ฑธ ๋•Œ ์ฃผ์˜ํ•ด์•ผํ•œ๋‹ค.

  • ๋‹ค์Œ 2๊ฐœ๋Š” ๋™์ผํ•œ ํ‘œํ˜„์ด๋‹ค
flag = True
flag IS True
  • ๋‹ค์Œ 2๊ฐœ๋Š” ๋™์ผํ•˜์ง€ ์•Š๋‹ค
flag IS True
flag IS NOT False

์ด์œ ๋Š” flag๊ฐ€ NULL ๊ฐ’์ผ ๊ฒฝ์šฐ IS NOT False ์กฐ๊ฑด์— ๋“ค์–ด๊ฐ€๊ฒŒ ๋˜๊ธฐ ๋•Œ๋ฌธ์— ๋‘ ์ฟผ๋ฆฌ์˜ ๊ฒฐ๊ณผ๊ฐ’์ด ๋‹ค๋ฅด๊ฒŒ ๋œ๋‹ค.

NULL ๋น„๊ต

NULL ๋น„๊ต๋Š” ํ•ญ์ƒ IS ํ˜น์€ IS NOT ์œผ๋กœ ์ˆ˜ํ–‰ํ•ด์•ผ ํ•œ๋‹ค.
!= ์ด๋‚˜ <> ์œผ๋กœ ์ˆ˜ํ–‰ํ•˜๋ฉด ์ž˜๋ชป๋œ ๊ฒฐ๊ณผ๊ฐ€ ๋‚˜์˜จ๋‹ค.

COALESCE

  • NULL๊ฐ’์„ ๋‹ค๋ฅธ ๊ฐ’์œผ๋กœ ๋ฐ”๊ฟ”์ฃผ๋Š” ํ•จ์ˆ˜
COALESCE(exp1, exp2, exp3,...)

exp1 ๋ถ€ํ„ฐ ์ธ์ž๋ฅผ ํ•˜๋‚˜์”ฉ ์‚ดํŽด์„œ NULL์ด ์•„๋‹Œ ๊ฐ’์ด ๋‚˜์˜ค๋ฉด ๊ทธ๊ฑธ ๋ฆฌํ„ดํ•œ๋‹ค.
์ธ์ž๋ฅผ ๋ชจ๋‘ ์‚ดํˆ๋Š”๋ฐ๋„ ๋ชจ๋‘ NULL์ด๋ผ๋ฉด ์ตœ์ข…์ ์œผ๋กœ NULL์„ ๋ฆฌํ„ดํ•œ๋‹ค.

SELECT
	value,
	COALESCE(value, 0) -- value๊ฐ€ NULL์ด๋ฉด 0์„ ๋ฆฌํ„ดํ•œ๋‹ค.
FROM raw_data.count_test

๊ณต๋ฐฑ ํ˜น์€ ์˜ˆ์•ฝํ‚ค์›Œ๋“œ๋ฅผ ํ•„๋“œ ์ด๋ฆ„์œผ๋กœ ์‚ฌ์šฉํ•˜๋ ค๋ฉด?

"" ๋กœ ๋‘˜๋Ÿฌ์‹ธ์„œ ์‚ฌ์šฉํ•œ๋‹ค.

CREATE TABLE test (
	group int primary key,
    'mailing address' varchar(32)
)

๐Ÿ“ ์ฃผ์š”๋ฉ”๋ชจ์‚ฌํ•ญ

Colab ๋งํฌ : https://colab.research.google.com/drive/1w9xDcgagtxKAD1n5cmtO8-LKL_ujc1B8

์ฐธ๊ณ  :
https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

https://www.w3schools.com/mysql/


๐Ÿ˜ต ๊ณต๋ถ€ํ•˜๋ฉด์„œ ์–ด๋ ค์› ๋˜ ๋‚ด์šฉ

profile
๊ฑฐ์นœ ๋Œ์ด ๋‹ค๋“ฌ์–ด์ ธ ์กฐ๊ฐ์ด ๋˜๋“ฏ

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