[SQLD] SQL ๊ธฐ๋ณธ - 2

ํ˜คยท2023๋…„ 5์›” 12์ผ
0

SQLD

๋ชฉ๋ก ๋ณด๊ธฐ
4/18
post-thumbnail

๐Ÿ’ก SQLD ์ž๊ฒฉ์ฆ ์‹œํ—˜ ๋Œ€๋น„ ํ•™์Šตํ•œ ๋‚ด์šฉ์„ ์š”์•ฝ์ •๋ฆฌํ•ฉ๋‹ˆ๋‹ค.

WHERE ์กฐ๊ฑด์ ˆ

WHERE ์ ˆ์„ ์‚ฌ์šฉํ•˜์ง€ ์•Š๊ณ  ํ•„์š” ์—†๋Š” ๋งŽ์€ ์ž๋ฃŒ๋“ค์„ DB๋กœ๋ถ€ํ„ฐ ์š”์ฒญํ•˜๊ฒŒ ๋˜๋ฉด CPU, ๋ฉ”๋ชจ๋ฆฌ์™€ ๊ฐ™์€ ์‹œ์Šคํ…œ ์ž์›์„ ๊ณผ๋‹คํ•˜๊ฒŒ ์‚ฌ์šฉํ•˜๊ฒŒ ๋œ๋‹ค.

WHERE ์ ˆ์€ ์กฐํšŒํ•˜๋ ค๋Š” ๋ฐ์ดํ„ฐ์˜ ํŠน์ • ์กฐ๊ฑด์„ ๋ถ€์—ฌํ•  ๋ชฉ์ ์œผ๋กœ ์‚ฌ์šฉํ•˜๊ธฐ ๋•Œ๋ฌธ์— FROM ์ ˆ ๋’ค์— ์˜จ๋‹ค.

SELECT ์ปฌ๋Ÿผ๋ช… FROM ํ…Œ์ด๋ธ”๋ช… WHERE ์กฐ๊ฑด์‹;

์กฐ๊ฑด์‹์˜ ๊ตฌ์„ฑ

  • ์ปฌ๋Ÿผ๋ช…, ๋น„๊ต์—ฐ์‚ฐ์ž, ๋ฌธ์ž, ์ˆซ์ž, ํ‘œํ˜„์‹, ๋น„๊ต ์ปฌ๋ ด๋ช…

์—ฐ์‚ฐ์ž์˜ ์ข…๋ฅ˜

  1. ๋น„๊ต ์—ฐ์‚ฐ์ž : =, >, >=, <, <=
  2. SQL ์—ฐ์‚ฐ์ž : BETWEEN a AND b, IN (list), LIKE ๋ฌธ์ž์—ด, IS NULL
  3. ๋…ผ๋ฆฌ ์—ฐ์‚ฐ์ž : AND, OR, NOT
  4. ๋ถ€์ • ๋น„๊ต ์—ฐ์‚ฐ์ž : !=, ^=, <>, NOT ์ปฌ๋Ÿผ๋ช… =, NOT ์ปฌ๋Ÿผ๋ช… >
  5. ๋ถ€์ • SQL ์—ฐ์‚ฐ์ž : NOT BETWEEN a AND b, NOT IN (list), IS NOT NULL

์—ฐ์‚ฐ์ž ์šฐ์„ ์ˆœ์œ„

๊ด„ํ˜ธ() -> NOT ์—ฐ์‚ฐ์ž -> ๋น„๊ต, SQL ๋น„๊ต ์—ฐ์‚ฐ์ž ->AND ->OR

ROWNUM(์˜ค๋ผํด)

์ปฌ๋Ÿผ๊ณผ ๋น„์Šทํ•œ ์„ฑ๊ฒฉ์˜ Pseudo Column, ์ฒ˜๋ฆฌ ๊ฒฐ๊ณผ ์ง‘ํ•ฉ์˜ ๊ฐ ํ–‰์— ๋Œ€ํ•ด ์ž„์‹œ๋กœ ๋ถ€์—ฌ๋˜๋Š” ์ผ๋ จ๋ฒˆํ˜ธ์ด๋ฉฐ, WHERE ์ ˆ์—์„œ ํ–‰์˜ ๊ฐœ์ˆ˜๋ฅผ ์ œํ•œํ•˜๋Š” ๋ชฉ์ ์œผ๋กœ ์‚ฌ์šฉํ•œ๋‹ค.

SELECT ์ปฌ๋Ÿผ๋ช… FROM ํ…Œ์ด๋ธ”๋ช… WHERE ROWNUM <= n;

TOP์ ˆ : ๊ฒฐ๊ณผ ์ง‘ํ•ฉ์œผ๋กœ ์ถœ๋ ฅ๋˜๋Š” ํ–‰์˜ ์ˆ˜๋ฅผ ์ œํ•œ(SQL Server)

SELECT TOP(n) ์ปฌ๋Ÿผ๋ช… FROM ํ…Œ์ด๋ธ”๋ช…;


ํ•จ์ˆ˜(Function)

๋‚ด์žฅ ํ•จ์ˆ˜ : ๋ฒค๋”์—์„œ ์ œ๊ณตํ•˜๋Š” ํ•จ์ˆ˜
์‚ฌ์šฉ์ž ์ •์˜ ํ•จ์ˆ˜ : ์‚ฌ์šฉ์ž๊ฐ€ ์ •์˜ํ•˜๋Š” ํ•จ์ˆ˜

๋‚ด์žฅ ํ•จ์ˆ˜

  • ๋‹จ์ผํ–‰ ํ•จ์ˆ˜ : ์ž…๋ ฅ ๊ฐ’์ด ๋‹จ์ผํ–‰ ๊ฐ’์ด ์ž…๋ ฅ
  • ๋‹ค์ค‘ํ–‰ ํ•จ์ˆ˜ : ์—ฌ๋Ÿฌ ํ–‰์˜ ๊ฐ’์ด ์ž…๋ ฅ

๐Ÿ’กํ•จ์ˆ˜ = ์ž…๋ ฅ๊ฐ’ : ์ถœ๋ ฅ๊ฐ’ = m : 1

๋‹จ์ผํ–‰ ํ•จ์ˆ˜์˜ ์ข…๋ฅ˜(์˜ค๋ผํด/SQL Server)

1. ๋ฌธ์žํ˜• ํ•จ์ˆ˜

  • LOWER : ์•ŒํŒŒ๋ฒณ ๋Œ€ -> ์†Œ
  • UPPER : ์•ŒํŒŒ๋ฒณ ์†Œ -> ๋Œ€
  • ASCII : ๋ฌธ์ž, ์ˆซ์ž -> ASCII ์ฝ”๋“œ
  • CHA/CHAR : ASCII ์ฝ”๋“œ -> ๋ฌธ์ž, ์ˆซ์ž
  • CONCAT : ๋ฌธ์ž์—ด์„ ์—ฐ๊ฒฐ
    CONCAT(โ€˜abc, โ€˜def); => โ€˜abcdefโ€™
  • SUBSTR/SUBSTRING : m๋ถ€ํ„ฐ n๊ฐœ์˜ ๋ฌธ์ž๋ฅผ ๋ฐ˜ํ™˜
    SUBSTR(โ€˜012345โ€™, 2, 3); => โ€˜234โ€™
  • LENGTH/LEN : ๋ฌธ์ž์—ด์˜ ๊ธธ์ด ๋ฐ˜ํ™˜
  • LTRIM : ๋ฌธ์ž์—ด์— ์ฒซ ๋ฌธ์ž๋ถ€ํ„ฐ ์‹œ์ž‘ํ•ด ์ง€์ •๋ฌธ์ž๋ฅผ ์‚ญ์ œ
    LTRIM(โ€˜study springโ€™, โ€˜sโ€™); => โ€˜tudy springโ€™โ€™
  • RTRIM : ๋ฌธ์ž์—ด์— ๋’ท ๋ฌธ์ž๋ถ€ํ„ฐ ์‹œ์ž‘ํ•ด ์ง€์ •๋ฌธ์ž ์‚ญ์ œ
    RTRIM(โ€˜study springโ€™, โ€˜sโ€™); => โ€˜study pringโ€™
  • TRIM : ๋ฌธ์ž์—ด์—์„œ ๋จธ๋ฆฌ๋ง, ๊ผฌ๋ฆฌ๋ง, ์–‘์ชฝ ์ง€์ •๋ฌธ์ž๋ฅผ ์ œ๊ฑฐ
    TRIM(โ€˜aโ€™, FROM โ€˜aaBBccDDBaDaaโ€™); => โ€˜BBccDDBaDโ€™

2. ์ˆซ์žํ˜• ํ•จ์ˆ˜

  • ABS : ์ˆซ์ž์˜ ์ ˆ๋Œ€๊ฐ’
  • SIGN : ์ˆซ์ž๊ฐ€ ์–‘์ˆ˜, ์Œ์ˆ˜, 0 ๊ตฌ๋ณ„
  • MOD : ์ˆ˜1์„ ์ˆ˜2๋กœ ๋‚˜๋ˆ„์–ด ๋‚˜๋จธ์ง€ ๊ฐ’์„ ๋ฐ˜ํ™˜(% ๋Œ€์ฒด๊ฐ€๋Šฅ)
    MOD(7, 3) = 7 % 3
  • CEIL/CEILING : ์†Œ์ˆ˜์  ์˜ฌ๋ฆผ
  • FLOOR : ์†Œ์ˆ˜์  ๋‚ด๋ฆผ
  • ROUND : ์ˆซ์ž๋ฅผ ์†Œ์ˆ˜์  m์ž๋ฆฌ์—์„œ ๋ฐ˜์˜ฌ๋ฆผ
    ROUND(38.5235, 3) = 38.524
  • TRUNC : ์ˆซ์ž๋ฅผ ์†Œ์ˆ˜ m์ž๋ฆฌ์—์„œ ์ž˜๋ผ์„œ ๋ฒ„๋ฆผ
    TRUNC(38.5235, 3) = 38.523
  • SIN, COS, TAN : ์ˆซ์ž์˜ ์‚ผ๊ฐํ•จ์ˆ˜ ๊ฐ’์„ ๋ฐ˜ํ™˜
  • EXP(์ง€์ˆ˜), POWER(๊ฑฐ๋“ญ์ œ๊ณฑ), SQRT(์ œ๊ณฑ๊ทผ), LOG(๋กœ๊ทธ), LN(์ž์—ฐ๋กœ๊ทธ)

3. ๋‚ ์งœํ˜• ํ•จ์ˆ˜

  • SYSDATE/GETDATE() : ํ˜„์žฌ ๋‚ ์งœ์™€ ์‹œ๊ฐ„์„ ์ถœ๋ ฅ
  • EXTRACT/DATEPART : ๋…„/์›”/์ผ ๋ฐ์ดํ„ฐ ์ถœ๋ ฅ
  • TO_NUMBER(TO_CHAR(d, โ€˜YYYYโ€™))/ YEAR(d)
  • TO_NUMBER(TO_CHAR(d, โ€˜MMโ€™))/ MONTH(d)
  • TO_NUMBER(TO_CHAR(d, โ€˜DDโ€™))/ DAY(d)

4. ๋ณ€ํ™˜ํ˜• ํ•จ์ˆ˜

ํŠน์ • ๋ฐ์ดํ„ฐ ํƒ€์ž…์„ ๋‹ค์–‘ํ•œ ํ˜•์‹์œผ๋กœ ์ถœ๋ ฅํ•˜๊ณ  ์‹ถ์„ ๊ฒฝ์šฐ ์‚ฌ์šฉ

  • ๋ช…์‹œ์  ๋ฐ์ดํ„ฐ ์œ ํ˜• ๋ฐ˜ํ™˜ : ์ง์ ‘ ๋ณ€ํ™˜ ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉ
  • ์•”์‹œ์  ๋ฐ์ดํ„ฐ ์œ ํ˜• ๋ฐ˜ํ™˜ : ์ž๋™์œผ๋กœ DB๊ฐ€ ๋ณ€ํ™˜ํ•ด์คŒ

๋ช…์‹œ์  ๋ฐ์ดํ„ฐ ์œ ํ˜• ๋ฐ˜ํ™˜์˜ ํ•จ์ˆ˜

์˜ค๋ผํด

  • TO_NUMBER(๋ฌธ์ž์—ด) : ๋ฌธ์ž์—ด -> ์ˆซ์ž
  • TO_CHAR(์ˆซ์ž|๋‚ ์งœ [, FORMAT]) : ์ˆซ์ž๋‚˜ ๋‚ ์งœ๋ฅผ ์ฃผ์–ด์ง„ FORMAT ํ˜•ํƒœ๋กœ ๋ฌธ์ž์—ด ํƒ€์ž…์œผ๋กœ ๋ณ€ํ™˜
  • TO_DATE(๋ฌธ์ž์—ด [, FORMAT]) : ๋ฌธ์ž์—ด -> ๋‚ ์งœํƒ€์ž…

SQL Server

  • CAST(expression AS data_type [(length)])
  • CONVERT(data_type [(length)], expression [, style ])

CASE ํ‘œํ˜„

IF-THEN-ELSE ๋…ผ๋ฆฌ์™€ ์œ ์‚ฌํ•œ ๋ฐฉ์‹์œผ๋กœ ํ‘œํ˜„์‹์„ ์ž‘์„ฑํ•ด SQL์˜ ๋น„๊ต ์—ฐ์‚ฐ ๊ธฐ๋Šฅ์„ ๋ณด์™„ํ•˜๋Š” ์—ญํ• ์„ ํ•œ๋‹ค.

CASE SIMPLE(SEARCHED)_CASE_EXPRESSION ์กฐ๊ฑด ELSE ํ‘œํ˜„์ ˆ END;
SIMPLE(SEARCHED)_CASE_EXPRESSION ์กฐ๊ฑด์ด ๋งž์œผ๋ฉด ํ•ด๋‹น ์กฐ๊ฑด ๋‚ด์˜ THEN์ ˆ์„ ์ˆ˜ํ–‰ํ•˜๊ณ  ๋งž์ง€ ์•Š์œผ๋ฉด ELSE์ ˆ์„ ์ˆ˜ํ–‰ํ•œ๋‹ค.

DECODE(ํ‘œํ˜„์‹, ๊ธฐ์ค€๊ฐ’1, ๊ฐ’1 [, ๊ธฐ์ค€๊ฐ’2, ๊ฐ’2, โ€ฆ, ๋””ํดํŠธ๊ฐ’)

์˜ค๋ผํด์—์„œ๋งŒ ์‚ฌ์šฉํ•˜๋Š” ํ•จ์ˆ˜๋กœ, ํ‘œํ˜„์‹์˜ ๊ฐ’์ด ๊ธฐ์ค€๊ฐ’1์ด๋ฉด ๊ฐ’1์„ ์ถœ๋ ฅํ•˜๊ณ , ๊ธฐ์ค€๊ฐ’2๋ฉด ๊ฐ’2๋ฅผ ์ถœ๋ ฅํ•œ๋‹ค. ๋งŒ์•ฝ ๊ธฐ์ค€๊ฐ’์ด ์—†์œผ๋ฉด ๋””ํดํŠธ๊ฐ’์„ ์ถœ๋ ฅํ•œ๋‹ค.

NULL ๊ด€๋ จ ํ•จ์ˆ˜

  • NVL(์‹1,์‹2)/ISNULL(์‹1,์‹2) : ์‹1์˜ ๊ฐ’์ด NULL ์ด๋ฉด ์‹2 ์ถœ๋ ฅ
  • NULLIF(์‹1,์‹2) : ์‹1์ด ์‹2์™€ ๊ฐ™์œผ๋ฉด NULL์„ ์•„๋‹ˆ๋ฉด ์‹1์„ ์ถœ๋ ฅ
  • COALESCE(์‹1,์‹2) : ์ž„์˜์˜ ๊ฐœ์ˆ˜ํ‘œํ˜„์‹์—์„œ NULL์ด ์•„๋‹Œ ์ตœ์ดˆ์˜ ํ‘œํ˜„์‹, ๋ชจ๋‘ NULL์ด๋ฉด NULL ๋ฐ˜ํ™˜ex)COALESCE(NULL,NULL,โ€˜abcโ€™) -> โ€˜abc

GROUP BY, HAVING ์ ˆ

์ง‘๊ณ„ ํ•จ์ˆ˜

  • ๋‹ค์ค‘ํ–‰ ํ•จ์ˆ˜ ์ค‘ ํ•˜๋‚˜๋กœ ์—ฌ๋Ÿฌ ํ–‰๋“ค์˜ ๊ทธ๋ฃน์ด ๋ชจ์—ฌ์„œ ๊ทธ๋ฃน๋‹น ๋‹จ ํ•˜๋‚˜์˜ ๊ฒฐ๊ณผ๋ฅผ ๋Œ๋ ค์ฃผ๋Š” ํ•จ์ˆ˜
  • GROUP BY์ ˆ์€ ํ–‰๋“ค์„ ์†Œ๊ทธ๋ฃนํ™” ํ•œ๋‹ค.
  • SELECT์ ˆ, HAVING์ ˆ, ORDER BY ์ ˆ์— ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋‹ค.

์ง‘๊ณ„ ํ•จ์ˆ˜์˜ ์ข…๋ฅ˜

  • COUNT(*) : NULL ํฌํ•จ ํ–‰์˜ ์ˆ˜
  • COUNT(ํ‘œํ˜„์‹) : NULL ์ œ์™ธ ํ–‰์˜ ์ˆ˜
  • SUM, AVG : NULL ์ œ์™ธ ํ•ฉ๊ณ„, ํ‰๊ท  ์—ฐ์‚ฐ
  • STDDEV : ํ‘œ์ค€ ํŽธ์ฐจ
  • VARIAN : ๋ถ„์‚ฐ
  • MAX, MIN : ์ตœ๋Œ€๊ฐ’, ์ตœ์†Œ๊ฐ’

GROUP BY์ ˆ

๋ฐ์ดํ„ฐ๋“ค์„ ์ž‘์€์€ ๊ทธ๋ฃน์œผ๋กœ ๋ถ„๋ฅ˜ํ•˜์—ฌ ์†Œ๊ทธ๋ฃน์— ๋Œ€ํ•œ ํ•ญ๋ชฉ๋ณ„๋กœ ํ†ต๊ณ„ ์ •๋ณด๋ฅผ ์–ป์„ ๋•Œ ์ถ”๊ฐ€ ์‚ฌ์šฉ๋œ๋‹ค.

HAVING ์ ˆ

WHERE์ ˆ๊ณผ ๋น„์Šทํ•˜์ง€๋งŒ ๊ทธ๋ฃน์„ ๋‚˜ํƒœ๋Š” ๊ฒฐ๊ณผ ์ง‘ํ•ฉ์˜ ํ–‰์— ์กฐ๊ฑด์ด ์ ์šฉ๋œ๋‹ค๋Š” ์ ์—์„œ ์ฐจ์ด๊ฐ€ ์žˆ๋‹ค.

SELECT ์ปฌ๋Ÿผ๋ช… FROM ํ…Œ์ด๋ธ”๋ช… [WHERE ์กฐ๊ฑด์‹] [GROUP BY ์ปฌ๋Ÿผ์ด๋‚˜ ํ‘œํ˜„์‹] [HAVING ๊ทธ๋ฃน์กฐ๊ฑด์‹];

SELECT position ํฌ์ง€์…˜ FROM player GROUP BY position HAVING AVG(height) >= 180;

GROUP BY, HAVING ์ ˆ์˜ ํŠน์ง•

  1. GROUP BY ์ ˆ์„ ํ†ตํ•ด ์†Œ๊ทธ๋ฃน๋ณ„ ๊ธฐ์ค€์„ ์ •ํ•œ ํ›„, SELECT ์ ˆ์— ์ง‘๊ณ„ ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•œ๋‹ค.
  2. ์ง‘๊ณ„ ํ•จ์ˆ˜์˜ ํ†ต๊ณ„ ์ •๋ณด๋Š” NULL ๊ฐ’์„ ๊ฐ€์ง„ ํ–‰์„ ์ œ์™ธํ•˜๊ณ  ์ˆ˜ํ–‰ํ•œ๋‹ค.
  3. GROUP BY ์ ˆ์—์„œ๋Š” ALIAS ์‚ฌ์šฉ ๋ถˆ๊ฐ€
  4. ์ง‘๊ณ„ ํ•จ์ˆ˜๋Š” WHERE ์ ˆ์— ์˜ฌ ์ˆ˜ ์—†๋‹ค.(GROUP BY์ ˆ๋ณด๋‹ค WERER์ ˆ์ด ๋จผ์ € ์ˆ˜ํ–‰๋œ๋‹ค)
  5. HAVING ์ ˆ์—๋Š” ์ง‘๊ณ„ํ•จ์ˆ˜๋ฅผ ์ด์šฉํ•˜์—ฌ ์กฐ๊ฑด ํ‘œ์‹œo
  6. HAVING ์ ˆ์€ ์ผ๋ฐ˜์ ์œผ๋กœ GROUP BY ๋’ค์— ์œ„์น˜

GROUP BY ์†Œ๊ทธ๋ฃน์˜ ๋ฐ์ดํ„ฐ ์ค‘ ์ผ๋ถ€๋งŒ ์ถ”์ถœํ•˜๋Š” ๋ฐฉ๋ฒ•

  1. GROUP BY ์—ฐ์‚ฐ ์ „ WHERE ์ ˆ์—์„œ ์กฐ๊ฑด์„ ์ ์šฉํ•˜์—ฌ ํ•„์š”ํ•œ ๋ฐ์ดํ„ฐ๋งŒ ์ถ”์ถœํ•˜์—ฌ GROUP BY ์—ฐ์‚ฐ์„ ํ•˜๋Š” ๋ฐฉ๋ฒ•
  2. GROUP BY ์—ฐ์‚ฐ ํ›„ HAVING ์ ˆ์—์„œ ํ•„์š”ํ•œ ๋ฐ์ดํ„ฐ๋งŒ ํ•„ํ„ฐ๋ง

๊ฐ€๋Šฅํ•˜๋ฉด 1๋ฒˆ ๋ฐฉ๋ฒ•์„ ์‚ฌ์šฉํ•˜๋Š” ๊ฒƒ์ด ์ž์› ์‚ฌ์šฉ ์ธก๋ฉด์—์„œ ํšจ์œจ์ ์ด๋‹ค.


ORDER BY ์ ˆ

: SQL ๋ฌธ์žฅ์œผ๋กœ ์กฐํšŒ๋œ ๋ฐ์ดํ„ฐ๋“ค์„ ๋‹ค์–‘ํ•œ ๋ชฉ์ ์— ๋งž๊ฒŒ ํŠน์ • ์ปฌ๋Ÿผ์„ ๊ธฐ์ค€์œผ๋กœ ์ •๋ ฌํ•˜์—ฌ ์ถœ๋ ฅํ•˜๋Š”๋ฐ ์‚ฌ์šฉ.

ORDER BY ์ ˆ์˜ ํŠน์ง•

  • ALIAS ๋ช…์ด๋‚˜ ์ปฌ๋Ÿผ ์ˆœ์„œ๋ฅผ ๋‚˜ํƒ€๋‚ด๋Š” ์ •์ˆ˜ ์‚ฌ์šฉ ๊ฐ€๋Šฅ
    (์œ ์ง€๋ณด์ˆ˜์„ฑ, ๊ฐ€๋…์„ฑ์„ ๊ณ ๋ คํ•˜์—ฌ ์ปฌ๋Ÿผ๋ช…, ALIAS๋ช… ์‚ฌ์šฉ ๊ถŒ๊ณ )
  • ๊ธฐ๋ณธ์ ์œผ๋กœ ์˜ค๋ฆ„์ฐจ์ˆœ ์ ์šฉ
  • SQL ๋ฌธ์žฅ์˜ ์ œ์ผ ๋งˆ์ง€๋ง‰์— ์œ„์น˜์น˜

SELECT ์ปฌ๋Ÿผ๋ช… FROM ํ…Œ์ด๋ธ”๋ช… [WHERE ์กฐ๊ฑด์‹] [GROUP BY ์ปฌ๋Ÿผ์ด๋‚˜ ํ‘œํ˜„์‹] [HAVING ๊ทธ๋ฃน์กฐ๊ฑด์‹] [ORDER BY ์ปฌ๋Ÿผ์ด๋‚˜ ํ‘œํ˜„์‹[ASC or DESC]];

  • ASC(Ascending) : ์˜ค๋ฆ„์ฐจ์ˆœ(๊ธฐ๋ณธ๊ฐ’, ์ƒ๋žต๊ฐ€๋Šฅ)
  • DESC(Descending) : ๋‚ด๋ฆผ์ฐจ์ˆœ

๐Ÿ’ก Null์˜ ์ทจ๊ธ‰
์˜ค๋ผํด : null ๊ฐ’์„ ๊ฐ€์žฅ ํฐ ๊ฐ’์œผ๋กœ ์ทจ๊ธ‰
SQL Server : null ๊ฐ’์„ ๊ฐ€์žฅ ์ž‘์€ ๊ฐ’์œผ๋กœ ์ทจ๊ธ‰

SELECT ๋ฌธ์žฅ ์‹คํ–‰ ์ˆœ์„œ

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

๊ด€๊ณ„ํ˜• ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๊ฐ€ ๋ฐ์ดํ„ฐ๋ฅผ ๋ฉ”๋ชจ๋ฆฌ์— ์˜ฌ๋ฆด ๋•Œ ํ–‰ ๋‹จ์œ„๋กœ ๋ชจ๋“  ์ปฌ๋Ÿผ์„ ๊ฐ€์ ธ์˜ค๊ฒŒ ๋˜๋ฏ€๋กœ, SELECT ์ ˆ์—์„œ ์ผ๋ถ€ ์ปฌ๋Ÿผ๋งŒ ์„ ํƒํ•˜๋”๋ผ๋„ ORDER BY ์ ˆ์—์„œ ๋ฉ”๋ชจ๋ฆฌ์— ์˜ฌ๋ผ์™€ ์žˆ๋Š” ๋‹ค๋ฅธ ์ปฌ๋Ÿผ์˜ ๋ฐ์ดํ„ฐ๋ฅผ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋‹ค.

SELECT ์ปฌ๋Ÿผ1, ์ปฌ๋Ÿผ2 FROM ํ…Œ์ด๋ธ”๋ช… ORDER BY ์ปฌ๋Ÿผ3;


์กฐ์ธ(JOIN)

: ๋‘ ๊ฐœ ์ด์ƒ์˜ ํ…Œ์ด๋ธ”๋“ค์„ ์—ฐ๊ฒฐ ๋˜๋Š” ๊ฒฐํ•ฉํ•˜์—ฌ ๋ฐ์ดํ„ฐ๋ฅผ ์ถœ๋ ฅํ•˜๋Š” ๊ฒƒ. ์ผ๋ฐ˜์ ์œผ๋กœ ํ–‰๋“ค์€ PK, FK ๊ฐ’์˜ ์—ฐ๊ด€์— ์˜ํ•ด JOIN์ด ์„ฑ๋ฆฝ๋˜์ง€๋งŒ ์–ด๋–ค ๊ฒฝ์šฐ์—๋Š” PK, FK ๊ด€๊ณ„๊ฐ€ ์—†์–ด๋„ ๋…ผ๋ฆฌ์ ์ธ ๊ฐ’๋“ค์˜ ์—ฐ๊ด€๋งŒ์œผ๋กœ ์„ฑ๋ฆฝ ๊ฐ€๋Šฅ!

๊ด€๊ณ„ํ˜• ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์—์„œ ๊ฐ€์žฅ ํฐ ์žฅ์ ์ด์ž ๋Œ€ํ‘œ ํ•ต์‹ฌ ๊ธฐ๋Šฅ!

๐Ÿšจ ์ฃผ์˜ํ•  ์ !
FROM ์ ˆ์— ์—ฌ๋Ÿฌ ํ…Œ์ด๋ธ”์ด ๋‚˜์—ด๋˜๋”๋ผ๋„ SQL์—์„œ ๋ฐ์ดํ„ฐ๋ฅผ ์ฒ˜๋ฆฌํ•  ๋•Œ๋Š” ๋‹จ ๋‘ ๊ฐœ์˜ ์ง‘ํ•ฉ ๊ฐ„์—๋งŒ ์กฐ์ธ์ด ์ผ์–ด๋‚œ๋‹ค.
JOIN์— ์ฐธ์—ฌํ•˜๋Š” ๋Œ€์ƒ ํ…Œ์ด๋ธ” : N๊ฐœ
ํ•„์š”ํ•œ JOIN์˜ ์กฐ๊ฑด : N-1๊ฐœ ์ด์ƒ

JOIN์˜ ์กฐ๊ฑด์€ WHERE ์ ˆ์— ๊ธฐ์ˆ ํ•œ๋‹ค.

EQUI JOIN(๋“ฑ๊ฐ€ ์กฐ์ธ, โ€˜=โ€™)

  • ๋‘ ๊ฐœ์˜ ํ…Œ์ด๋ธ” ๊ฐ„์— ์ปฌ๋Ÿผ ๊ฐ’๋“ค์ด ์„œ๋กœ ์ •ํ™•ํ•˜๊ฒŒ ์ผ์น˜ํ•˜๋Š” ๊ฒฝ์šฐ ์‚ฌ์šฉ๋˜๋Š” ๋ฐฉ๋ฒ•. ๋Œ€๋ถ€๋ถ„ PK, FK์˜ ๊ด€๊ณ„๋ฅผ ๊ธฐ๋ฐ˜์œผ๋กœ ํ•œ๋‹ค.

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

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

  • FROM์ ˆ์˜ ํ…Œ์ด๋ธ”์— ๋Œ€ํ•ด์„œ ALIAS๋ฅผ ์‚ฌ์šฉ ํ•  ์ˆ˜ ์žˆ๋‹ค.
  • ๊ฐ€๋…์„ฑ์„ ์œ„ํ•ด ํ…Œ์ด๋ธ”๋ช… ๋Œ€์‹  ALIAS ์‚ฌ์šฉ์„ ๊ถŒ๊ณ .

Non EQUI JOIN(๋น„๋“ฑ๊ฐ€ ์กฐ์ธ)

  • ๋‘ ๊ฐœ์˜ ํ…Œ์ด๋ธ” ๊ฐ„์— ์ปฌ๋Ÿผ ๊ฐ’๋“ค์ด ์„œ๋กœ ์ •ํ™•ํ•˜๊ฒŒ ์ผ์น˜ํ•˜์ง€ ์•Š๋Š” ๊ฒฝ์šฐ ์‚ฌ์šฉ.
  • BETWEEN, >, >=, <, <= ๋“ฑ์˜ ์—ฐ์‚ฐ์ž๋ฅผ ์‚ฌ์šฉ

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

profile
๐Ÿง‘โ€๐Ÿ’ป๋ฐฑ์—”๋“œ ๊ฐœ๋ฐœ์ž, ์กฐ๊ธˆ์”ฉ ๊พธ์ค€ํ•˜๊ฒŒ

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