Skip to content

๐Ÿ SQL UnvUs Style Convention โ€‹

๊ฐ€๋…์„ฑ ๋†’์€ SQL ์ž‘์„ฑ์„ ์œ„ํ•œ UnvUs ์Šคํƒ€์ผ ๊ฐ€์ด๋“œ

โœจ ๊ฐœ์š” (Overview) โ€‹

SQL์€ ๋‹จ์ˆœํ•œ ๋ฐ์ดํ„ฐ ์งˆ์˜ ์–ธ์–ด๊ฐ€ ์•„๋‹™๋‹ˆ๋‹ค. ์šฐ๋ฆฌ๊ฐ€ ์ž‘์„ฑํ•˜๋Š” ์ฟผ๋ฆฌ๋„ ํ•˜๋‚˜์˜ ๋””์ž์ธ์ด๋ฉฐ, ๊ทธ ๋””์ž์ธ์ด ๋ช…ํ™•ํ•˜๊ณ  ํ๋ฆ„์ด ์žˆ์–ด์•ผ ๋ฐ์ดํ„ฐ๋„ ์•„๋ฆ„๋‹ต๊ฒŒ ๋‹ค๊ฐ€์˜ต๋‹ˆ๋‹ค.

UnvUs์Šค๋Ÿฝ๊ณ  ์ง๊ด€์ ์ธ ์ฟผ๋ฆฌ ์ž‘์„ฑ์„ ์œ„ํ•œ UnvUs Style SQL Convention์„ ์†Œ๊ฐœํ•ฉ๋‹ˆ๋‹ค.

๐ŸŒŠ UnvUs Style ์ด๋ž€? โ€‹

INFO

  • River Style (๊ฐ•๋ฌผ ์Šคํƒ€์ผ): SQL ํ‚ค์›Œ๋“œ์™€ ์ ˆ์„ ๋งˆ์น˜ ๊ฐ•๋ฌผ์ด ํ๋ฅด๋“ฏ์ด ์ •๋ ฌํ•˜์—ฌ, ๊ฐ€๋…์„ฑ๊ณผ ๋…ผ๋ฆฌ ๊ตฌ์กฐ๋ฅผ ๊ทน๋Œ€ํ™”ํ•˜๋Š” ์Šคํƒ€์ผ์ž…๋‹ˆ๋‹ค.
  • UnvUs Style: River Style์˜ ํ•ต์‹ฌ ์›์น™์„ ๊ธฐ๋ฐ˜์œผ๋กœ, ํŠนํžˆ MyBatis ํ™˜๊ฒฝ์—์„œ์˜ ์ ์šฉ์„ฑ๊ณผ ๊ฐ€๋…์„ฑ์„ ๋”์šฑ ํ–ฅ์ƒ์‹œํ‚ค๊ธฐ ์œ„ํ•ด ๋ช‡ ๊ฐ€์ง€ ๊ทœ์น™์„ ์žฌ์ •์˜ํ•˜๊ณ  ๊ตฌ์ฒดํ™”ํ•œ ์Šคํƒ€์ผ์ž…๋‹ˆ๋‹ค. (์˜ˆ: <include> ํƒœ๊ทธ ์ฒ˜๋ฆฌ, ๋™์  SQL๊ณผ์˜ ์กฐํ™” ๋“ฑ)
๐Ÿ“Œ ์™ผ์ชฝ ์ •๋ ฌ ํ‚ค์›Œ๋“œ (Main Flow)๐Ÿ“Ž ์˜ค๋ฅธ์ชฝ ์ •๋ ฌ ํ‚ค์›Œ๋“œ (์กฐ๊ฑด/์ˆ˜์‹ ๋“ฑ)
SELECTAS
INSERT / DELETE / UPDATEIN, IS, LIKE, BETWEEN, EXISTS, DISTINCT
FROMTRUE / FALSE, NULL, NOT
JOIN, LEFT JOIN, ONALL, ANY
WHERE
AND / OR
GROUP BY
HAVING
ORDER BY
LIMIT, OFFSET
UNION, CASE, WHEN, THEN, ELSE, END

๐Ÿ“ ์Šคํƒ€์ผ ๊ฐ€์ด๋“œ๋ผ์ธ โ€‹

โœ… ๊ธฐ๋ณธ ๊ตฌ์กฐ โ€‹

sql
SELECT u.username
     , u.email
     , u.user_id
  FROM users AS u
 WHERE u.is_active = TRUE
 ORDER BY u.username ASC;
  • ์ฃผ์š” ํ‚ค์›Œ๋“œ๋Š” ์ƒˆ ์ค„์—์„œ ์‹œ์ž‘ํ•˜๋ฉฐ ์™ผ์ชฝ ์ •๋ ฌํ•ฉ๋‹ˆ๋‹ค.
  • ์„ ํƒ ์ปฌ๋Ÿผ, WHERE ์ ˆ ๋‚ด๋ถ€ ์กฐ๊ฑด ๋“ฑ์€ ํ•œ ๋‹จ๊ณ„ ๋“ค์—ฌ์“ฐ๊ธฐํ•ฉ๋‹ˆ๋‹ค.
  • AS, ํ• ๋‹น(=), ๋น„๊ต ์—ฐ์‚ฐ์ž (>=, <=, LIKE ๋“ฑ)๋Š” ๊ฐ™์€ ์ค„์—์„œ ์ž์—ฐ์Šค๋Ÿฝ๊ฒŒ ์ด์–ด์ง€๋„๋ก ํ•ฉ๋‹ˆ๋‹ค.

๐Ÿค JOIN ๊ตฌ์กฐ โ€‹

sql
    SELECT o.order_id
         , o.order_date
         , u.username
         , u.email
      FROM orders AS o
      JOIN users AS u
        ON o.user_id = u.user_id
 LEFT JOIN user_profiles AS p  -- LEFT JOIN๋„ JOIN๊ณผ ๋™์ผํ•œ ๋ผ์ธ์— ์ •๋ ฌ
        ON u.user_id = p.user_id   -- ON ์ ˆ์€ JOIN์— ๋งž์ถฐ ๋“ค์—ฌ์“ฐ๊ธฐ
       AND p.is_primary = TRUE     -- ON ์ ˆ์— ์—ฌ๋Ÿฌ ์กฐ๊ฑด์ด ์˜ฌ ๊ฒฝ์šฐ AND๋กœ ์—ฐ๊ฒฐ
     WHERE u.is_active = TRUE;
  • JOIN๊ณผ ON์€ ํ•œ ์„ธํŠธ๋กœ,
  • JOIN โ†’ ON ์ˆœ์„œ๋กœ ๋“ค์—ฌ์“ฐ๊ธฐํ•˜์—ฌ ๊ด€๊ณ„๋ฅผ ์‹œ๊ฐ
  • LEFT JOIN ์˜ ๊ฒฝ์šฐ JOIN ๊นŒ์ง€๊ฐ€ river

๐Ÿงฉ ์„œ๋ธŒ์ฟผ๋ฆฌ (Subquery) ์ž‘์„ฑ ๋ฐฉ์‹ โ€‹

( ๊ด„ํ˜ธ๋Š” ์ƒˆ๋กœ์šด ๋ฆฌ๋ฒ„(River)์˜ ์‹œ์ž‘์ ์ž…๋‹ˆ๋‹ค. ์„œ๋ธŒ์ฟผ๋ฆฌ ๋‚ด๋ถ€๋„ ๋ฆฌ๋ฒ„ ์Šคํƒ€์ผ์„ ๊ทธ๋Œ€๋กœ ์œ ์ง€ํ•ฉ๋‹ˆ๋‹ค.

โœ… ์˜ˆ์‹œ โ€‹

sql
SELECT u.username
     , u.email
     , u.user_id
  FROM users AS u
 WHERE u.user_id IN
       (SELECT o.user_id
          FROM orders AS o
         WHERE o.order_date >= '2024-01-01'
           AND o.order_date <= '2024-12-31'
           AND o.status = 'COMPLETED')
   AND u.is_active = TRUE
 ORDER BY u.username ASC;

TIP

  • ์—ฌ๋Š” ๊ด„ํ˜ธ (๋Š” ์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ ์‚ฌ์šฉํ•˜๋Š” ํ‚ค์›Œ๋“œ(์˜ˆ: IN, =, EXISTS)์™€ ๊ฐ™์€ ์ค„์˜ ๋‹ค์Œ ์ค„๋กœ ๋‚ด๋ ค ํ•œ ๋‹จ๊ณ„ ๋“ค์—ฌ์”๋‹ˆ๋‹ค. (์œ„ ์˜ˆ์‹œ๋Š” ๋‹ค์Œ ์ค„๋กœ ๋‚ด๋ฆฐ ํ˜•ํƒœ)
  • ์ด ๋“ค์—ฌ์“ฐ๊ธฐ๋ฅผ ๊ธฐ์ค€์œผ๋กœ ์„œ๋ธŒ์ฟผ๋ฆฌ ๋‚ด๋ถ€์˜ River ์Šคํƒ€์ผ์„ ์ ์šฉํ•ฉ๋‹ˆ๋‹ค.
  • ์„œ๋ธŒ์ฟผ๋ฆฌ ๋‚ด๋ถ€์˜ SELECT, FROM, WHERE ๋“ฑ์˜ ํ‚ค์›Œ๋“œ๋Š” ์ด ์ƒˆ๋กœ์šด ๋“ค์—ฌ์“ฐ๊ธฐ ์ˆ˜์ค€์—์„œ ์™ผ์ชฝ ์ •๋ ฌ๋ฉ๋‹ˆ๋‹ค.
  • ๋‹ซ๋Š” ๊ด„ํ˜ธ )๋Š” ์„œ๋ธŒ์ฟผ๋ฆฌ ๋‚ด์šฉ์ด ๋๋‚œ ํ›„ ์ค„์— ์‚ฝ์ž… ํ•ฉ๋‹ˆ๋‹ค.
  • ์„œ๋ธŒ์ฟผ๋ฆฌ๊ฐ€ ๋๋‚œ ํ›„ ์ด์–ด์ง€๋Š” ์™ธ๋ถ€ ์ฟผ๋ฆฌ์˜ ์ ˆ(์˜ˆ: AND, OR, ORDER BY ๋“ฑ)์€ ๊ธฐ์กด ์™ธ๋ถ€ ์ฟผ๋ฆฌ์˜ River ํ๋ฆ„๊ณผ ๋“ค์—ฌ์“ฐ๊ธฐ ์ˆ˜์ค€์œผ๋กœ ๋ณต๊ท€ํ•ฉ๋‹ˆ๋‹ค.

๐Ÿงฉ <include> ๊ตฌ๋ฌธ์˜ ๋ฆฌ๋ฒ„ ์ ์šฉ ๋ฐฉ์‹ โ€‹

โœ… ์˜ˆ์‹œ โ€‹

xml
<select id="selectActiveUsers" resultType="User">
  <!--@formatter:off -->
  SELECT u.user_id
       , u.username
       , u.email
    FROM users AS u
   WHERE u.is_active = TRUE
    <include refId="A">
        <if test="B == B">
            <if test="C == C">
     AND B = B
            </if>
        </if>
    </include>
   ORDER BY u.username ASC
  <!--@formatter:on -->
</select>

WARNING

  • <include> ๋‚˜ <if> ์™€ ๊ฐ™์€ ํƒœ๊ทธ๋“ค์€ xml indent convention ์„ ๋”ฐ๋ฆ…๋‹ˆ๋‹ค (4 blank spaces)
  • ํƒœ๊ทธ์˜ indent ์™€ ์ƒ๊ด€์—†์ด ๋‚ด๋ถ€ ์ฟผ๋ฆฌ๋ฌธ์€ ๊ธฐ์กด river ์— ๋งž๊ฒŒ ์ •๋ ฌํ•ฉ๋‹ˆ๋‹ค.

๐Ÿ’ก UserFilter.xml ๋‚ด๋ถ€ ์˜ˆ์‹œ โ€‹

xml
<sql id="UserFilter">
  AND u.created_at >= #{startDate}
  AND u.created_at <= #{endDate}
</sql>

๋ณ„๋„ ์ •์˜๋œ <sql> ๋ธ”๋ก๋„ ๋ฆฌ๋ฒ„ ์Šคํƒ€์ผ๋กœ ์ž‘์„ฑํ•˜์—ฌ ์ผ๊ด€์„ฑ๊ณผ ๊ฐ€๋…์„ฑ์„ ์œ ์ง€ํ•ฉ๋‹ˆ๋‹ค.