مقایسه تابع coalesce با isnull 

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

---

مقایسه تابع coalesce با isnull  - تعداد پارامتر ورودی

به طور خلاصه می توانیم بگوییم که تابع COALESCE اولین عبارت (expression) غیر null رو از بین آرگومان های ورودی بر می گرداند و تابع ISNULL مقدار NULL را با مقدار مشخص شده جایگزین می کند. با اجرای مثال زیر، هر دو تابع coalesce و isnull مقدار 1759 را به عنوان خروجی نمایش می دهند:

SET NOCOUNT ON;
                             
      DECLARE
         @x AS INT = NULL,
         @y AS INT = 1759,
         @z AS INT = 42;
                              
 SELECT COALESCE(@x, @y, @z);
 SELECT ISNULL(@x, @y);
 

پس تا اینجا دیدیم که هر دو تابع coalesce و isnull در مثال بالا دارای عملکرد یکسانی هستند (در مثال های بعدی تفاوت تولید خروجی این دو عملگر را خواهیم دید). به عنوان یک مزیت برای تابع COALESCE می توان گفت که تعداد پارامترهای آن محدودیتی ندارد در حالیکه در تابع ISNULL تنها دو پارامتر ورودی می توانیم داشته باشیم. نکته دیگر در مورد تابع coalesce این است که از توابع استاندارد ISO/ANSI است در حالیکه تابع ISNULL از توابع T-SQL است که توسط مایکروسافت توسعه داده شده است (در مقاله ای جداگانه در مورد تفاوت این گونه توابع بحث خواهیم کرد).

دو تفاوت اشاره شده، از تفاوت های ساده و مشخص بین دو تابع coalesce و isnull بودند، در ادامه به تفاوت هایی اشاره می کنیم که شاید تا به حال به آن ها توجهی نداشته اید و خوب به دلایلی مانند شانس شما! و یا اتفاق نیفتادن حالت خاص، مشکلی در اسکریپت شما رخ نداده است. اما دانستن این تفاوت ها الزامی و کاربردی هستند.

---

مقایسه تابع coalesce با isnull  - نوع مقدار بازگشتی

 توجه به این نکته که مقدار خروجی دو تابع coalesce و isnull باید دارای نوع (Datat Type) باشد این سؤال جالب رو برای ما ایجاد می کنه که چگونه نوع یک مقدار بازگشتی تعیین می شود؟!

هر کدام از توابع coalesce و isnull دارای روشی متفاوت در تعیین نوع مقدار بازگشتی خود دارند که در مورد تابع ISNULL ساده تر از coalesce است و همیشه نوع (data type) مقدار خروجی برابر است با نوع پارامتر اول. (در مقایسه تابع coalesce با isnull، حالاتی را بررسی می کنیم که پارامترهای ورودی دو تابع دارای نوع های مختلف هستند)

با توضیح ارائه شده، حدس بزنید که نوع مقدار خروجی تابع isnull در مثال زیر چیست:

DECLARE
	@x AS int = NULL,
	@z AS decimal(5,2)=125.25

select isnull(@x, @z)

 خروجی مثال بالا برابر است با  125 به دلیل اینکه پارامتر اول تابع isnull برابر است با x@ و نوع این پارامتر هم int هست در نتیجه مقدار z@ به نوع int تبدیل (cast) می شود و عدد 125 را در خروجی مشاهده می کنیم. (برای آشنایی بیشتر با این مورد نوع های داده ای مختلف را امتحان کنید)

 و اما نوع (data type) مقدار خروجی در تابع coalesce برابر است با نوع پارامتر ورودی با بالاترین اولویت از بین پارامترهای موجود بدون توجه به مکان قرارگیری آن. دقت کنید نوع های داده ای در sql server دارای اولویت هستند. (برای درک بهتر این موضوع، اولویت عملگرها در عملیات ریاضی رو به خاطر بیاورید). اولویت نوع هایی داده ای (Data types) در sql server به ترتیب زیر است (چپ به راست).

<user-defined data types (highest) >sql_variant >xml >datetimeoffset >datetime2 >datetime >smalldatetime >date >time >float >real >decimal >money >smallmoney
<bigint >int >smallint >tinyint >bit >ntext >text >image >timestamp >uniqueidentifier >nvarchar (including nvarchar(max) ) >nchar >varchar (including varchar(max) )  char>varbinary (including varbinary(max) ) >binary

 با توجه به توضیحات بالا، حدس بزنید که نوع مقدار خروجی تابع coalesce در مثال زیر چیست:

DECLARE
	@x AS int = NULL,
	@y as bigint=100,
	@z AS decimal(5,2)=125.25

SELECT  COALESCE(@x, @y,@z)  AS result

همانطور که در مثال بالا می بینیم تابع coalesce سه پارامتر ورودی از نوع های int، bigint و decimal داره. طبق اولویت نوع های داده ای، از بین این سه نوع، decimal دارای بالاترین اولویت است. (دقت کنید همانطور که اشاره شد،اینکه پارامتر چندم تابع باشد مهم نیست) پس نوع مقدار خروجی تابع coalesce به decimal تبدیل می شود. و اما منطق تابع coalesce می گوید که اولین مقدار غیر null به عنوان خروجی برگشت داده شود که در اینجا y@ به عنوان خروجی مشخص می شود، اما نوع آن باید به (5,2)decimal تبدیل شود که نتیجه 100.00 خواهد بود. پس در تابع coalesce دو مرحله داریم:

  مرحله اول: تعیین نوع خروجی که برابر است با نوع پارمتر ورودی با بالاترین اولویت

 مرحله دوم: تعیین مقدار خروجی که برابر است با اولین مقدار غیر null در بین پارامترها

برای این که در این رابطه ابهامی باقی نماند پیشنهاد می کنم، نوع های داده ای مختلف با اولویت های متفاوت را در اسکریپت بالا جایگزین کنید و نتیجه را ببینید.

نکته اول: گاهی ممکن است شما مانند مثال بالا به طور مشخص، تبدیل شدن نوع داده ای رو متوجه نشوید. در این حالت از روش زیر استفاده کنید:

DECLARE
	@x AS char(3) = NULL,
	@y as varchar(10)='Bandamir',
	@z AS nvarchar(50)=N'بندامیر'

--ایجاد جدول و درج نتیجه تابع در آن	
SELECT  COALESCE(@x, @y,@z)  AS famil INTO dbo.T1;

--استخراج نوع داده ای 
SELECT TYPE_NAME(user_type_id)
   FROM sys.columns
      WHERE object_id = OBJECT_ID('dbo.T1') AND name = N'famil';

در مثال بالا اگر تنها دستور  SELECT  COALESCE(@x, @y,@z)  AS famil را اجرا کنید، مقدار Bandamir در خروجی نمایش داده می شود. ممکن است شما اینگونه تحلیل کرده باشید که در این مثال تابع coalesce سه نوع داده ای char، varchar و nvarchar دارد و طبق اولویت نوع داده ای، بایستی خروجی از نوع nvarchar باشد. خوب تا اینجا درست. اما دقت کنید تنها نوع داده است که روی خروجی اعمال می شود و نه تعداد کاراکتر یا طول آن. با عدم توجه به این نکته ممکن است شما انتظار نمایش Bandami (با طول 7، به دلیل (7)nvarchar) را در خروجی داشته باشید.

با اجرای کامل اسکریپت بالا، خروجی nvarchar خواهد بود که به درستی نوع مقدار خروجی را نمایش می دهد.

نکته دوم: این ویژگی تابع coalesce در تعیین نوع خروجی، یه جاهایی هم دردوسرساز میشه. مثال زیر رو در نظر بگیرید و حدس بزنید که خروجی چه خواهد بود؟

--COALESCE تابع 
SELECT COALESCE('abc', 1);

--ISNULL تابع 
SELECT ISNULL('abc', 1);

در مثال بالا تابع coalesce با خطای Conversion failed when converting the varchar value 'abc' to data type int مواجه می شود. دلیل این مورد رو دیگه باید به خوبی بدونین اما به عنوان یک مرور، در مثال بالا تابع coalesce دو نوع رشته ای و عددی دارد و با توجه به اینکه اولویت مقدار عددی بالاتر است، این تابع سعی می کند مقدار خروجی (abc) را به عدد تبدیل کند که با خطا مواجه می شود. اما تابع isnull خیلی راحت مقدار abc رو برای ما نمایش میده! چون همیشه نوع خروجی برابر است با نوع پارامتر اول.

نکته دوم: تابع coalesce خلاصه نویسی شده دستور case ... when در sql server است و این دو معادل یکدیگر هستند. به دلیل مشاهده بهتر جزئیات در  دستور case ... when سعی کنید دستورات فوق را با case ... when بازنویسی کنید و قوانین گفته شده را در آن نیز بررسی کنید. 

---

 مقایسه تابع coalesce با isnull  - ویژگی Nullability

 تا اینجا با توضیحات داده شده در مقایسه تابع coalesce با isnull متوجه شدیم که خروجی مشاهده شده دارای یک ویژگی به نام نوع هست که اهمیت ویژه ای دارد. اما ویژگی دوم  خروجی Nullability است. به این معنا که اگر از این توابع به عنوان قید (constraint) استفاده شود و یا در ایجاد جدول با استفاده از select ... into ( که به آن make table query هم گفته می شود) این ویژگی اهمیت پیدا می کند.

در ایجاد ستون جدول با استفاده از تابع coalesce تنها زمانی یک ستون NOT NULL است که همه پارامترهای ورودی تابع nonnullable باشند و در تابع isnull تنها زمانی یک ستون NOT NULL است که حداقل یکی از دو پارامتر nonnullable باشند. برای درک بهتر این مورد، مثال زیر را اجرا می کنیم:

--int ایجاد جدولی با ستون از نوع 
SELECT CAST(1 AS INT) AS col1 INTO dbo.T0;

--T0 با استفاده از جدول  T1 ایجاد جدول   
SELECT
	COALESCE(1, 2) AS COALESCE_1_2,
	COALESCE(col1, 0) AS COALESCE_col1_0,
	ISNULL(col1, 0) AS ISNULL_col1_0
	 INTO dbo.T1
	    FROM dbo.T0;

 تا اینجا، جدول T1 رو با استفاده از جدول T0 و توابع coalesce و isnull ایجاد کرده ایم. برای اینکه ببینیم آیا ستون های موجود در جدول T1 ویژگی AllowsNull را دارند یا نه از اسکریپت زیر استفاده می کنیم:

SELECT
 COLUMNPROPERTY(OBJECT_ID('dbo.T1'), 'COALESCE_1_2','AllowsNull') AS COALESCE_1_2,
 COLUMNPROPERTY(OBJECT_ID('dbo.T1'), 'COALESCE_col1_0','AllowsNull') AS COALESCE_col1_0,
 COLUMNPROPERTY(OBJECT_ID('dbo.T1'), 'ISNULL_col1_0','AllowsNull') AS ISNULL_col1_0;

خروجی به شکل زیر خواهد بود:

COALESCE_1_2 :0   COALESCE_col1_0 :1      ISNULL_col1_0: 0

با توجه به اینکه در (COALESCE(1, 2 هر دو پارامتر nonnullable هستند در نتیجه ستون COALESCE_1_2 نیز NOT NULL است و در تابع (COALESCE(col1, 0 به دلیل nullable ستون col1 از جدول T0، ستون COALESCE_col1_0 به صورت nullable است و به عبارتی مقدار null می پذیرد. نهایتا ستون  ISNULL_col1_0 نیز چون با استفاده از تابع (ISNULL(col1, 0 ایجاد شده است و در این تابع، پارامتر دوم (عدد 0) nonnullable است، این ستون نیز NOT NULL است.

نکته: در مثال فوق به دلیل اینکه مفهوم Nullability بیشتر در مورد جداول و ستون آن ها معنا دارد از جداول استفاده شد. اگر شما در تمرین ها تمایل به استفاده از متغییر محلی داشتید توجه داشته باشید که کلیه متغییرهای محلی (Local Variable) در sql server به صورت پیش فرض nullable هستند و اگر در زمان تعریف (Declare) مقداردهی اولیه نشوند، با null مقدار دهی می شوند. نکته دیگر اینکه مقادیر ثابت (constant) بصورت nonnullable هستند(همانطور که در مثال بالا استفاده شد).

---

 مقایسه تابع coalesce با isnull  - مقادیر NULL در پارمتر ورودی

در این قست حالتی را بررسی می کنیم که تمام پارامترهای ورودی دو تابع برابر با NULL باشند. همانطور که می دانید NULL یک مقدار untyped یا بدون نوع است و در تابع coalesce در صورتیکه همه پرامترهای ورودی NULL باشند با خطا مواجه خواهیم شد:

SELECT COALESCE(NULL, NULL);

با اجرا دستور بالا با خطای : At least one of the arguments to COALESCE must be an expression that is not the NULL constant مواجه خواهیم شد. برای جلوگیری از بروز این خطا باید از تبدیل نوع (cast) استفاده کنیم:   (SELECT COALESCE(CAST(NULL AS INT), NULL.

و اما در تابع isnull هر دو پارامتر ورودی می تواندبا مقدار NULL ست شود. حال این سؤال پیش می آید که با توجه به untyped بودن مقدار NULL پس نوع خروجی چگونه تعیین می شود؟! تابع isnull در این حالت نوع خروجی NULL را int در نظر می گیرد: (جهت مشاهده نوع خروجی NULL از روش ذکر شده در نکته اول مقایسه تابع coalesce با isnull  - نوع مقدار بازگشتی استفاده می کنیم) 

--NULL با دو پارامتر  ISNULL مشاهده خروجی تابع 
SELECT ISNULL(NULL, NULL);

 --مشاهده نوع خروجی تابع    
SELECT ISNULL(NULL, NULL) AS col1  INTO dbo.T1;
                              
SELECT TYPE_NAME(user_type_id)
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.T1')
AND name = N'col1';

 با اجرای اسکریپت بالا، در ابتدا مقدار NULL را به عنوان خروجی تابع isnull مشاهده می کنیم و خروجی قسمت دوم نیز int و بیانگر نوع خروجی تابع isnull یعنی NULL است. توجه به این تفاوت دو تابع coalesce و  isnull در جلوگیری از ایجاد خطا در اجرای برنامه بسیار مهم است.

---

 مقایسه تابع coalesce با isnull  - خودآزمایی

در این آموزش سعی شد تا مهمترین تفاوت های بین دو تابع coalesce و  isnull به صورتی کاربردی و ساده بیان شود. نوبتی هم باشد نوبت می رسد به ارزیابی میزان یادگیری شما از این آموزش، جهت سنجش میزان تسلط شما به  مقایسه تابع coalesce با isnull چهار خودآزمایی تهیه کرده ایم که به کلیه موضوعات آن در طول آموزش اشاره شده است. از طریق لینک های زیر این خود آزمایی ها را انجام دهید.

نکته: قبل از پاسخ دهی به خود آزمایی ها، حتما مقدمه ای بر خودآمایی sql server را مطالعه کنید.

      مقایسه تابع coalesce با isnull - خودآزمایی نوع مقدار بازگشتی

      مقایسه تابع coalesce با isnull - خودآزمایی NULLABILITY

      مقایسه تابع coalesce با isnull - خودآزمایی اولویت نوع های داده ای

      مقایسه تابع coalesce با isnull - خودآزمایی مقادیر NULL در پارامتر ورودی