فصل ۴: کوئری پیشرفته — JOIN، تجمیع، CTE و Window Function

زیرکوئری، EXISTS، Derived Table، LATERAL و View

کوئری داخل کوئری

زیرکوئری می‌تواند یک مقدار (scalar)، یک فهرست (برای IN)، یک «وجود دارد یا نه» (EXISTS) یا یک جدول موقت (derived table) برگرداند.

-- scalar: محصولات گران‌تر از میانگین
SELECT sku, list_price FROM products
WHERE list_price > (SELECT AVG(list_price) FROM products);

-- EXISTS: مشتری‌هایی که دست‌کم یک سفارش بالای ۵۰۰ میلیون ریال دارند
SELECT c.id, c.full_name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.total_amount > 500000000
);

زیرکوئری دوم «هم‌بسته» (correlated) است: به ستون کوئری بیرونی (c.id) ارجاع می‌دهد. EXISTS به محض پیدا کردن اولین ردیف متوقف می‌شود و مهم نیست داخلش چه چیزی SELECT کنید.

NOT IN در برابر NOT EXISTS

-- محصولاتی که هرگز فروش نرفته‌اند
SELECT p.sku FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id);

اگر ستون زیرکوئری NULL داشته باشد، NOT IN برای همه‌ی ردیف‌ها UNKNOWN می‌شود و نتیجه خالی می‌آید. NOT EXISTS این مشکل را ندارد و MySQL 8 آن را به anti-join بهینه تبدیل می‌کند.

Derived table و LATERAL

-- derived table: میانگین خرید هر مشتری، سپس فقط مشتری‌های بالای میانگین کل
SELECT t.customer_id, t.spent
FROM (SELECT customer_id, SUM(total_amount) AS spent
      FROM orders GROUP BY customer_id) AS t
WHERE t.spent > 1000000000;

-- LATERAL (8.0.14+): سه سفارش آخر هر مشتری
SELECT c.full_name, last3.id, last3.created_at
FROM customers c
JOIN LATERAL (
  SELECT o.id, o.created_at FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.created_at DESC LIMIT 3
) AS last3 ON TRUE;

derived table معمولی نمی‌تواند به جدول‌های کنارش ارجاع دهد؛ LATERAL این اجازه را می‌دهد و برای «N ردیف آخر هر گروه» با ایندکس (customer_id, created_at) بسیار سریع است.

View: کوئری نام‌دار

CREATE OR REPLACE VIEW v_order_summary AS
SELECT o.id, o.created_at, o.status, c.full_name, c.mobile, o.total_amount
FROM orders o JOIN customers c ON c.id = o.customer_id;

SELECT * FROM v_order_summary WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20;

-- View قابل به‌روزرسانی با محافظ
CREATE VIEW v_draft_orders AS
SELECT id, customer_id, status FROM orders WHERE status = 'draft'
WITH CHECK OPTION;   -- UPDATE که ردیف را از شرط View بیرون ببرد رد می‌شود

View داده ذخیره نمی‌کند (MySQL «Materialized View» ندارد). مزیتش پنهان کردن پیچیدگی و محدود کردن دسترسی است: می‌توانید به کاربر گزارش‌گیر فقط روی View دسترسی بدهید و ستون‌های حساس را نشان ندهید.

نکته‌هایی که کمتر کسی می‌داند

  • SELECT * در تعریف View هنگام ساخت به فهرست ثابت ستون‌ها تبدیل می‌شود؛ ستون‌هایی که بعداً به جدول اضافه کنید در View نمی‌آیند تا View را دوباره بسازید.
  • View با ALGORITHM=MERGE در کوئری بیرونی ادغام می‌شود و از ایندکس‌ها استفاده می‌کند؛ اما اگر GROUP BY، DISTINCT، UNION یا LIMIT داشته باشد به TEMPTABLE تبدیل می‌شود و فیلتر بیرونی بعد از ساخت کل نتیجه اعمال می‌شود.
  • View به‌طور پیش‌فرض با SQL SECURITY DEFINER ساخته می‌شود؛ اگر کاربر سازنده حذف شود، View با خطای «The user specified as a definer does not exist» از کار می‌افتد — دردسر رایج بعد از انتقال بکاپ به سرور دیگر.
  • زیرکوئری scalar که بیش از یک ردیف برگرداند، خطای 1242 می‌دهد، اما فقط وقتی داده‌ی تکراری واقعاً پیدا شود؛ یعنی ممکن است ماه‌ها درست کار کند و ناگهان بشکند.
  • بهینه‌ساز MySQL 8 بسیاری از IN (subquery)ها را به semi-join تبدیل می‌کند؛ توصیه‌ی قدیمی «همیشه زیرکوئری را به JOIN تبدیل کن» دیگر قانون نیست — با EXPLAIN بسنجید.
درس قبلی درس بعدی

برای ذخیره‌ی پیشرفت و شرکت در آزمون، وارد شوید — رایگان است.