عملگر NOT IN در SQL Server چالش Null و راهکارهای بهینه سازی

عملگر SQL NOT IN: چالش‌ها و بهینه‌سازی در SQL Server

یکی از عملگرهای رایج در زبان SQL، عملگر NOT IN است که برای فیلتر کردن ردیف‌هایی که مقدار ستون آن‌ها در مجموعه مقادیر مشخصی وجود ندارد، استفاده می‌شود. این عملگر زمانی کاربرد دارد که بخواهید رکوردهایی را که با هیچ یک از مقادیر لیست شده مطابقت ندارند، از مجموعه نتایج خود حذف کنید.

به عنوان مثال، فرض کنید می‌خواهید تمام محصولاتی را مشاهده کنید که در دسته‌های ‘نوشیدنی‌ها’ یا ‘غلات’ قرار ندارند. در این حالت، استفاده از عملگر NOT IN یک راهکار مناسب است.

برای شروع، یک پایگاه داده نمونه و دو جدول به نام‌های Products و Categories ایجاد می‌کنیم و داده‌های نمونه را وارد می‌کنیم تا بتوانیم با آن‌ها کار کنیم.

-- ایجاد پایگاه داده نمونه
CREATE DATABASE SampleDB;
GO

USE SampleDB;
GO

-- ایجاد جدول Categories
CREATE TABLE Categories (
    CategoryId INT PRIMARY KEY,
    CategoryName VARCHAR(50)
);
GO

-- ایجاد جدول Products
CREATE TABLE Products (
    ProductId INT PRIMARY KEY,
    ProductName VARCHAR(100),
    CategoryId INT,
    Price DECIMAL(10, 2)
);
GO

-- درج داده در جدول Categories
INSERT INTO Categories (CategoryId, CategoryName) VALUES
(1, 'Beverages'),
(2, 'Condiments'),
(3, 'Confections'),
(4, 'Dairy Products'),
(5, 'Grains');
GO

-- درج داده در جدول Products
INSERT INTO Products (ProductId, ProductName, CategoryId, Price) VALUES
(1, 'Chai', 1, 18.00),
(2, 'Chang', 1, 19.00),
(3, 'Aniseed Syrup', 2, 10.00),
(4, 'Chef Anton''s Cajun Seasoning', 2, 22.00),
(5, 'Genen Shouyu', 2, 15.50),
(6, 'Pavlova', 3, 17.45),
(7, 'Chocolade', 3, 12.75),
(8, 'Schoggi Schokolade', 3, 43.90),
(9, 'Gudbrandsdalen', 4, 36.00),
(10, 'Geitost', 4, 25.00);
GO

حالا، فرض کنید می‌خواهیم نام محصولاتی را پیدا کنیم که در دسته ‘نوشیدنی‌ها’ (Beverages) قرار ندارند. می‌توانیم از NOT IN به همراه یک ساب‌کوئری (Subquery) استفاده کنیم. این ساب‌کوئری CategoryId مربوط به دسته ‘نوشیدنی‌ها’ را برمی‌گرداند و NOT IN اطمینان حاصل می‌کند که فقط محصولاتی که CategoryId آن‌ها در این لیست نباشد، انتخاب شوند.

SELECT ProductName
FROM Products
WHERE CategoryId NOT IN (SELECT CategoryId FROM Categories WHERE CategoryName = 'Beverages');

نتیجه این کوئری، محصولاتی مانند Aniseed Syrup, Chef Anton’s Cajun Seasoning, Genen Shouyu, Pavlova, Chocolade, Schoggi Schokolade, Gudbrandsdalen, Geitost خواهد بود که نشان می‌دهد محصولاتی که در دسته ‘نوشیدنی‌ها’ نبوده‌اند، به درستی فیلتر شده‌اند.

عملگر NOT IN به خصوص زمانی که با مقادیر NULL در ساب‌کوئری‌ها روبرو می‌شود، می‌تواند رفتار غیرمنتظره‌ای از خود نشان دهد. این یکی از چالش‌های اصلی استفاده از آن است. اگر ساب‌کوئری شما حداقل یک مقدار NULL را برگرداند، کل عبارت NOT IN در هر ردیف به UNKNOWN ارزیابی می‌شود و در نتیجه هیچ ردیفی برگردانده نمی‌شود.

برای درک بهتر این موضوع، یک مقدار NULL را به جدول Categories اضافه می‌کنیم.

INSERT INTO Categories (CategoryId, CategoryName) VALUES
(6, NULL);
GO

حالا، اگر دوباره همان کوئری قبلی را اجرا کنیم، متوجه می‌شویم که هیچ ردیفی برگردانده نمی‌شود. دلیل این امر این است که لیست مقادیر ساب‌کوئری شامل NULL شده است.

SELECT ProductName
FROM Products
WHERE CategoryId NOT IN (SELECT CategoryId FROM Categories WHERE CategoryName = 'Beverages');

این رفتار به دلیل منطق سه حالته SQL (True, False, Unknown) است. وقتی SQL مقداری را با NULL مقایسه می‌کند، نتیجه همیشه UNKNOWN است. عبارت NOT IN (…, NULL, …) به معنای مقایسه X != Y AND X != Z AND X != NULL است. از آنجایی که X != NULL همیشه UNKNOWN است، کل عبارت WHERE به UNKNOWN تبدیل می‌شود و هیچ ردیفی انتخاب نمی‌گردد.

برای رفع این مشکل و اطمینان از عملکرد صحیح NOT IN در حضور NULL، دو راهکار اصلی وجود دارد: 1. فیلتر کردن مقادیر NULL در ساب‌کوئری

ساده‌ترین راه حل این است که مطمئن شوید ساب‌کوئری شما هیچ مقدار NULLی را برنمی‌گرداند. این کار را می‌توان با اضافه کردن یک شرط WHERE CategoryId IS NOT NULL (اگر CategoryId قابل NULL باشد) یا WHERE CategoryName IS NOT NULL به ساب‌کوئری انجام داد.

SELECT ProductName
FROM Products
WHERE CategoryId NOT IN (SELECT CategoryId FROM Categories WHERE CategoryName = 'Beverages' AND CategoryId IS NOT NULL);

با این تغییر، کوئری دوباره محصولاتی را برمی‌گرداند که در دسته ‘نوشیدنی‌ها’ نیستند، زیرا مقدار NULL از لیست ساب‌کوئری حذف شده است.

2. استفاده از NOT EXISTS به جای NOT IN

یکی دیگر از جایگزین‌های قدرتمند برای NOT IN، عملگر NOT EXISTS است. NOT EXISTS به طور متفاوتی با NULLها برخورد می‌کند و در بسیاری از موارد کارایی بهتری نیز دارد، به خصوص برای مجموعه داده‌های بزرگ. NOT EXISTS هر ردیف را در کوئری اصلی با ردیف‌های ساب‌کوئری مقایسه می‌کند و اگر هیچ تطابقی پیدا نکند (و ساب‌کوئری ردیفی را برنگرداند)، ردیف اصلی را انتخاب می‌کند.

SELECT p.ProductName
FROM Products p
WHERE NOT EXISTS (SELECT 1 FROM Categories c WHERE c.CategoryId = p.CategoryId AND c.CategoryName = 'Beverages');

این کوئری نیز نتایج مشابهی را با NOT IN صحیح برمی‌گرداند، اما نسبت به حضور NULL در جدول Categories مقاوم‌تر است زیرا EXISTS (و در نتیجه NOT EXISTS) به مقادیر NULL در ساب‌کوئری اهمیتی نمی‌دهد؛ بلکه فقط به وجود یا عدم وجود ردیف‌ها اهمیت می‌دهد.

استفاده از LEFT JOIN به همراه WHERE Col IS NULL نیز یک الگوی رایج و کارآمد برای یافتن رکوردهای “عدم وجود” است که اغلب عملکرد بهتری نسبت به NOT IN و NOT EXISTS در شرایط خاص دارد، به خصوص زمانی که ایندکس‌ها به خوبی طراحی شده باشند.

SELECT p.ProductName
FROM Products p
LEFT JOIN Categories c ON p.CategoryId = c.CategoryId AND c.CategoryName = 'Beverages'
WHERE c.CategoryId IS NULL;

این کوئری نیز نتایج صحیح را برمی‌گرداند و به دلیل نحوه عملکرد JOINها، اغلب برای پایگاه‌های داده بزرگ و کوئری‌های پیچیده ترجیح داده می‌شود.

در نهایت، برای پاکسازی پایگاه داده نمونه، می‌توانیم آن را حذف کنیم:

USE master;
GO

DROP DATABASE SampleDB;
GO

انتخاب بین NOT IN، NOT EXISTS یا LEFT JOIN / IS NULL به سناریو، حجم داده، و وجود NULLها بستگی دارد. درک دقیق رفتار هر یک، به ویژه در مورد NULL، برای نوشتن کوئری‌های کارآمد و بدون خطا در SQL ضروری است.