خطر استفاده از عملگر Not IN در SQL SERVER - مقدمه

یک نکته ای رو که علاقه مندم در ابتدای آموزش خطر استفاده از عملگر Not IN به افراد مشغول در حوزه دیتابیس گوشزد کنم این است که برای فعالیت به صورت حرفه ای در این حوزه، همواره باید با استثناها و موارد عجیب و غریب در sql server آشنایی داشته باشید! و هر چه حوزه کاری شما حرفه ای تر و حساس تر باشد این نیاز برجسته تر خواهد بود. در آموزش خطر استفاده از عملگر Not IN، با یکی از این استثناها آشنا می شویم و علاوه بر موضوع اصلی آموزش، پیرامون مقدار null در sql server هم مطالبی ارائه خواهد شد.

به طور خلاصه از عملگر Not IN برای پیدا کردن رکوردهای که فاقد شرط/هایی هستند(Missing) استفاده می کنیم. به عنوان مثال برای تهیه گزارشاتی مانند لیست کارمندانی که  حقوق فلان ماه رو دریافت نکرده اند و یا لیست کالاهایی که جهت سفارش رزرو نشده اند و  ... . شکل کلی استفاده از عملگر Not IN بصورت زیر است:

select * from first_table as a  جدول حاوی کلیه رکوردها
     where a.id Not IN(Select id from second_table where ....)

 

نکته: عملگرهایی مانند Exists - IN - Not IN - Not Exists جهت انجام عملیات از جوین (semi join - inner join) استفاده می کنند.

در هر حال، جهت تولید خروجی نهایی، باید یک مقایسه بین id رکوردهای جدول اول (first table)  و جدول دوم (second table) که دارای شرط خاصی هستند انجام شود. جهت آشنایی کامل با خطر استفاده از عملگر Not IN  در ابتدا جداول Person و Address را ایجاد کرده و رکوردهایی تستی در این دو جدول درج می کنیم. در ابتدا یک کوئری با عملگر IN طراحی و نحوه انجام این کوئری را در Execution Plan بررسی خواهیم کرد. در ادامه کوئری دیگری با عملگر Not IN طراحی و اجرا می کنیم و مجدداً Execution Plan را بررسی و تحلیل می کنیم و در این مرحله با خطر استفاده از عملگر Not IN آشنا خواهیم شد. در انتهای آموزش نیز راه حل مقابله با این مشکل را ارائه خواهیم داد.

---

خطر استفاده از عملگر Not IN در SQL SERVER - مثال عملی

در ابتدا جداول  Person و Address را بصورت زیر ایجاد و رکوردهایی تستی در آن ها درج می کنیم:

--ایجاد جداول
CREATE TABLE dbo.Person
(
Name nvarchar(20),
AddressID int
)
Go
CREATE TABLE dbo.Address
(
AddressID int,
Address nvarchar(20)
)
Go
--درج رکورد تستی در جداول 
--1.Person درج در جدول 
INSERT INTO dbo.Person (Name,AddressID) VALUES ('بندامیر'N,1)
INSERT INTO dbo.Person (Name,AddressID) VALUES ('رضایی'N,1)
INSERT INTO dbo.Person (Name,AddressID) VALUES ('جلیلی'N,2)
INSERT INTO dbo.Person (Name,AddressID) VALUES ('منجی'N,2)
INSERT INTO dbo.Person (Name,AddressID) VALUES ('حاتمی'N,3)
INSERT INTO dbo.Person (Name,AddressID) VALUES ('کشوری'N,3)
INSERT INTO dbo.Person (Name,AddressID) VALUES ('کاشانی'N,4)
INSERT INTO dbo.Person (Name,AddressID) VALUES ('محسنی'N,1)
--1.Address درج در جدول 
INSERT INTO dbo.Address (AddressID,Address) VALUES (1,N'اصفهان')
INSERT INTO dbo.Address (AddressID,Address) VALUES (2,N'تهران')
INSERT INTO dbo.Address (AddressID,Address) VALUES (3,N'شیراز')
INSERT INTO dbo.Address (AddressID,Address) VALUES (null,N'کرمان')

محتویات جداول  Person و Address پس از درج:

در ابتدا یک کوئری طراحی می کنیم تا افراد با AddressID موجود در جدول Address را استخراج کنیم. برای این منظور از کوئری زیر با عملگر IN استفاده می کنیم:

SELECT Name,AddressID
  FROM dbo.Person
    WHERE AddressID IN (SELECT AddressID FROM dbo.Address)

جهت بررسی نحوه عملکرد IN در مثال بالا، کوئری فوق را بصورت زیر می نویسیم و Execution Plan را بررسی می کنیم:

با توجه به اطلاعات موجود در قسمت Predicate از tootip بالا، یک نکته مهم قابل توجه است:

نکته: مقادیر موجود در عملگر IN به دنباله ای از شرط های OR تبدیل می شوند.

ممکن است این سؤال برای شما ایجاد شود که در صورت وجود مقدار NULL در عملگر IN، نحوه ارزیابی عبارت به چه شکل خواهد بود. برای پاسخ به این سؤال، به دو نکته در مورد NULL اشاره می کنیم:

نکته اول: نتیجه هر مقایسه یا عملیات با آن برابر است با NULL. (در مقایسه بین NULL با NULL مثلاً NULL=NULL، تنظیم ANSI_NULLS تعیین کننده نتیجه است)

نکته دوم: نتیجه عملیات OR با مقدار NULL به صورت زیر است:

 تا اینجا با عملگر IN، در حد مورد نیاز این آموزش آشنا شدیم. برای مشاهده خطر استفاده از عملگر Not IN و تولید خروجی اشتباه در حالت خاص، یک کوئری طراحی می کنیم تا افرادی از جدول Person را استخراج کنیم که AddressID آن ها در جدول Address وجود ندارد. همانطور که مشخص است AddressID با مقدار 4 در جدول Address وجود ندارد.

--وجود ندارد Address استخراج افرادی که آدرس آن ها در جدول 
SELECT Name,AddressID
 FROM dbo.Person P
  WHERE P.AddressID NOT IN (SELECT A.AddressID FROM dbo.Address A)

جهت بررسی نحوه انجام عملگر NOT IN کوئری را بصورت زیر طراحی و پلن اجرایی(execution plan) تولید شده را بررسی می کنیم:

 

با توجه به اطلاعات موجود در قسمت Predicate از tootip بالا، یک نکته مهم قابل توجه است:

نکته: مقادیر موجود در عملگر Not IN به دنباله ای از شرط های AND تبدیل می شوند.

در مثال بالا، مقدار NULL را جزو پارامترهای عملگر Not IN اضافه نکردیم. در این مرحله، با اضافه کردن NULL، مجدداً نتیجه را بررسی می کنیم:

همانطور که در تصویر بالا مشاهده می کنید، هیچ مقداری در خروجی نمایش داده نمی شود! و اما دلایل این مورد:

1. با توجه به این نکته که نتیجه مقایسه هر مقداری با NULL برابر است با NULL(قبلاً اشاره شد) در نتیجه: حاصل مقایسه dbo].[Person].[AddressID]<>null]  برابر با NULL می شود.

2. نتیجه انجام عملیات AND با مقدار NULL همواره برابر با false است در نتیجه: در حالتی که AddressID=4 است داریم:

در شکل بالا، نحوه ارزیابی عبارت را در حالتی که  AddressID=4 است، مرحله به مرحله مشاهده می کنید و دلیل عدم نمایش نتیجه، با توجه به شکل بالا کاملاً مشخص است. (با وجود NULL و AND کلیه ارزیابی ها نتیجه false دارند!).

نکته: نتیجه عملیات AND با مقدار NULL به صورت زیر است:

تا اینجا با توضیحات، مثال ها و تصاویر ارائه شده، به خوبی با خطر استفاده از عملگر Not IN و عدم تولید خروجی در برخی مواقع، به خوبی آشنا شدیم. به منظور بررسی بیشتر، کوئری اولیه Not IN را بدون تغییر اجرا و بررسی می کنیم:

همانطور که در شکل بالا مشاهده می کنید، جهت انجام Not IN از Nested Loops استفاده شده است در آموزش جوین چیست این عملگر و نحوه کار آن بطور کامل معرفی شد. در کوئری فوق، هر یک از رکوردهای جدول Person با تک تک مقادیر موجود در جدول Address مقایسه می شوند(فیلد AddressID) و شرط مورد بررسی نیز به شکل زیر است:

[dbo].[Person].[AddressID] IS NULL OR
[dbo].[Address].[AddressID] IS NULL OR [dbo].[Person].[AddressID]=[AutoParam].[dbo].[Address].[AddressID]

در صورتی که به ازای یک رکورد، هریک از شرط های بالا برابر با True شود، آن رکورد در خروجی نمایش داده نمی شود. شاید در ظاهر، شرط فوق با آنچه در مثال قبل دیدیم (وجود AND بین عبارات) یکسان نباشد اما از نظر منطقی، هر دو به شکل یکسانی اجرا می شوند به این صورت که در اجرای واقعی، مقدار موجود در فیلد AddressID هر یک از سطر های جدول Person با مقادیر 1و2و3وnull مقایسه می شوند. 

---

خطر استفاده از عملگر Not IN در SQL SERVER - راه حل

یکی از روش ها جهت جلوگیری از مشکل ایجاد شده در عملگر Not IN، استفاده از عملگر NOT EXISTS است. این عملگر با تولید نتیجه صحیح از بروز چنین مشکلی جلوگیری می کند:

 

 به عنوان یک توضیح مختصر، عملگر NOT EXISTS در کوئری بالا، زمانی یک رکورد از جدول Person را در خروجی نمایش می دهد که به ازای آن، هیچ رکوردی در subquery(جدول Address) موجود نباشد. (در آموزش هایی جداگانه عملگرهای  NOT EXISTS و  EXISTS را بررسی خواهیم کرد).

  

 

• قبلی
• بعدی