خانه/ مبانی SQL/ NULL

NULL: یعنی «نامشخص»، نه صفر و نه خالی

مبتدی ۱۴ دقیقه مطالعه

در دو درس قبل، دوبار به NULL اشاره کردیم بدون این‌که کامل توضیحش بدهیم: یک‌بار وقتی گفتیم عملگر + با یک NULL کل نتیجه را خراب می‌کند، و یک‌بار وقتی گفتیم WHERE city = NULL هیچ‌وقت کار نمی‌کند. وقتش رسیده کامل بفهمیم NULL دقیقاً چیست و چرا این‌قدر با بقیه‌ی مقادیر فرق دارد.

NULL دقیقاً چه معنایی دارد؟

NULL یعنی «مقدار نامشخص» یا «عدم وجود مقدار» — نه صفر، نه رشته‌ی خالی ('')، و نه هیچ مقدار دیگری. تفاوتش را با یک مثال ببین:

مقدار	معنا
`grade = 0`	دانش‌آموز امتحان داده و نمره‌اش صفر شده
`grade = NULL`	هنوز مشخص نیست نمره‌اش چند است — شاید هنوز امتحان نداده
`city = ''`	مقدار شهر، یک رشته‌ی متنی خالی است (یک مقدار واقعی، فقط خالی)
`city = NULL`	اصلاً معلوم نیست دانش‌آموز اهل کجاست

این تفاوت مهم است چون خیلی از باگ‌های رایج برنامه‌نویسی SQL، از قاطی‌کردن همین سه مفهوم می‌آید.

چرا WHERE column = NULL هرگز کار نمی‌کند

SQL بر خلاف اکثر زبان‌های برنامه‌نویسی، فقط دو حالت TRUE/FALSE ندارد؛ یک حالت سوم هم دارد به نام UNKNOWN (نامشخص). هر مقایسه‌ای که یکی از طرفینش NULL باشد — حتی NULL = NULL — نتیجه‌اش نه TRUE است و نه FALSE، بلکه UNKNOWN است. و WHERE فقط ردیف‌هایی را نگه می‌دارد که شرطشان دقیقاً TRUE باشد؛ ردیف‌های UNKNOWN هم مثل FALSE کنار گذاشته می‌شوند.

به همین دلیل، WHERE grade = NULL برای هیچ ردیفی — حتی ردیف‌هایی که واقعاً gradeشان NULL است — هرگز TRUE نمی‌شود، و پرس‌وجو همیشه نتیجه‌ی خالی برمی‌گرداند.

عبارت	عملگر منطقی	نتیجه
TRUE	AND UNKNOWN	UNKNOWN
FALSE	AND UNKNOWN	FALSE
TRUE	OR UNKNOWN	TRUE
FALSE	OR UNKNOWN	UNKNOWN
—	NOT UNKNOWN	UNKNOWN

نکته‌ی کاربردی این جدول: اگر یکی از دو طرف OR، TRUE باشد، کل نتیجه TRUE می‌شود — حتی اگر طرف دیگر UNKNOWN باشد. اما در بقیه‌ی حالت‌ها، وجود UNKNOWN معمولاً نتیجه را غیرقابل‌پیش‌بینی می‌کند؛ برای همین باید همیشه صریحاً NULL را مدیریت کنیم.

راه درست: IS NULL و IS NOT NULL

برای پرسیدن «آیا این مقدار نامشخص است؟»، باید از عبارت مخصوص IS NULL استفاده کرد، نه عملگر =:

sql

SELECT full_name, city
FROM students
WHERE city IS NULL;

SELECT full_name, grade
FROM students
WHERE grade IS NOT NULL;

بیا هر دو روش — اشتباه و درست — را کنار هم روی یک جدول با چند مقدار NULL امتحان کنیم:

امتحانش کن — چرا = NULL جواب نمی‌دهد

نتیجه‌ی نمایش‌داده‌شده مربوط به آخرین دستور اجراشده است. یک‌بار فقط خط اول را نگه دار و اجرا کن تا با چشم خودت ببینی = NULL همیشه نتیجه‌ی خالی می‌دهد.

NULL در محاسبات و رشته‌ها

همان‌طور که در درس SELECT اشاره کردیم، هر عبارت محاسباتی که یکی از مقادیرش NULL باشد، کل نتیجه‌اش NULL می‌شود — چون «عدد مشخص + مقدار نامشخص» هم نامشخص است:

sql

SELECT full_name, grade, grade + 1 AS NextYear
FROM students;
-- برای دانش‌آموزی که grade اش NULL است، NextYear هم NULL می‌شود

همین موضوع درباره‌ی چسباندن رشته‌ها با + هم صادق است — و دقیقاً همان چیزی است که در درس SELECT وعده دادیم کامل توضیح دهیم:

sql

SELECT full_name + N' - ' + city AS Summary
FROM students;
-- اگر city این ردیف NULL باشد، کل Summary هم NULL می‌شود

بیا این را عملاً روی داده‌ای که چند مقدار NULL دارد، ببینیم:

امتحانش کن — NULL در محاسبه و رشته

دقت کن: NextYear برای ردیف‌های با نمره‌ی نامشخص، خودش NULL شده؛ اما ستون Summary که با CONCAT ساخته شده، حتی برای دانش‌آموزی که شهرش نامشخص است، بقیه‌ی متن را نگه داشته — چون CONCAT مقدار NULL را نادیده می‌گیرد. این دقیقاً همان تفاوتی است که در درس SELECT وعده داده بودیم.

جایگزین کردن NULL با یک مقدار پیش‌فرض

گاهی به‌جای نمایش NULL خام در گزارش، بهتر است یک مقدار پیش‌فرض خواناتر مثل «نامشخص» نشان بدهیم. برای این کار دو تابع رایج وجود دارد:

تابع	پشتیبانی	توضیح
`ISNULL(expr, replacement)`	فقط SQL Server	دقیقاً دو ورودی می‌گیرد
`COALESCE(expr1, expr2, ...)`	استاندارد؛ در همه‌ی DBMSها از جمله SQLite	هر تعداد ورودی می‌گیرد و اولین مقدار غیر NULL را برمی‌گرداند

sql

-- فقط در SQL Server واقعی اجرا می‌شود:
SELECT full_name, ISNULL(city, N'نامشخص') AS City
FROM students;

-- استاندارد و قابل‌اجرا همه‌جا (از جمله همین Playground):
SELECT full_name, COALESCE(city, N'نامشخص') AS City
FROM students;

نکته‌ی مهم برای Playground این سایت: موتور SQLite تابع ISNULL را اصلاً ندارد و اجرای آن با خطای نحوی متوقف می‌شود (معادلش در SQLite تابع IFNULL(x, y) است). اما COALESCE یک تابع استاندارد است و در SQLite هم دقیقاً مثل SQL Server کار می‌کند — به همین دلیل توصیه می‌شود در کدی که می‌خواهی قابل‌انتقال بین پایگاه‌داده‌های مختلف باشد، همیشه COALESCE را ترجیح بدهی.

بیا COALESCE را روی همان جدول امتحان کنیم:

امتحانش کن — جایگزینی NULL با COALESCE

NULL و DISTINCT: یک استثنای جالب

گفتیم NULL = NULL برابر با UNKNOWN است، نه TRUE. اما وقتی نوبت به DISTINCT می‌رسد، همه‌ی مقادیر NULL به‌عنوان «یک مقدار تکراری» با هم در نظر گرفته می‌شوند و فقط یک‌بار در خروجی می‌مانند — با این‌که در منطق مقایسه‌ی معمولی، دو NULL هرگز «برابر» شناخته نمی‌شدند. این یکی از معدود جاهایی است که SQL با NULL رفتاری متفاوت از قانون معمولش دارد:

sql

SELECT DISTINCT grade
FROM students;
-- با این‌که دو ردیف NULL دارند، فقط یک NULL در خروجی می‌آید

امتحانش کن — رفتار NULL در DISTINCT

با این‌که در جدول سه ردیف grade IS NULL ندارند (دو ردیف NULL واقعی)، در خروجی DISTINCT فقط یک NULL می‌بینی — درست مثل این‌که NULL هم یک مقدار عادی و تکرارپذیر بوده باشد.

چند نکته‌ی تکمیلی

ستونی که هنگام ساخت جدول NOT NULL تعریف شده (یادت هست، درس جدول‌ها)، هرگز نمی‌تواند NULL بگیرد — این بهترین راه جلوگیری از NULLهای ناخواسته از همان ابتدا است.
تابع COUNT(*) همه‌ی ردیف‌ها را می‌شمارد، اما COUNT(column) فقط مقادیر غیر NULL آن ستون را می‌شمارد — این تفاوت را با جزئیات کامل در فصل ۳ (تجمیع داده‌ها) می‌بینیم.
در مرتب‌سازی با ORDER BY، این‌که مقادیر NULL اول لیست بیایند یا آخر آن، بین DBMSهای مختلف فرق می‌کند — موضوعی که در درس بعدی به آن می‌پردازیم.

جمع‌بندی این درس

NULL یعنی «نامشخص»؛ نه صفر است، نه رشته‌ی خالی.
هر مقایسه با NULL (حتی NULL = NULL) به UNKNOWN می‌رسد، نه TRUE؛ به همین دلیل = NULL هیچ‌وقت کار نمی‌کند.
برای بررسی NULL همیشه از IS NULL / IS NOT NULL استفاده کن.
هر عبارت محاسباتی یا رشته‌ای که + در آن باشد، با یک NULL کاملاً NULL می‌شود؛ CONCAT() این مشکل را ندارد.
COALESCE استاندارد و قابل‌انتقال است؛ ISNULL فقط در SQL Server کار می‌کند.
در DISTINCT، همه‌ی مقادیر NULL یکسان در نظر گرفته می‌شوند — استثنایی بر قانون کلی NULL.

در درس بعدی سراغ ORDER BY می‌رویم: یاد می‌گیریم چطور نتیجه‌ی پرس‌وجو را به دلخواه مرتب کنیم، و این‌که مقادیر NULL دقیقاً کجای این ترتیب قرار می‌گیرند.