🗄 10 قابلیت کمترشناخته‌شده SQL که هر Developer باید بداند(پارت 1️⃣)

بیشتر Developerها احتمالاً فقط از حدود 20 درصد قابلیت‌های SQL استفاده می‌کنند.
آن‌ها SELECT، JOIN و GROUP BY می‌نویسند و همان‌جا متوقف می‌شوند.
اما SQL یک لایه‌ی دوم هم دارد؛ قابلیت‌هایی که می‌توانند یک صفحه کد Application یا سه Query جداگانه را به یک Statement تمیز و یکپارچه تبدیل کنند.⚡

ءDeveloperهای Senior همیشه به سراغ این قابلیت‌ها می‌روند. بسیاری از Developerهای Junior و Mid-level حتی یک‌بار هم آن‌ها را ندیده‌اند.
هیچ‌کدام از این قابلیت‌ها جدید یا عجیب‌وغریب نیستند. آن‌ها همین حالا در Databaseای که استفاده می‌کنید وجود دارند و منتظرند تا از آن‌ها استفاده کنید.
🚀امروز می‌خواهم ۱۰ قابلیت کمترشناخته‌شده‌ی SQL را به شما نشان بدهم که هر Developer باید آن‌ها را بشناسد.

📌در این مطلب، موارد زیر را بررسی می‌کنیم:

🔸️Common Table Expressions یا CTE
🔹️Window Functions
🔸️LATERAL Joins
🔹️GROUPING SETS، ROLLUP و CUBE
🔸️عبارت FILTER در Aggregateها
🔹️UPSERT با استفاده از INSERT ... ON 🔸️CONFLICT
🔹️پشتیبانی از JSON
🔸️Computed / Generated Columns
🔹️TABLESAMPLE
🔸️Partial Indexes
بریم سراغشون. 🚀

تمام Queryهای این مطلب روی Database
ءPostgreSQL تست شده‌اند. بیشتر این قابلیت‌ها در Databaseهای دیگر نیز وجود دارند، اما Syntax دقیق آن‌ها ممکن است متفاوت باشد؛ در طول مطلب به تفاوت‌های اصلی اشاره می‌کنم.

1️⃣ ءCommon Table Expressions یا CTE

یک Query پیچیده که همه‌چیز در یک Statement داخل آن فشرده شده باشد، خواندنش سخت و تغییر دادنش حتی سخت‌تر است.
ءCommon Table Expression یا همان CTE به شما اجازه می‌دهد با استفاده از Keyword مربوط به WITH، آن Query را به چند مرحله‌ی نام‌گذاری‌شده و پشت‌سرهم تقسیم کنید.🧩
هر مرحله مانند یک Result موقت و نام‌گذاری‌شده است که می‌توانید در مراحل بعدی روی آن کار کنید.

WITH recent_shipments AS (
    SELECT
        s.id,
        s.number,
        s.carrier,
        s.status,
        s.created_at
    FROM shipments.shipments s
    WHERE s.created_at >= CURRENT_DATE - INTERVAL '30 days'
),
shipment_details AS (
    SELECT
        rs.number,
        rs.carrier,
        rs.status,
        COUNT(si.id) AS total_items,
        SUM(si.quantity) AS total_quantity
    FROM recent_shipments rs
    LEFT JOIN shipments.shipment_items si
        ON rs.id = si.shipment_id
    GROUP BY
        rs.number,
        rs.carrier,
        rs.status
)
SELECT
    number AS shipment_number,
    carrier,
    status,
    total_items,
    total_quantity
FROM shipment_details
ORDER BY total_quantity DESC;

این Query شامل دو بخش نام‌گذاری‌شده است. recent_shipments، Shipmentهایی را که در 30 روز گذشته ایجاد شده‌اند انتخاب می‌کند.📅
سپس shipment_details روی آن Result کار می‌کند، Itemهای مربوط به Shipment را Join می‌کند و تعداد و مقدار آن‌ها را Aggregate می‌کند.
در نهایت، SELECT اصلی از shipment_details می‌خواند؛ درست مثل اینکه با یک Table معمولی کار می‌کند.
مزیت اصلی این است که Query را می‌توانید از بالا به پایین بخوانید؛ درست مثل مراحل یک دستورالعمل، به‌جای اینکه مجبور باشید Nested Subqueryها را از داخل به بیرون دنبال کنید.🧠
ءCTEها از Recursive Query نیز پشتیبانی می‌کنند؛ با استفاده از WITH RECURSIVE.
این قابلیت زمانی بسیار کاربردی است که با داده‌های Hierarchical مانند:
🏢ساختار سازمانی
🌳درخت دسته‌بندی‌ها
📂ءCategory Tree
🔗ساختار Parent/Child
کار می‌کنید.
یک Common Table Expression می‌تواند داخل Statementهای SELECT، INSERT، UPDATE یا DELETE استفاده شود.