آموزش Join و کوئری‌های ضروری SQL با مثال در SQL Server

حتی اگر هر روز از ORM استفاده می‌کنید، SQL زبانی است که دیتابیس واقعاً با آن صحبت می‌کند. تسلط بر آن کمک می‌کند کوئری‌های تولیدشده توسط EF Core را بخوانید، گزارش‌ها را بهینه بنویسید و مشکلات کارایی را دیباگ کنید. این راهنما کوئری‌هایی را پوشش می‌دهد که بارها و بارها می‌نویسید؛ با نحو SQL Server و یک ساختار ساده‌ی فروشگاهی.

ساختار نمونه

CREATE TABLE Customers (
    Id INT PRIMARY KEY, Name NVARCHAR(100), City NVARCHAR(50)
);

CREATE TABLE Orders (
    Id INT PRIMARY KEY, CustomerId INT NULL REFERENCES Customers(Id),
    OrderDate DATE, Status NVARCHAR(20), Total DECIMAL(18,2)
);

CREATE TABLE OrderItems (
    Id INT PRIMARY KEY, OrderId INT REFERENCES Orders(Id),
    ProductName NVARCHAR(100), Quantity INT, UnitPrice DECIMAL(18,2)
);

ترتیب منطقی اجرای SELECT

شما SELECT را اول می‌نویسید، اما SQL Server بخش‌ها را به این ترتیب ارزیابی می‌کند:

1. FROM / JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. OFFSET / FETCH

این ترتیب خطاهای رایج را توضیح می‌دهد: نمی‌توانید از نام مستعار SELECT در WHERE استفاده کنید (هنوز وجود ندارد)، اما در ORDER BY می‌توانید.

Joinها

Join ردیف‌های دو جدول را بر اساس یک شرط ترکیب می‌کند.

INNER JOIN — فقط ردیف‌های منطبق

SELECT c.Name, o.Id AS OrderId, o.Total
FROM Customers c
INNER JOIN Orders o ON o.CustomerId = c.Id;

مشتریان بدون سفارش و سفارش‌های بدون مشتری حذف می‌شوند.

LEFT JOIN — همه‌ی ردیف‌های چپ، منطبق‌های راست

SELECT c.Name, COUNT(o.Id) AS OrderCount
FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id
GROUP BY c.Name;

مشتریان بدون سفارش با OrderCount = 0 نمایش داده می‌شوند (به COUNT(o.Id) دقت کنید، نه COUNT(*) که عدد ۱ می‌شمارد).

یک کاربرد کلاسیک — پیدا کردن ردیف‌های بدون تطابق:

-- مشتریانی که هرگز سفارش نداده‌اند
SELECT c.*
FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id
WHERE o.Id IS NULL;

هشدار: گذاشتن شرط روی جدول سمت راست در WHERE، یک LEFT JOIN را به INNER JOIN تبدیل می‌کند. جدول سمت راست را در بخش ON فیلتر کنید: LEFT JOIN Orders o ON o.CustomerId = c.Id AND o.Status = 'Paid'

RIGHT و FULL OUTER JOIN

• RIGHT JOIN قرینه‌ی LEFT JOIN است و معمولاً برای خوانایی به LEFT JOIN بازنویسی می‌شود.
• FULL OUTER JOIN ردیف‌های بدون تطابق را از هر دو طرف نگه می‌دارد؛ مناسب گزارش‌های مغایرت‌گیری.

چند Join

SELECT c.Name, o.OrderDate, i.ProductName, i.Quantity * i.UnitPrice AS LineTotal
FROM Orders o
JOIN Customers  c ON c.Id = o.CustomerId
JOIN OrderItems i ON i.OrderId = o.Id
WHERE o.OrderDate >= '2026-01-01';

تجمیع با GROUP BY و HAVING

SELECT c.City,
       COUNT(DISTINCT c.Id) AS Customers,
       COUNT(o.Id)          AS Orders,
       SUM(o.Total)         AS Revenue,
       AVG(o.Total)         AS AvgOrder
FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id AND o.Status = 'Paid'
GROUP BY c.City
HAVING SUM(o.Total) > 10000000
ORDER BY Revenue DESC;

• WHERE ردیف‌ها را قبل از گروه‌بندی فیلتر می‌کند.
• HAVING گروه‌ها را بعد از تجمیع فیلتر می‌کند.
• هر ستون غیرتجمیعی در SELECT باید در GROUP BY هم بیاید.

زیرکوئری و EXISTS

-- سفارش‌های بالاتر از میانگین
SELECT Id, Total
FROM Orders
WHERE Total > (SELECT AVG(Total) FROM Orders);

-- مشتریانی که یک محصول خاص خریده‌اند
SELECT c.Name
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    JOIN OrderItems i ON i.OrderId = o.Id
    WHERE o.CustomerId = c.Id AND i.ProductName = N'لپ‌تاپ'
);

EXISTS با اولین تطابق متوقف می‌شود و با NULL امن کار می‌کند. NOT EXISTS را به NOT IN ترجیح دهید؛ اگر زیرکوئری حتی یک NULL داشته باشد، NOT IN هیچ ردیفی برنمی‌گرداند.

CTE (Common Table Expression)

CTE به یک نتیجه‌ی میانی نام می‌دهد و کوئری‌های پیچیده را خوانا می‌کند:

WITH CustomerTotals AS (
    SELECT CustomerId, SUM(Total) AS Spent
    FROM Orders
    WHERE Status = 'Paid'
    GROUP BY CustomerId
)
SELECT c.Name, ct.Spent
FROM CustomerTotals ct
JOIN Customers c ON c.Id = ct.CustomerId
WHERE ct.Spent > 5000000
ORDER BY ct.Spent DESC;

CTEهای بازگشتی ساختارهای سلسله‌مراتبی مثل دسته‌بندی‌ها یا چارت سازمانی را پیمایش می‌کنند:

WITH CategoryTree AS (
    SELECT Id, Name, ParentId, 0 AS Depth FROM Categories WHERE ParentId IS NULL
    UNION ALL
    SELECT c.Id, c.Name, c.ParentId, t.Depth + 1
    FROM Categories c JOIN CategoryTree t ON c.ParentId = t.Id
)
SELECT * FROM CategoryTree ORDER BY Depth, Name;

توابع پنجره‌ای (Window Functions)

توابع پنجره‌ای روی ردیف‌های مرتبط محاسبه می‌کنند، بدون اینکه مثل GROUP BY آن‌ها را ادغام کنند.

SELECT
    o.CustomerId,
    o.Id,
    o.Total,
    ROW_NUMBER() OVER (PARTITION BY o.CustomerId ORDER BY o.OrderDate DESC) AS RecentRank,
    SUM(o.Total) OVER (PARTITION BY o.CustomerId)                          AS CustomerTotal,
    SUM(o.Total) OVER (ORDER BY o.OrderDate ROWS UNBOUNDED PRECEDING)      AS RunningTotal,
    LAG(o.Total) OVER (PARTITION BY o.CustomerId ORDER BY o.OrderDate)     AS PreviousOrder
FROM Orders o;

کوئری معروف «آخرین سفارش هر مشتری»:

WITH Ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS rn
    FROM Orders
)
SELECT * FROM Ranked WHERE rn = 1;

صفحه‌بندی

SELECT Id, OrderDate, Total
FROM Orders
ORDER BY OrderDate DESC, Id DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;   -- صفحه‌ی ۳ با ۲۰ ردیف

OFFSET به ORDER BY نیاز دارد و ترتیب باید قطعی باشد (یک ستون یکتا مثل Id اضافه کنید)، وگرنه ممکن است ردیفی در دو صفحه ظاهر شود. برای صفحات عمیق در جدول‌های بزرگ، صفحه‌بندی Keyset (WHERE Id < @lastId) بسیار سریع‌تر است.

کار درست با NULL

• NULL = NULL درست نیست — از IS NULL / IS NOT NULL استفاده کنید.
• توابع تجمیعی NULL را نادیده می‌گیرند: AVG(Discount) فقط مقادیر غیر NULL را میانگین می‌گیرد.
• COALESCE(Discount, 0) مقدار NULL را با مقدار پیش‌فرض جایگزین می‌کند.

تغییر امن داده‌ها

BEGIN TRANSACTION;

UPDATE Orders
SET Status = 'Cancelled'
OUTPUT deleted.Status AS OldStatus, inserted.Id
WHERE Status = 'Pending' AND OrderDate < DATEADD(DAY, -30, GETDATE());

-- ردیف‌های تغییرکرده را بررسی کنید، سپس:
COMMIT;   -- یا ROLLBACK;

همیشه قبل از UPDATE یا DELETE، شرط WHERE را اول با SELECT اجرا کنید.

جمع‌بندی

ترتیب منطقی ارزیابی کوئری را بشناسید، Join درست را انتخاب کنید (و فیلتر LEFT JOIN را در ON بگذارید)، با GROUP BY/HAVING تجمیع کنید، برای بررسی وجود از EXISTS استفاده کنید، CTE را برای خوانایی و توابع پنجره‌ای را برای رتبه‌بندی و جمع تجمعی به کار ببرید و با یک ORDER BY قطعی صفحه‌بندی کنید. این الگوها بیشتر نیازهای گزارش‌گیری و دسترسی به داده در پروژه‌های واقعی را پوشش می‌دهند.

پرسش‌های متداول

آیا JOIN از زیرکوئری سریع‌تر است؟

اغلب بهینه‌ساز برای هر دو یک Plan یکسان تولید می‌کند. اول خواناترین کوئری را بنویسید و اگر کارایی مهم بود، Execution Planها را مقایسه کنید.

تفاوت UNION و UNION ALL چیست؟

UNION تکراری‌ها را حذف می‌کند (کار اضافه) و UNION ALL همه را نگه می‌دارد و سریع‌تر است. مگر اینکه به حذف تکرار نیاز دارید، از UNION ALL استفاده کنید.

چطور SQL تولیدشده توسط EF Core را ببینم؟

روی یک IQueryable متد .ToQueryString() را صدا بزنید یا لاگ را با options.LogTo(Console.WriteLine) فعال کنید.