تفاوت بین دستور IN و Exists در شرط Where

[حمیدرضاصادقیان]

سلام.

در این تاپیک نکاتی در مورد این دو دستور بیان خواهد شد.

جداول زیر رو ایجاد بفرمائید.

Create Table
Customers(CustId int not null,
CustomerName varchar(20),
Constraint PK_Customers primary Key(CustID)
);

Create Table Orders
(OrderId int not null,
CustId Int Null,
Price Int,
Constraint PK_Orders primary key(OrderId)
);

Insert Into Customers
Values(1,'Hamid'),
(2,'Mohammad'),
(3,'Ali'),
(4,'Reza'),
(5,'ALireza');

Insert Into Orders
Values(1,1,1000),
(2,1,5000),
(3,2,6000),
(4,Null,7000),
(5,4,8000);


ما میخواهیم لیست مشتریانی رو بدست بیاوریم که هیچ فروشی برای آنها در جدول orders ثبت نشده است.
به دو روش میتوان آنرا انجام داد.
هم با استفاده از دستور In هم با استفاده از دستور Exists.
دستور زیر را ابتدا اجرا بفرمائید.


Select * from Customers
Where CustId not in(select CustId from Orders)

ولی این دستور نتیجه ای رو به ما نمایش نخواهد داد.(دلیل آنرا بیان کنید)
در صورتی که الان کاربر با کد 3و5 هیچ فروشی برای آنها ثبت نشده است.

حال دستور زیر رو اجرا کنید.

Select * from Customers
Where Not Exists(Select * from Orders
Where Customers.custid=Orders.Custid)

مشاهده میکنید که نتیجه مورد نظر مارا نمایش داد.

هردو دستور از لحاظ منطقی و ساختاری درست هستند ولی چرا با In نتیجه دلخواه را نمایش نداد ولی با Exists نتیجه دلخواه را دریافت کردیم؟

برای اینکه بتوانیم نتیجه را با In نیز ببینیم باید چه تغییری در دستور بدهیم و چرا؟؟

[Felony]

ولی این دستور نتیجه ای رو به ما نمایش نخواهد داد.(دلیل آنرا بیان کنید)
دستور In وقتی نتیجه مقایسه رو True بر میگردونه که صراحتا مقدار مورد نظر رو در Sub Query یا لیست ارائه شده ببینه ولی دستور Exists وقتی Sub Query حاوی حداقل 1 رکورد باشه مقدار True بر میگردونه ( مثل Select Count ) عمل میکنه ولی به صورت بهینه تر .

در جدول Orders مقدار فیلد CustID یکی از رکوردها ( شماره 4 ) پوچ ( Null ) هست ، Null قابل مقایسه با هیچ چیزی نیست ، حتی با خود Null ؛ پس دستور In نمیتونه عدد پاس داده شده به Sub Query رو با مقدار Null مقایسه کنه و False بر میگردونه .

برای حل مشکل باید رکوردهای Null رو از نتیجه Sub Query کنار گذاشت :
Select * from Customers
Where CustId not in(select CustId from Orders where CustId is not null)

[حمیدرضاصادقیان]

باتشکر از جناب تاجیک .جواب صحیح هست. در واقع Exists وقتی به null برمیخوره باهاش مثل یک مقدار False رفتار میکنه.ولی وقتی In به یک مقدار null برخورد میکنه باهاش مثل یک مقدار not Unknown برخورد میکنه و هیچ رفتاری نشون نمیده به همین خاطر هیچ رکوردی رو برنمیگردونه.
این دو دستور نیز در این مورد از لحاظ Plan شبیه هم میباشند و در Performance هیچ تفاوتی باهم ندارند.

[کامروا]

در جدول Orders مقدار فیلد CustID یکی از رکوردها ( شماره 4 ) پوچ ( Null ) هست ، Null قابل مقایسه با هیچ چیزی نیست ، حتی با خود Null ؛ پس دستور In نمیتونه عدد پاس داده شده به Sub Query رو با مقدار Null مقایسه کنه و False بر میگردونه .



در واقع Exists وقتی به null برمیخوره باهاش مثل یک مقدار False رفتار میکنه.ولی وقتی In به یک مقدار null برخورد میکنه باهاش مثل یک مقدار not Unknown برخورد میکنه و هیچ رفتاری نشون نمیده به همین خاطر هیچ رکوردی رو برنمیگردونه.
در پاسخ آقای تاجیک، منظور از False برگرداندن ، SubQuery است درسته؟ چون گفتیم که null قابل قیاس با هیچ چیز نیست. و در درنهایت چون مقدار SubQuery برابر False می باشد هیچ خروجی نداریم؟
اما توی Exists رکورد حاوی null رو False بر میگرداند.

سپاس

[حمیدرضاصادقیان]

منظور از unknown یعنی نه false نه True. درواقع چون نتیجه برای In مشخص نیست نمیتونه کنترلی روی نتیجه داشته باشه و هیچ رکوردی رو برنمی گردونه ولی در Exists مقدار Null رو با False کنترل میکنه و نتیجه درست رو به ما میده.

[کامروا]

منظور از unknown یعنی نه false نه True. درواقع چون نتیجه برای In مشخص نیست نمیتونه کنترلی روی نتیجه داشته باشه و هیچ رکوردی رو برنمی گردونه ولی در Exists مقدار Null رو با False کنترل میکنه و نتیجه درست رو به ما میده.

ممنون از پاسختون.

خب پاسخ این قسمت آقای تاجیک


س دستور In نمیتونه عدد پاس داده شده به Sub Query رو با مقدار Null مقایسه کنه و False بر میگردونه .
منظور از False، چیست؟

[dublsigma]

هیچ مناقشه ای در مثل نیست، ولی چرا باید مقدار ستون CustId در جدول orders قابلیت پذیرش مقدار null را داشته باشه.

[حمیدرضاصادقیان]

این یک مثاله.در واقع حرف شما درسته.
ولی غرض نشان دادن تفاوت نتیجه ارائه شده توسط این دو دستور بود.
نظر شما در این زمینه چیه؟

[dublsigma]

ایده و نظر خاصی ندارم. منتهی میتونیم مشکل not in رو با دو سطح سابکوری (two level subquery) بشکل خنده دار زیر حل کنیم، که جواب کد 3و5 مورد نظر هست.

select custid
from customers
where custid not in
(select custid
from customers
where custid in (select custid
from orders));

[حمیدرضاصادقیان]

خوب اینم راهیه. راه ساده ترش استفاده از is null هست که با یک in هم حل میشه.
درواقع میخواستم مشکل In رو دربرخورد با Null ها مطرج کنم.

[hassanzarabi]

با سلام و خسته نباشید خدمت شما
از بابت این آموزش خیلی عالی بسیار ممنونم خیلی عالی بود
ممنونم جناب آقای مهندس حمیدرضاصادقیان (http://barnamenevis.org/member.php?4504-%D8%AD%D9%85%DB%8C%D8%AF%D8%B1%D8%B6%D8%A7%D8%B5%D 8%A7%D8%AF%D9%82%DB%8C%D8%A7%D9%86)
با تشکر از شما