خانه SQL Server تفاوت CROSS APPLY و CROSS JOIN SQL Server دستورات SQL نوشته شده توسط: تورج عزیزی تاریخ انتشار: ۰۴ شهریور ۱۳۹۴ آخرین بروزرسانی: ۲۵ آبان ۱۴۰۲ زمان مطالعه: 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 ، به مقاله زیر مراجعه کنید. چه رتبه ای میدهید؟ میانگین ۳.۸ / ۵. از مجموع ۶ اولین نفر باش معرفی نویسنده مقالات 18 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده تورج عزیزی معرفی محصول ایمان باقری دوره آموزشی کوئری نویسی در SQL Server 2.190.000 تومان مقالات مرتبط ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز ۰۳ شهریور SQL Server اعمال گواهینامه SSL روی SQL Server تیم فنی نیک آموز ۳۱ مرداد SQL Server دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی تیم فنی نیک آموز ۲۵ مرداد SQL Server نرم افزار (Microsoft SQL Server Management Studio (SSMS چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ seyedmahdi ۱۴ / ۰۴ / ۹۵ - ۰۲:۴۱ با تشکر از مقاله خوب شما ، لطفا در خصوص outer apply توضیح کاملتری ایفاد نمایید. پاسخ به دیدگاه تورج عزیزی ۱۶ / ۰۳ / ۹۸ - ۰۳:۴۵ سلام اگر کمی به معنی خود کلمه apply توجه کنید متوجه کلیت قضیه می شید این کلمه کلیدی اجازه میده روی تمام رکورد های سمت چپ apply عملیاتی رو انجام بدین و خروجی رو به ازای هر ردیف تولید کنید که این خروجی به فرم ردیف هست و هر ردیف میتونه حداقل یک ستون یا بیشتر رو داشته باشه … حالا ممکنه به ازای یک ردیف خاص هیچ خروجی نداشته باشیم و به ازای یک ردیف دیگه چند رکورد داشته باشیم اگر بخواهیم ردیف های سمت چپ apply بدون توجه به خروجی apply همیشه تو خروجی نهایی ظاهر بشن باید از outer استفاده کنیم در غیر این صورت cross استفاده میشه… ضمنا در cross join شما صرفا یک ضرب دکارتی دارید اما هدف از apply این هست که رکوردهایی با استفاده از رکورد های سمت چپ apply تولید شوند و در این فرایند تولید معمولا از فیلدهای سمت چپ apply در سمت راست استفاده میشود…. امید دارم واضح گفته باشم ۱ پاسخ به دیدگاه مهران رحمتی ۱۱ / ۰۶ / ۹۴ - ۱۰:۳۳ سلام تورج جان از ارایه مقاله خوب شما ممنونم . بسار عالی بود . بنظر میرسد که در اینگونه مقاله ها بهتر است یک مثال با ارایه اسکریپت مربوطه به ساخت جدول نمونه و درج چند رکورد در آن و سپس ارایه SELECT مربوط به آن بیان بشود ، در ذهن خواننده بهتر قرار میگیرد پاسخ به دیدگاه m ۰۶ / ۰۶ / ۹۴ - ۰۵:۳۰ سلام جالب بود هر چند تمام تلاشم این هست که تا بتونم از cross استفاده نکنم ولی مقالتون خوب بود پاسخ به دیدگاه Amir Irani ۰۶ / ۰۶ / ۹۴ - ۱۱:۳۹ ممنون از مقاله خوبتون پاسخ به دیدگاه محمدرضا احمدی ۰۵ / ۰۶ / ۹۴ - ۰۹:۱۸ سلام بهتر بود مقاله با ارائه کامل همه پیش نیازها و به شکل کاملتر برای درک بهتر ارائه میشد خسته نباشید پاسخ به دیدگاه dr.ghasemi@gmail.com ۰۵ / ۰۶ / ۹۴ - ۰۱:۰۷ با سلام و تشکر از مقالات خوب و تخصصی با این که قبلا با همه این مباحث کارکرده بودم، ولی این مقاله با دیدگاهی جدید، بیان روان و مثالهای ساده نظم بهتری به دانسته های پراکنده ام داد. از شما ممنونم. بعنوان یک تجربه که می تواند گاهی از ساعتها اتلاف وقت برنامه نویس جلوگیری کند، میخواستم این نکته رو تذکر بدم که در 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 در سمت راست استفاده میشود…. امید دارم واضح گفته باشم ۱ پاسخ به دیدگاه