تفاوت CROSS APPLY و CROSS JOIN

تفاوت CROSS APPLY و CROSS JOIN

نوشته شده توسط: تورج عزیزی
۰۴ شهریور ۱۳۹۴
زمان مطالعه: 16 دقیقه
۴
(۳)

مقدمه

سلام؛ انتظار می رود خواننده این مقاله با مفاهیم Derived Table و Subquery ها و دستور APPLY آشنا باشد.

قبل از اینکه بخواهیم پی به تفاوت CROSS APPLY و CROSS JOIN ببریم، باید کمی در مورد Derived Table ها و Correlated Sub-query ها و قلمر دید این دو عبارت صحبت کنم.
دوره کوئری نویسی نیک آموز
یک Derived Table  اصطلاحاً Self-Contained است یعنی تمام جداول و ستون های SELECT پدر در Derived Table غیر قابل دستیابی است. به مثال زیر توجه کنید:
select A.*, b.X
from A
cross join (select B.X from B where B.Val=A.Val) b
منظور از SELECT پدر همان SELECT ای است که یک لایه بالاتر از Derived Table است.
این دستور نامعتبر است چون A.Val در قلمرو دید Derived Table نیست (ارزیابی Derived Table مستقل از سایر جداول در کوئری انجام می شود). برای محدود کردن خروجی کوئری بالا به طوریکه A.Val مساوی با B.Val باشد باید از شرط WHERE استفاده کنیم:
select A.*, b.X
from A
cross join (select * from B) b
where A.Val = b.Val

 قانون قلمرو دید Derived Table ها محدود به  CROSS JOIN نیست و در همه انواع Join این قانون بر قرار است: OUTER و INNER و حتی UNION.
این قانون در تضاد با قلمرو دید Correlated Sub-query است. در Correlated Sub-query جداول و ستون های SELECT پدر در قلمرو دید Sub-query است. Sub-query به ازای هر ردیف کوئری اجرا می شود بنابراین سایر ستون ها و جداول در دسترس هستند:

select A.*, (select B.X from B where B.Val=A.Val) as X
from A

یک راه ساده برای درک تفاوت بین CROSS APPLY و CROSS JOIN  دانستن همین اختلاف در قلمرو دید است.  CROSS JOIN با یک Derived Table جوین می شود اما CROSS APPLY بر خلاف ظاهرش در حقیقت یک Correlated Sub-query را روی هر ردیف اعمال می کند.
بنابراین می توانیم کوئری اول را به این شکل بنویسیم:

select A.*, b.X
from A
cross apply (select B.X from B where B.Val=A.Val) b

 از آنجایی که ما یک CROSS APPLY اجرا کردیم نه یک CROSS JOIN پس A.Val در قلمرو دید ماست و کوئری به درستی اجرا می شود.
همین قوانین در مورد Table Valued Function ها هم صادق است:

select A.*, B.X
from A
cross join dbo.UDF(A.Val) B

باز هم این کوئری معتبر نیست چون A.Val خارج از قلمرو دید است و بهترین کاری که تا قبل از ۲۰۰۵ می توان کرد استفاده از یک Correlated Sub-query است:

select A.*, (select X from dbo.UDF(A.Val)) X
from A

اما اگر تابع UDF بالا بیش از یک ردیف بر گرداند خطا می دهد. از نسخه ۲۰۰۵ به بعد اکنون می توانیم اینطور بنویسیم:

select A.*, b.X
from A
cross apply dbo.UDF(A.Val) b

از نظر Performance به طور کلی APPLY از JOIN ضعیف تر و Correlated Sub-query ضعیف تر از Derived Table است. با این وجود چرا باید از APPLY به جای Correlated Sub-query استفاده کنیم؟ چون قدرتمند تر است!

CROSS APPLY می تواند چند ردیف را برگرداند

بر خلاف Correlated Sub-query، دستور CROSS APPLY با چند ردیف کار می کند. این کار به ما اجازه می دهد تا یک جدول را به یک تابع مانند ParseCSV که خروجی اش چند ردیف است JOIN کنیم:

select A.ID, b.Val
from A
cross apply dbo.ParseCSV(A.CSV) b

وقتی تابع ParseCSV() چندین ردیف را برمی گرداند مانند این است که ما خروجی تابع را با جدول A  جوین کرده ایم و هر رکورد جدول A را به ازای خروجی تابع تکرار کرده ایم. کاری که باCorrelated Sub-query نمی توانستیم انجام دهیم.

CROSS APPLY می تواند چند ستون را برگرداند

در یک  Correlated Sub-query ما فقط می توانیم یک مقدار برگردانیم. مثلاً اگر بخواهیم یک مقدار running sum برگردانیم می توانیم به این شکل عمل کنیم:
select o.*,
(select sum(Amount) from Order o
where p.OrderDate <= o.OrderDate) as RunningSum
from Order o
و اگر بخواهیم یک دیگر بر اساس شرطی خاص برگردانیم مثلاً OrderCode های یکسان باید به این شکل عمل کنیم:
 select o.*,
(select sum(Amount) from Order o
where p.OrderDate <= o.OrderDate) as RunningSum,
(select sum(Amount) from Order o
where p.OrderCode = o.OrderCode and p.OrderDate <= o.OrderDate) as SameCode
from Order o
اما با استفاده از CROSS APPLY می توانیم ساده تر عمل کنیم

select o.*, rs.RunningSum, rs.SameCode
from Order o
cross apply
(
select
sum(Amount) as RunningSum,
sum(case when p.OrderCode = o.OrderCode then Amount else 0 end) as SameCode
from Order P
where P.OrderDate <= O.OrderDate
در کد بالا مزیت برگشت چند ستون به جای یک ستون را می بینید مانند Derived Table و همینطور قابلیت ارجاع به ستون های SELECT بیرونی.
CROSS APPLY همچنین امکان رجوع به ردیف قبلی را به سادگی ممکن می کند:

 select o.*, prev.*
from Order o
cross apply
(
select top 1 *
from Order P where P.OrderDate < O.OrderDate
order by OrderDate DESC
) prev
توجه کنید که CROSS APPLY در کد بالا به ازای سفارشاتی که یک روز قبل از تاریخشان سفارشی ثبت نشده نشان داده نمی شوند برای رفع این مشکل باید از OUTER APPLY استفاده کرد:

 select o.*, prev.*
from Order o
outer apply
(
select top 1 *
from Order P where P.OrderDate < O.OrderDate
order by OrderDate DESC
) prev

 

برای بدست آوردن اطلاعات بیش‌تر در مورد دیگر دستورات SQL ، به مقاله زیر مراجعه کنید.
 
دستورهای SQL Server

چه رتبه ای می‌دهید؟

میانگین ۴ / ۵. از مجموع ۳

اولین نفر باش

title sign
معرفی نویسنده
تورج عزیزی
مقالات
17 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
تورج عزیزی
پروفایل نویسنده
title sign
دیدگاه کاربران

    •    با تشکر از مقاله خوب شما ، لطفا در خصوص outer apply توضیح کاملتری ایفاد نمایید.

      • سلام
        اگر کمی به معنی خود کلمه apply توجه کنید متوجه کلیت قضیه می شید این کلمه کلیدی اجازه میده روی تمام رکورد های سمت چپ apply عملیاتی رو انجام بدین و خروجی رو به ازای هر ردیف تولید کنید که این خروجی به فرم ردیف هست و هر ردیف میتونه حداقل یک ستون یا بیشتر رو داشته باشه … حالا ممکنه به ازای یک ردیف خاص هیچ خروجی نداشته باشیم و به ازای یک ردیف دیگه چند رکورد داشته باشیم اگر بخواهیم ردیف های سمت چپ apply بدون توجه به خروجی apply همیشه تو خروجی نهایی ظاهر بشن باید از outer استفاده کنیم در غیر این صورت cross استفاده میشه…
        ضمنا در cross join شما صرفا یک ضرب دکارتی دارید اما هدف از apply این هست که رکوردهایی با استفاده از رکورد های سمت چپ apply تولید شوند و در این فرایند تولید معمولا از فیلدهای سمت چپ apply در سمت راست استفاده میشود….
        امید دارم واضح گفته باشم

    • سلام تورج جان

      از ارایه مقاله خوب شما ممنونم . بسار عالی بود . بنظر میرسد که در اینگونه مقاله ها بهتر است یک مثال با ارایه اسکریپت مربوطه به ساخت جدول نمونه و درج چند رکورد در آن و سپس ارایه SELECT مربوط به آن بیان بشود ، در ذهن خواننده بهتر قرار میگیرد
    •  سلام جالب بود هر چند تمام تلاشم این هست که تا بتونم از cross استفاده نکنم ولی مقالتون خوب بود

    •  ممنون از مقاله خوبتون 

    •  سلام

      بهتر بود مقاله با ارائه کامل همه پیش نیازها و به شکل کاملتر برای درک بهتر ارائه میشد 
      خسته نباشید
    •  با سلام و تشکر از مقالات خوب و تخصصی
      با این که قبلا با همه این مباحث کارکرده بودم، ولی این مقاله با دیدگاهی جدید، بیان روان و مثالهای ساده نظم بهتری به دانسته های پراکنده ام داد. از شما ممنونم.
      بعنوان یک تجربه که می تواند گاهی از ساعتها اتلاف وقت برنامه نویس جلوگیری کند، میخواستم این نکته رو تذکر بدم که در subquery ها عادت کنیم همیشه اسم جدول را هم قبل از اسم فیلد بیاوریم. اگر اسم فیلد بدون جدول در ساب کوئری باشد، ابتدا سعی میکند فیلد را در جدول فرزند پیداکند و اگر پیدا نکرد، آن را در جدول پدر جستجو می کند. 
      مثلا در مثال زیر می خواهیم در کنار هر سفارش، نام محصول نیز نمایش داده شود.
      Create Table Products (ID int, Title nvarchar(100) )
      Create Table Orders (ID int, Title nvarchar(100), ProductID int null)
      insert Products values (1, N’SQL Server ویژه برنامه نویسان’)
      insert Products values (2, N’دوره افزایش سرعت در SQL Server’)
      insert Orders values (1,N’مشتری اول’ , ۱)
      insert Orders values (2,N’مشتری اول’ , ۲)
      insert Orders values (3,N’مشتری دوم’ , ۲)
      Select Orders.* , (Select Title from Products Where ID = ProductID) As ProductTitle 
      from  Orders
      تا اینجا همه چیز به خوبی انجام می شود، اما فرض کنید بعدا تصمیم بگیریم مثلا فیلد ID از جدول Product را به PID تغییر نام بدهیم
      EXEC sp_rename @objname = ‘Products.ID’, @newname = ‘PID’, @objtype = ‘COLUMN’
      وقتی دوباره همان کوئری قبلی را اجرا می کنیم بدون این که با خطایی مواجه شویم، نتایج دور از انتظاری دریافت می کنیم.
      علت این است که در مثال اول فیلد ID به جدول Products برمیگردد و در اجرای دوم این فیلد را از جدول Orders انتخاب می کند.
      اما اگر در ساب کوئری نام همه فیلدها را با نام جدول همراه می کردیم با خطایی مواجه می شدیم که برطرف کردن آن بسیار ساده بود.
    •  سلام
      مقاتون را خوندم خوب بود ولی سطح بنده برای درک بهترش سخت بود خواهشا در صورت امکان یک منبع کامل که پیش نیازها و خود اصل موضوع را برای من باز کنه بهم معرفی بفرماید متشکرم

    •  با سلام و احترام

      جناب عزیزی مقالتون بسیار خوب و کامله فقط  کاش ابتدا پیش نیاز ها رو ارائه میدادید.
      با تشکر 
    • سلام
      اگر کمی به معنی خود کلمه apply توجه کنید متوجه کلیت قضیه می شید این کلمه کلیدی اجازه میده روی تمام رکورد های سمت چپ apply عملیاتی رو انجام بدین و خروجی رو به ازای هر ردیف تولید کنید که این خروجی به فرم ردیف هست و هر ردیف میتونه حداقل یک ستون یا بیشتر رو داشته باشه … حالا ممکنه به ازای یک ردیف خاص هیچ خروجی نداشته باشیم و به ازای یک ردیف دیگه چند رکورد داشته باشیم اگر بخواهیم ردیف های سمت چپ apply بدون توجه به خروجی apply همیشه تو خروجی نهایی ظاهر بشن باید از outer استفاده کنیم در غیر این صورت cross استفاده میشه…
      ضمنا در cross join شما صرفا یک ضرب دکارتی دارید اما هدف از apply این هست که رکوردهایی با استفاده از رکورد های سمت چپ apply تولید شوند و در این فرایند تولید معمولا از فیلدهای سمت چپ apply در سمت راست استفاده میشود….
      امید دارم واضح گفته باشم

      ۱
ثبت نام رایگان در همایش Tehran .NET Conf 2023 ، همین الان کلیک کنید
ثبت نام رایگان..
close-image