مدیریت NULL در SQL Server

مسئله مهم در طراحی بانک های اطلاعاتی این است که بدانیم چگونه با مقادیر از دست رفته در بانکی مانند SQL Server رفتار کرد. یک مشکل رایج این است که چگونه مقادیر از دست رفته را نشان دهیم .در SQL Server این مشکل با NULL حل شده است.

Null برای مشخص کردن مقادیر از دست رفته یا ناشناخته استفاده می شود. زمانی که صحبت از NULL می شود، بیشتر اوقات فکر می کنیم منظور" مقدار خالی"است.اما در واقع NULL یک مقدار مشخص نیست. اغلب به یک مقدار غیر موجود می گویند که ممکن است، نه صفر یا نه رشته خالی باشد. همیشه دو مقدار خالی با هم مساوی نیستند.

NULL محیط یا تنظیماتی است که اگر مقدار داده موجود نباشد نشان داده می شود. موتور بانک اطلاعاتی از یک bitmap ویژه برای ردیابی ستون ها در یک سطر خالی استفاده می کند. Bitmap شامل یک بیت برای هر ستون است که اگر ستون مورد نظر NULL مقدار 1 را ست می کند.

برای مثال دستور SELECT زیر، داده را از جدول BookDetails برمی گرداند:

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails

نتیجه به صورت زیر نشان داده می شود:

در نتیجه بالا، برخی ستون ها شامل مقادیر از دست رفته یا نا شناخته هستند. SQL کلمه کلیدی NULL را برای مشخص کردن مقدار از دست رفته یا ناشناخته در نظر گرفته است(reserves). در SQL کلمه NULL ها به طور متفاوت از مقادیر دیگر رفتار می کنند. زمانی که مقدار NULL مقایسه می شوند یا در اصطلاحات یا عبارات مورد استفاده قرار می گیرد، بررسی ویژه ای لازم دارد.

مقایسه مقادیر NULL

توسعه دهندگان، به ویژه مبتدیان در T-SQL که درک درستی از عملکرد NULL ندارند، برخی اوقات از عملگرهای مقایسه ای برای مقایسه کردن یک عبارت با مقدار NULL استفاده می کنند.

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate = NULL

فرض بر این است که NULL یک مقدار معتبر است و می تواند با دیگر مقادیر NULL مقایسه شود. بنابراین عبارت یا دستور بالا باید هر سطری که مقدار ReleaseDate آن NULL است را خالی برگرداند. بنابراین BookId 1, 3 به صورت یک نتیجه از منطق فرض شده برمی گردد. اما موتور پایگاه داده نمی تواند هیچی را با هیچی مقایسه کند (NULL to NULL). این دستور یک مجموعه خالی را به عنوان نتیجه برمی گردند.

ما باید مقدار غیر خالی (non-NULL) را برگردانیم:

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate <> NULL

دستور بالا تلاش می کند تا مقادیر غیر خالی واقعی را با NULL مقایسه کند. این به نظر می رسد که یک منطق قابل قبول است. بنابراین BookId 2, 4 و 5 به عنوان نتیجه از این منطق فرض شده برمی گردد. اما در واقع نمی توانیم مقادیر واقعی را با هیچی مقایسه کنیم. به عنوان یک نتیجه این عبارت هنوز هیچ داده ای برنمی گرداند، حتی اگر ما با اطمینان بدانیم که داده ای وجود دارد.

راه مناسب برای انجام مقایسه مقادیر NULL

SQL این معضل را پوشش می دهد.ما می توانیم از مقایسه های IS NULL و IS NOT NULL برای تست مقادیر NULL استفاده کنیم:

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate IS NULL

دستور SELECT بالا از عملگر IS NULL برای برگرداندن سطرهایی که ReleaseDate آنها NULL است استفاده می شود:

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate IS NOT NULL

دستور SELECT بالا از IS NOT NULL برای برگرداندن سطرهایی استفاده می کند که ReleaseDate آنها NULL نباشد:

تنظیم ANSI_NULL برای مقایسه های مقدار NULL

SQL SERVER یک ویژگی با نام ANSI_NULLS دارد که تعیین می کند چگونه موتور پایگاه داده مقایسه های NULL را مدیریت کند. گزینه ANSI_NULLS کنترل می کند که آیا می توانیم از یک عملگر معادل NULL برای معنی موارد مشابه به عنوان NULL استفاده کنیم؟ استاندارد ANSI SQL مشخص می کند که NULL هرگز برابر با هرچیزی نیست.

به طور پیش فرض، SQL Server با ANSI_NULLS نصب می شود که باعث می شود نتیجه بالا نشان داده شود.

بررسی تنظیمات فعلی ANSI_NULLS

IF ((32 & @@OPTIONS) = 32)
  PRINT 'The ANSI_NULLS option turned on.'
ELSE
  PRINT 'The ANSI_NULLS option turned off.'

اگر هیچ تغییری با ANSI_NULLS در موتور پایگاه داده وجود نداشته باشد، این عبارت " The ANSI_NULLS option turned on" را برمی گرداند.

ما می توانیم گزینه ANSI_NULLS را خاموش کنیم. یک مجموعه دستور رفتارها را فقط برای اتصالاتی که ما در آن دستور فرض می کنیم تغییر می کند. اگر ما فرض کنیم که یک SET command در یک stored procedure وجود دارد، این دستور معمولا رفتار procedure ها را کنترل می کند:

SET ANSI_NULLS OFF;
SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate = NULL

SET ANSI_NULLS OFF;
SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate <> NULL

ISNULL و IS NULL

سردرگمی دیگر کار با ISNULL و IS NULL می باشد. ISNULL معنایی مشابه با عملگر IS NULL دارد. اجازه دهید با یک مثال توضیح دهیم.

جزئیات برگردانده شده از کتاب که مقدار(Author) نویسنده NULL است:

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE Author IS NULL

اگر ما ISNULL را درک نکنیم، ممکن است سعی کنیم تا نتیجه ای مشابه با استفاده از ISNULL در عبارت WHERE بازیابی کنیم:

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ISNULL(Author)

خوشبختانه، ممکن است در نهایت با یک پیغام خطا مواجه شویم تا متوجه اشتباه خود شویم:

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

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ISNULL(Author, 'nonexistent') IN ('nonexistent')

در اغلب موارد، ما می خواهیم از تابع ISNULL در عبارت SELECT خود استفاده کنیم تا مقادیر NULL را با مقدار عبارت جایگزین کند:

SELECT BookId, Title, ReleaseDate, Price, ISNULL(Author, 'nonexistent') AS Author
FROM BookDetails

ستون Author در حال حاضر یک مقدرا برای تمام سطرها دارد. تمام NULL ها به nonexistent تغییر می دهد:

COALESCE(یکی شدن)

قبل از این که ادامه دهیم، ISNULL پیاده سازی شده در SQL SERVER برای نام تجاری T-SQL COALESEC، مشخص شده که استاندارد ANSI می باشند. هر دو تابع به ما اجازه می دهند که NULL را با مقدار عبارت جایگزین کنیم، اما تفاوت هایی بین این دو تابع وجود دارد. تابع COALESEC اجازه می دهد تا بیشتر از دو عبارت داشته باشیم.

برای مثال، عبارت SELECT زیر داده ها را از جدول StudentDetails برمی گرداند.

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III
FROM StudentDetails

اجازه دهید تابع COALESE را برای دستور SELECT بالا اعمال کنیم:

SELECT StudentId, StudentName, Department, 
Semester_I, Semester_II, Semester_III,
COALESCE(Semester_I, Semester_II, Semester_III, 0) AS COALESCE_Result
FROM StudentDetails

تابع COALESCE عبارت را به منظور های مشخص ارزیابی می کند و اولین مقدار NULL را برمی گرداند. ستون COALESE_Result نتیجه تابع COALESE را برمی گرداند.

در واقع تابع COALESCE مخفف عبارت CASE می باشد . اما ساختار عبارت CASE پیچیده است. مزیت عبارت CASE این است که منطق را بسیار واضح و روشن نشان می دهد.

SELECT StudentId, StudentName, Department, 
Semester_I, Semester_II, Semester_III,
COALESCE(Semester_I, Semester_II, Semester_III, 0) AS COALESCE_Result,
CASE
	WHEN Semester_I IS NOT NULL THEN Semester_I
	WHEN Semester_II IS NOT NULL THEN Semester_II
	WHEN Semester_III IS NOT NULL THEN Semester_III
	ELSE 0
END CASE_Result
FROM StudentDetails

CASE در مقابل ISNULL

تابع ISNULL محدود است. و تنها در دو عبارت مشخص شده است:

SELECT StudentId, StudentName, Department, 
Semester_I, ISNULL(Semester_II, 0) AS ISNULL_Result, Semester_III
FROM StudentDetails

در این مورد، ما تعریف می کنیم که اگر Semester_II خالی باشد، آنگاه از 0 استفاده می کنیم:

COALESCE انواع داده را به شکل متفاوت مدیریت می کند. برای درک بهتر آن اجازه دهید با یک مثال شروع کنیم،

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III
FROM StudentDetails
WHERE StudentId = 5

ستون های StudentId, Semester_I, Semester_II and Semester_III columns Int, StudentName وDepartment به صورت int تعریف شده اند، ستون های StudentName و Department با نوع Varchar مشخص شده اند. عبارت بالا نتیجه زیر را برمی گرداند.

حالا تابع COALESCE را نیز شامل می شود:

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
COALESCE(Department, StudentName, Semester_I) AS COALESCE_Result
FROM StudentDetails
WHERE StudentId = 5

در این زمان با یک پیغام خطا به اتمام می رسد:

COALESCE از نوعی با بالاترین اولویت بر اساس مقادیر ارسال شده استفاده می کند. ستون Semester_I یک int و یک اولویت int نسبت به Varchar را می گیرد. بنابراین موتور پایگاه داده سعی می کند EEE را به مقدار intتبدیل کند.

حالا اجازه دهید شامل تابع INSULL باشد.

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
ISNULL(Department, Semester_I) AS ISNULL_Result
FROM StudentDetails
WHERE StudentId = 5

عبارت SELECT بالا تابع ISNULL را برای استاندارد مجموعه های Departmnt تنظیم می کند این یک نوع Varchar می باشد. بنابراین موتور پایگاه داده مشکلی نخواهد داشت.

حالا اجازه دهید Order تابع ISNULL را تغییر دهیم.

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
ISNULL(Semester_I, Department) AS ISNULL_Result
FROM StudentDetails
WHERE StudentId = 5

این زمان همچنین ما با یک پیغام خطا به اتمام می رسانیم. زیرا Semester_I استانداردی است که یک نوع داده می باشد. بنابراین موتور پایگاه داده نمی تواند EEE را به نوع داده int تبدیل کند.

آیا هر مقدار + NULL برابر با همان مقدار است؟

زمانی که می خواهیم محاسباتی انجام دهیم مانند اضافه کردن یک مقدار به هیچی یا ضرب یا تفریق یا تقسیم کردن هیچی یا انجام دادن هیچ چیزی با هیچی یا NULL را می گویند، نگاهی به عبارت SELECT بیاندازید:

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
(Semester_I + Semester_II + Semester_III) AS TotalResult
FROM StudentDetails
WHERE StudentId = 5

زمانی که تلاش می کنیم محاسبه ای(در اینجا جمع استفاده شده است) با NULL انجام دهیم NULL دریافت می کنیم. ما می توانیم آن را با تعریف یک مقدار پیش فرض برای NULL معقول و منطقی کنیم.

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
(ISNULL(Semester_I, 0) + ISNULL(Semester_II,0) + ISNULL(Semester_III,0)) AS TotalResult
FROM StudentDetails
WHERE StudentId = 5

در اینجا ما از تابع ISNULL با مقدار پیش فرض صفر برای هر پارامتر داخل تابع جمع استفاده می کنیم. بنابراین Semester_I مقدار صفر را برمی گرداند، و TotalResult مقدار 90 را نشان می دهد.

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
ISNULL((Semester_I + Semester_II + Semester_III),0) AS TotalResult
FROM StudentDetails
WHERE StudentId = 5