{"id":498,"date":"2026-05-17T07:03:10","date_gmt":"2026-05-16T23:03:10","guid":{"rendered":"https:\/\/junai.ai\/blog\/pg-cte-subquery-10\/"},"modified":"2026-05-17T07:03:10","modified_gmt":"2026-05-16T23:03:10","slug":"pg-cte-subquery-10","status":"publish","type":"post","link":"https:\/\/junai.ai\/blog\/pg-cte-subquery-10\/","title":{"rendered":"\uc11c\ube0c\ucffc\ub9ac\uc640 CTE(WITH) \u2014 \uac00\ub3c5\uc131 \uc88b\uc740 \ucffc\ub9ac (\uae30\ucd08 10\ud3b8)"},"content":{"rendered":"\n<!-- WordPress REST API \ubc1c\ud589\uc6a9 HTML (\uc790\ub3d9 \uc0dd\uc131) -->\n<!-- WP-FEATURED-MEDIA-ID: 284 -->\n<div style=\"max-width:800px;margin:0 auto;\">\n<style>\n:root{--color-primary:#336791;--color-accent:#60a5fa;--color-bg:#fafafa;--color-bg-card:#ffffff;--color-text:#1e293b;--color-text-muted:#64748b;--hero-start:#0f172a;--hero-end:#336791;--font-body:-apple-system,BlinkMacSystemFont,'Apple SD Gothic Neo','Noto Sans KR',sans-serif;--size-body:17px;--line-height:1.75;}\n*{box-sizing:border-box;}\n.container{max-width:760px;margin:0 auto;padding:0 22px 80px;}\n.hero{background:linear-gradient(135deg,var(--hero-start) 0%,var(--hero-end) 100%);color:#fff;padding:72px 22px 56px;text-align:center;}\n.hero .badge{display:inline-block;background:rgba(96,165,250,0.18);color:var(--color-accent);padding:6px 14px;border-radius:999px;font-size:13px;font-weight:600;letter-spacing:0.5px;margin-bottom:18px;}\n.hero h1{margin:0 0 18px;font-size:36px;line-height:1.3;letter-spacing:-0.3px;}\n.hero p.sub{margin:0 auto;max-width:580px;font-size:17px;color:#bfdbfe;}\n.hero img{width:100%;max-width:720px;height:auto;margin:36px auto 0;display:block;border-radius:10px;box-shadow:0 8px 32px rgba(0,0,0,0.3);}\n.meta{display:flex;gap:14px;justify-content:center;margin-top:20px;font-size:13px;color:#93c5fd;flex-wrap:wrap;}\n.meta span::before{content:\"\u00b7\";margin-right:14px;color:#1e3a8a;}\n.meta span:first-child::before{content:\"\";margin:0;}\narticle{background:var(--color-bg-card);margin-top:-36px;padding:44px 28px;border-radius:14px;box-shadow:0 2px 18px rgba(0,0,0,0.06);}\narticle p{margin:0 0 18px;}\nh2{font-size:28px;line-height:1.35;letter-spacing:-0.3px;margin:48px 0 18px;padding-bottom:10px;border-bottom:2px solid var(--color-primary);}\nh2:first-of-type{margin-top:8px;}\nh3{font-size:21px;line-height:1.4;margin:30px 0 12px;color:var(--color-primary);}\ncode{background:#f1f5f9;color:#0f172a;padding:2px 6px;border-radius:4px;font-family:'SFMono-Regular',Menlo,Consolas,monospace;font-size:0.92em;}\npre{background:#0f172a;color:#e2e8f0;padding:18px 20px;border-radius:10px;overflow-x:auto;font-size:14.5px;line-height:1.65;margin:18px 0;}\npre code{background:transparent;color:inherit;padding:0;}\nul,ol{margin:0 0 18px;padding-left:24px;}\nli{margin-bottom:8px;}\nstrong{color:#0f172a;}\n.databox{background:#eff6ff;border-left:4px solid var(--color-primary);padding:18px 20px;border-radius:6px;margin:22px 0;}\n.databox p{margin:0 0 8px;}\n.databox p:last-child{margin:0;}\n.databox strong{color:var(--color-primary);}\n.warnbox{background:linear-gradient(135deg,#fef3c7 0%,#fde68a 100%);border-left:4px solid #d97706;padding:18px 20px;border-radius:6px;margin:22px 0;}\n.warnbox strong{color:#92400e;}\n.tablewrap{overflow-x:auto;-webkit-overflow-scrolling:touch;margin:22px 0;}\ntable{width:100%;border-collapse:collapse;font-size:15px;background:var(--color-bg-card);}\nth,td{padding:11px 12px;text-align:left;border-bottom:1px solid #e2e8f0;vertical-align:top;}\nth{background:#f1f5f9;font-weight:700;color:#0f172a;}\ntd:first-child,th:first-child{font-weight:700;}\n.cta{background:linear-gradient(135deg,#336791 0%,#60a5fa 100%);color:#fff;padding:30px 24px;border-radius:12px;margin-top:44px;text-align:center;}\n.cta h3{color:#fff;margin:0 0 10px;}\n.cta p{margin:0;color:#dbeafe;}\n.series-nav{background:#eff6ff;padding:18px 22px;border-radius:10px;margin-top:24px;font-size:14.5px;color:var(--color-text-muted);}\n.series-nav strong{color:var(--color-primary);}\n@media (max-width:480px){.hero{padding:52px 18px 44px;}.hero h1{font-size:26px;}.hero p.sub{font-size:15px;}article{padding:28px 18px;border-radius:10px;}h2{font-size:22px;}h3{font-size:18px;}body{font-size:16px;}pre{font-size:13px;padding:14px 16px;}}\n@media (max-width:560px){.tablewrap table,.tablewrap thead,.tablewrap tbody,.tablewrap tr,.tablewrap th,.tablewrap td{display:block;width:auto;}.tablewrap thead{display:none;}.tablewrap tr{margin:0 0 14px;border:1px solid #e2e8f0;border-radius:10px;overflow:hidden;}.tablewrap td{border:none;border-bottom:1px solid #f1f5f9;padding:9px 14px;}.tablewrap td:first-child{background:#eff6ff;font-weight:800;font-size:15.5px;}.tablewrap td:last-child{border-bottom:none;}.tablewrap td[data-label]::before{content:attr(data-label) \" \u2014 \";font-weight:700;color:var(--color-primary);}}\n<\/style>\n<header class=\"hero\">\n  <span class=\"badge\">PostgreSQL \uad50\uc7ac \u00b7 10\ud3b8 \/ 24\ud3b8<\/span>\n  <h1>\uc11c\ube0c\ucffc\ub9ac\uc640 CTE(WITH) \u2014 \uac00\ub3c5\uc131 \uc88b\uc740 \ucffc\ub9ac<\/h1>\n  <p class=\"sub\">\uc911\ucca9 SELECT \uc640 WITH \uc808. \uc7ac\uadc0 CTE \ud55c \uc904\ub85c \ud2b8\ub9ac \ub2e4\ub8e8\uae30.<\/p>\n  <div class=\"meta\"><span>SQL \uae30\ucd08<\/span><span>\uc77d\ub294 \uc2dc\uac04 8\ubd84<\/span><span>2026-05-17<\/span><\/div>\n  <img decoding=\"async\" src=\"https:\/\/junai.ai\/blog\/wp-content\/uploads\/2026\/05\/hero-63.jpg\" alt=\"WITH \uc808\ub85c \ubd84\ud574\ub41c \ucffc\ub9ac \uad6c\uc870\uac00 \ub2e8\uacc4\ubcc4 \ube14\ub85d\uc73c\ub85c \ud45c\ud604\ub41c \ub3c4\uc2dd\">\n<\/header>\n\n<div class=\"container\">\n<article>\n\n<p>JOIN \ub9cc\uc73c\ub85c \uc548 \ub418\ub294 \ucffc\ub9ac\uac00 \ub4f1\uc7a5\ud558\uae30 \uc2dc\uc791\ud569\ub2c8\ub2e4 \u2014 &#8220;\uac01 \uce74\ud14c\uace0\ub9ac\uc5d0\uc11c \ub9e4\ucd9c 1\uc704 \uc0c1\ud488&#8221; \uac19\uc740 \uac83. \ub2f5\uc740 <strong>\uc11c\ube0c\ucffc\ub9ac<\/strong>(\ucffc\ub9ac \uc548\uc758 \ucffc\ub9ac) \uc640 <strong>CTE<\/strong>(\uac00\ub3c5\uc131 \uc88b\uc740 \uba85\uba85 \ucffc\ub9ac). 10\ud3b8\uc740 \ub458\uc758 \ud615\ud0dc\uc640 \uc5b8\uc81c \ubb34\uc5c7\uc744 \uc4f8\uc9c0 \uc815\ub9ac\ud558\uace0, \uc7ac\uadc0 CTE \uae4c\uc9c0 \ubbf8\ub9ac \ubd05\ub2c8\ub2e4.<\/p>\n\n<h2>\uc11c\ube0c\ucffc\ub9ac \u2014 \ud615\ud0dc\uc5d0 \ub530\ub77c 3\uac00\uc9c0<\/h2>\n\n<h3>\u2460 scalar \uc11c\ube0c\ucffc\ub9ac \u2014 \ub2e8\uc77c \uac12<\/h3>\n\n<pre><code>-- WHERE \uc548\uc5d0\uc11c\nSELECT * FROM orders\nWHERE total &gt; (SELECT AVG(total) FROM orders);\n\n-- SELECT \uc548\uc5d0\uc11c (\uac01 \ud589\ub9c8\ub2e4 \uc2e4\ud589\ub428 \u2014 \ube44\uc2f8\uae30 \uc26c\uc6c0)\nSELECT o.id,\n       o.total,\n       (SELECT name FROM users WHERE id = o.user_id) AS user_name\nFROM   orders o;<\/code><\/pre>\n\n<h3>\u2461 row \uc11c\ube0c\ucffc\ub9ac \u2014 \ud55c \ud589, \uc5ec\ub7ec \uceec\ub7fc<\/h3>\n\n<pre><code>SELECT * FROM orders\nWHERE (user_id, total) = (SELECT id, max_total FROM ...);<\/code><\/pre>\n\n<h3>\u2462 table \uc11c\ube0c\ucffc\ub9ac \u2014 \uc5ec\ub7ec \ud589 + \uc5ec\ub7ec \uceec\ub7fc (FROM \uc808\uc5d0)<\/h3>\n\n<pre><code>-- \uce74\ud14c\uace0\ub9ac\ubcc4 \ub9e4\ucd9c 1\uc704\nSELECT *\nFROM   (\n  SELECT category_id, name, total,\n         ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY total DESC) AS rn\n  FROM   products\n) ranked\nWHERE  rn = 1;<\/code><\/pre>\n\n<h2>IN \u00b7 EXISTS \u00b7 NOT EXISTS<\/h2>\n\n<pre><code>-- IN (\uac12 \ube44\uad50)\nSELECT * FROM orders\nWHERE user_id IN (SELECT id FROM users WHERE active = true);\n\n-- EXISTS (\uc874\uc7ac\ub9cc \ud655\uc778 \u2014 \ub9e4\uce58 \uc989\uc2dc \uba48\ucda4, \ubcf4\ud1b5 \ub354 \ube60\ub984)\nSELECT * FROM orders o\nWHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.active);\n\n-- NOT EXISTS (anti-join, NULL \ud568\uc815 \ud68c\ud53c)\nSELECT * FROM users u\nWHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);<\/code><\/pre>\n\n<div class=\"warnbox\">\n  <p><strong>NOT IN \uc758 NULL \ud568\uc815 (6\ud3b8 \ub2e4\uc2dc).<\/strong> <code>WHERE x NOT IN (subquery)<\/code> \uc758 subquery \uacb0\uacfc\uc5d0 NULL \uc774 \ud558\ub098\ub77c\ub3c4 \uc788\uc73c\uba74 \uc804\uccb4 \uacb0\uacfc\uac00 \ube48 \ud589. <code>NOT EXISTS<\/code> \ub294 NULL \uc601\ud5a5 \uc5c6\uc74c \u2014 <strong>&#8220;\uc81c\uc678&#8221; \uc758\ub3c4\uba74 NOT EXISTS \uac00 \uc548\uc804<\/strong>.<\/p>\n<\/div>\n\n<h2>WITH (CTE) \u2014 \uba85\uba85\ub41c \uc784\uc2dc \ucffc\ub9ac<\/h2>\n\n<pre><code>WITH active_users AS (\n  SELECT id, name FROM users WHERE active = true\n),\nbig_orders AS (\n  SELECT user_id, SUM(total) AS rev\n  FROM   orders\n  WHERE  total &gt;= 50000\n  GROUP BY user_id\n)\nSELECT u.name, COALESCE(o.rev, 0) AS rev\nFROM   active_users u\nLEFT JOIN big_orders o ON o.user_id = u.id\nORDER BY rev DESC;<\/code><\/pre>\n\n<p>\uac19\uc740 \uacb0\uacfc\ub97c \uc11c\ube0c\ucffc\ub9ac\ub85c \uc4f0\uba74 \uc911\ucca9\uc774 \uae4a\uc5b4\uc9d1\ub2c8\ub2e4. WITH \ub85c \ubd84\ud574\ud558\uba74 <strong>\uc704\uc5d0\uc11c \uc544\ub798\ub85c<\/strong> \uc77d\ud798. \ud070 \ucffc\ub9ac\ub294 \uac70\uc758 \ud56d\uc0c1 WITH \ub85c \uad6c\uc870\ub97c \uc7a1\ub294 \uac8c \uac00\ub3c5\uc131 \uc815\ub2f5.<\/p>\n\n<div class=\"databox\">\n  <p><strong>CTE \uc758 \uc131\ub2a5 (Postgres 12+).<\/strong> \uc61b Postgres \ub294 CTE \uac00 &#8220;\ucd5c\uc801\ud654 \uc7a5\ubcbd&#8221; \uc774\ub77c \ub290\ub838\uc9c0\ub9cc, 12\ubd80\ud130\ub294 <strong>\uc778\ub77c\uc778 \ucd5c\uc801\ud654<\/strong> \uac00 \uae30\ubcf8\uc774\ub77c \uc77c\ubc18 \uc11c\ube0c\ucffc\ub9ac\uc640 \ube44\uc2b7\ud55c \uc131\ub2a5. \uac00\ub3c5\uc131 \ube44\uc6a9 \uac70\uc758 \uc5c6\uc774 \ubd84\ud574\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \uc77c\ubd80\ub7ec \uc7a5\ubcbd \ub9cc\ub4e4\uace0 \uc2f6\uc73c\uba74 <code>WITH x AS MATERIALIZED (...)<\/code>.<\/p>\n<\/div>\n\n<h2>\uc5ec\ub7ec CTE \uccb4\uc774\ub2dd \u2014 \ub2e8\uacc4\ubcc4 \ub370\uc774\ud130 \ubcc0\ud658<\/h2>\n\n<pre><code>-- \ubd84\uc11d \ud30c\uc774\ud504\ub77c\uc778 \u2014 \ub2e8\uacc4\ubcc4 \uba85\ud655\nWITH raw AS (\n  SELECT user_id, total, created_at\n  FROM   orders\n  WHERE  created_at &gt;= '2026-01-01'\n),\nby_month AS (\n  SELECT user_id,\n         date_trunc('month', created_at) AS month,\n         SUM(total) AS rev\n  FROM   raw\n  GROUP BY user_id, date_trunc('month', created_at)\n),\ntop_per_month AS (\n  SELECT *,\n         RANK() OVER (PARTITION BY month ORDER BY rev DESC) AS rk\n  FROM   by_month\n)\nSELECT month, user_id, rev\nFROM   top_per_month\nWHERE  rk &lt;= 3\nORDER BY month, rk;<\/code><\/pre>\n\n<h2>\uc7ac\uadc0 CTE \u2014 \ud2b8\ub9ac\u00b7\uacc4\uce35 \uad6c\uc870<\/h2>\n\n<pre><code>-- \uc9c1\uc6d0-\ub9e4\ub2c8\uc800 \ud2b8\ub9ac, \"\uc900\uc131\" \uc758 \ubaa8\ub4e0 \ubd80\ud558 \uc9c1\uc6d0 (\uac04\uc811 \ud3ec\ud568)\nWITH RECURSIVE subordinates AS (\n  -- 1) \uc2dc\uc791\uc810\n  SELECT id, name, manager_id, 1 AS depth\n  FROM   employees\n  WHERE  name = '\uc900\uc131'\n\n  UNION ALL\n\n  -- 2) \uc7ac\uadc0 \u2014 \uc9c1\uc804 \uacb0\uacfc\uc758 \ubd80\ud558 \uc9c1\uc6d0\n  SELECT e.id, e.name, e.manager_id, s.depth + 1\n  FROM   employees e\n  JOIN   subordinates s ON e.manager_id = s.id\n)\nSELECT * FROM subordinates ORDER BY depth, name;<\/code><\/pre>\n\n<p>\uc7ac\uadc0 CTE \uc758 \ud328\ud134: <strong>(anchor) UNION ALL (recursion)<\/strong>. \ud2b8\ub9ac\u00b7\uadf8\ub798\ud504\u00b7\ubc88\ud638 \uc2dc\ud000\uc2a4 \uc0dd\uc131 \ub4f1\uc5d0 \uc0ac\uc6a9. \ubb34\ud55c \ub8e8\ud504 \uc704\ud5d8 \u2014 \ud56d\uc0c1 <code>depth &lt; N<\/code> \uac19\uc740 \uc870\uac74\uc774\ub098 \ub370\uc774\ud130\uc5d0 \uc0ac\uc774\ud074 \uc5c6\ub294 \uac8c \ubcf4\uc7a5\ub3fc\uc57c \uc548\uc804.<\/p>\n\n<h2>generate_series \u2014 \uc22b\uc790\/\ub0a0\uc9dc \uc2dc\ud000\uc2a4<\/h2>\n\n<pre><code>-- 1\ubd80\ud130 10\uae4c\uc9c0\nSELECT * FROM generate_series(1, 10);\n\n-- \ub0a0\uc9dc (\uc6d4\ubcc4)\nSELECT * FROM generate_series(\n  '2026-01-01'::date,\n  '2026-12-31'::date,\n  '1 month'::interval\n);\n\n-- \"\uc9c0\ub09c 30\uc77c \ub9e4\uc77c \ub9e4\ucd9c\" \u2014 \ub370\uc774\ud130 \uc5c6\ub294 \ub0a0\ub3c4 0 \uc73c\ub85c \ucc44\uc6b0\uae30\nWITH days AS (\n  SELECT generate_series(\n    CURRENT_DATE - INTERVAL '29 days',\n    CURRENT_DATE,\n    '1 day'::interval\n  )::date AS d\n)\nSELECT d.d, COALESCE(SUM(o.total), 0) AS rev\nFROM   days d\nLEFT JOIN orders o ON o.created_at::date = d.d\nGROUP BY d.d\nORDER BY d.d;<\/code><\/pre>\n\n<h2>\uc11c\ube0c\ucffc\ub9ac vs CTE vs JOIN \u2014 \uc120\ud0dd \uac00\uc774\ub4dc<\/h2>\n\n<div class=\"tablewrap\">\n<table>\n  <thead>\n    <tr><th>\uc0c1\ud669<\/th><th>\ucd94\ucc9c<\/th><\/tr>\n  <\/thead>\n  <tbody>\n    <tr><td data-label=\"\uc0c1\ud669\">\ub2e8\uc77c \uac12 \ube44\uad50 (WHERE)<\/td><td data-label=\"\ucd94\ucc9c\">scalar \uc11c\ube0c\ucffc\ub9ac<\/td><\/tr>\n    <tr><td data-label=\"\uc0c1\ud669\">&#8220;\uc788\ub294\uc9c0&#8221; \ud655\uc778<\/td><td data-label=\"\ucd94\ucc9c\">EXISTS \/ NOT EXISTS<\/td><\/tr>\n    <tr><td data-label=\"\uc0c1\ud669\">&#8220;\uc774 \uc911 \ud558\ub098&#8221; (\uac12 \ubaa9\ub85d)<\/td><td data-label=\"\ucd94\ucc9c\">IN (\ub2e8, NULL \uc8fc\uc758)<\/td><\/tr>\n    <tr><td data-label=\"\uc0c1\ud669\">\ub370\uc774\ud130 \ud569\uce58\uae30 + \uceec\ub7fc<\/td><td data-label=\"\ucd94\ucc9c\">JOIN<\/td><\/tr>\n    <tr><td data-label=\"\uc0c1\ud669\">\ub2e8\uacc4\uac00 3\uac1c \uc774\uc0c1<\/td><td data-label=\"\ucd94\ucc9c\">WITH (CTE)<\/td><\/tr>\n    <tr><td data-label=\"\uc0c1\ud669\">\ud2b8\ub9ac\u00b7\uc7ac\uadc0<\/td><td data-label=\"\ucd94\ucc9c\">WITH RECURSIVE<\/td><\/tr>\n    <tr><td data-label=\"\uc0c1\ud669\">\ubc88\ud638\/\ub0a0\uc9dc \uc2dc\ud000\uc2a4<\/td><td data-label=\"\ucd94\ucc9c\">generate_series<\/td><\/tr>\n  <\/tbody>\n<\/table>\n<\/div>\n\n<div class=\"cta\">\n  <h3>11\ud3b8 \u2014 \uc708\ub3c4\uc6b0 \ud568\uc218 (OVER\u00b7PARTITION\u00b7ROW_NUMBER)<\/h3>\n  <p>ROW_NUMBER\u00b7RANK\u00b7LAG\u00b7LEAD \u2014 \uac19\uc740 \uacb0\uacfc\uc5d0\uc11c \ud589 \ub2e8\uc704 \ubd84\uc11d.<\/p>\n<\/div>\n\n<div class=\"series-nav\">\n  <strong>\ud83d\udcda PostgreSQL \ubc30\uc6b0\uae30 \uad50\uc7ac<\/strong><br>\n  \uc774\uc804: 9\ud3b8 JOIN \u00b7 \ud604\uc7ac: <strong>10\ud3b8 (SQL \uae30\ucd08)<\/strong> \u00b7 \ub2e4\uc74c \u2192 11\ud3b8 \uc708\ub3c4\uc6b0 \ud568\uc218 \u00b7 \uc9c4\ud589: <strong>10\/24<\/strong>\n<\/div>\n\n<\/article>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>\uc11c\ube0c\ucffc\ub9ac\u00b7CTE\u00b7\uc7ac\uadc0 CTE \uc815\ub9ac. 24\ud3b8 \uad50\uc7ac 10\ud3b8.<\/p>\n","protected":false},"author":1,"featured_media":284,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19],"tags":[],"class_list":["post-498","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/posts\/498","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/comments?post=498"}],"version-history":[{"count":0,"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/posts\/498\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/media\/284"}],"wp:attachment":[{"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/media?parent=498"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/categories?post=498"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/tags?post=498"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}