⚠️ `NULL`: نه صفر است، نه رشتهٔ خالی

در این فصل چه یاد می‌گیری#

سه فصل است بدهیِ این فصل را جمع کرده‌ایم. حالا پرداختش می‌کنیم، و با عددی تمام می‌شود که کلِ روایتِ این ترم را عوض می‌کند.

AVG(rating) بر ۱٬۲۰۰ تقسیم نکرد، بر ۹۶۹ تقسیم کرد. ۲۳۱ کتاب از حساب افتادند. و آن ۲۳۱تا تصادفی نیستند: پرامانت‌ترینشان ۲ بار امانت رفته، در حالی که کم‌امانت‌ترینِ ۹۶۹تای دیگر ۳ بار. یعنی دو گروه اصلاً هم‌پوشانی ندارند و میانگینی که گرفتیم، میانگینِ کتاب‌های محبوب است، نه میانگینِ کتاب‌ها.

آخر این فصل می‌توانی:

• بگویی NULL چه چیزی هست و چه چیزهایی نیست
• بگویی چرا = NULL هیچ‌وقت درست نمی‌شود و چرا خطا هم نمی‌دهد
• تفاوتِ COUNT(*) و COUNT(col) را با عدد نشان بدهی
• ثابت کنی که یک شرطِ ساده چند سطر را بی‌صدا انداخته بیرون
• با COALESCE و IFNULL و NULLIF تصمیمِ خودت را جای تصمیمِ پیش‌فرض بگذاری

قبل از شروع#

از فصلِ ۳: IS NULL و IS NOT NULL را مکانیکی یاد گرفتی. اینجا دلیلش می‌آید.

تعریف، در یک جمله: NULL یعنی «این مقدار را نمی‌دانیم». نه صفر، نه رشتهٔ خالی، نه «هیچ». همهٔ رفتارهای عجیبِ این فصل از همین یک جمله بیرون می‌آیند — و اگر آن را جدی بگیری، هیچ‌کدامشان عجیب نمی‌مانند.

📓 نوت‌بوک: نوت‌بوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

۱. COUNT(*) در برابرِ COUNT(col)#

COUNT(*) سطرها را می‌شمارد. COUNT(col) سطرهایی را می‌شمارد که در آن ستون مقدار دارند.

print(q("""
    SELECT COUNT(*)             AS all_rows,
           COUNT(rating)        AS rated,
           COUNT(price_text)    AS priced,
           COUNT(audio_minutes) AS with_audio
    FROM books
"""))

   all_rows  rated  priced  with_audio
0      1200    969    1200           0

سه عدد و سه داستانِ متفاوت. rating در ۲۳۱ سطر خالی است. price_text در هیچ سطری خالی نیست — آن ۶۵ رشتهٔ خالیِ فصلِ ۵ رشته‌اند، نه NULL؛ COUNT می‌شماردشان. و audio_minutes در همهٔ سطرها خالی است: ستونی که به اسکیما اضافه شد و هیچ‌کس پرش نکرد.

فاصلهٔ COUNT(*) و COUNT(col) ارزان‌ترین آزمونِ سلامتِ داده است که می‌شناسیم. یک بار برای هر ستونی که قرار است رویش حساب کنی بگیرش.

۲. SUM روی ستونی که هیچ مقداری ندارد#

print(q("""
    SELECT SUM(rating)        AS rating_sum,
           AVG(rating)        AS rating_avg,
           SUM(audio_minutes) AS audio_sum,
           AVG(audio_minutes) AS audio_avg
    FROM books
"""))

   rating_sum  rating_avg audio_sum audio_avg
0      3669.5    3.786894      None      None

SUM روی ستونی که همه‌اش خالی است، صفر نمی‌دهد — NULL می‌دهد. و این کاملاً درست است: جمعِ هیچ‌چیزی معلوم نیست، پس ادعای «صفر» یک ادعای بی‌پشتوانه است.

ولی در پایتون همین None وقتی وارد یک محاسبه شود، برنامه‌ات را می‌شکند یا بدتر، بی‌صدا به چیزِ دیگری تبدیل می‌شود. پس هر جا SUM می‌گیری و ممکن است هیچ سطری نداشته باشد، جوابِ NULL را پیش‌بینی کن.

۳. منطقِ سه‌ارزشی#

در پایتون هر مقایسه یا True است یا False. در SQL سه حالت هست: درست، نادرست، و نامعلوم.

print(q("""
    SELECT 1 = NULL       AS a,
           1 <> NULL      AS b,
           NULL = NULL    AS c,
           NULL IS NULL   AS d,
           1 IS NOT NULL  AS e
"""))

      a     b     c  d  e
0  None  None  None  1  1

سه ستونِ اول None شدند، نه ۰ و نه ۱.

منطقش دقیقاً همان تعریفِ یک‌خطی است. «آیا ۱ برابرِ چیزی است که نمی‌دانیم چیست؟» — نمی‌دانیم. «آیا چیزی که نمی‌دانیم برابرِ چیزِ دیگری است که نمی‌دانیم؟» — باز هم نمی‌دانیم؛ دو آدمِ ناشناس لزوماً یک نفر نیستند.

و WHERE فقط سطرهایی را نگه می‌دارد که شرطشان درست باشد. «نامعلوم» درست نیست، پس آن سطر می‌افتد بیرون. این توضیحِ کاملِ آن = NULL صفرجوابی است که در فصلِ ۳ دیدی.

IS و IS NOT عملگرهای مخصوصی هستند که این قاعده را نمی‌خورند: همیشه درست یا نادرست برمی‌گردانند، هرگز نامعلوم.

print(q("""
    SELECT (1 = 1) AND NULL  AS and_true,
           (1 = 2) AND NULL  AS and_false,
           (1 = 1) OR NULL   AS or_true,
           (1 = 2) OR NULL   AS or_false
"""))

  and_true  and_false  or_true or_false
0     None          0        1     None

دو ستونِ وسط جوابِ قطعی دادند. «نادرست AND نامعلوم» نادرست است — هر چیزی که آن نامعلوم باشد، نتیجه عوض نمی‌شود. «درست OR نامعلوم» هم درست است، به همان دلیل. NULL وقتی نتیجه را عوض نمی‌کند، ناپدید می‌شود.

۴. غربالی که سطر می‌بلعد#

high = int(q("SELECT COUNT(*) FROM books WHERE rating >= ?", (4.0,)).iloc[0, 0])
low = int(q("SELECT COUNT(*) FROM books WHERE rating < ?", (4.0,)).iloc[0, 0])
missing = int(q("SELECT COUNT(*) FROM books WHERE rating IS NULL").iloc[0, 0])

print("rating >= 4  :", high)
print("rating <  4  :", low)
print("جمع دو تکه   :", high + low, "| کل جدول: 1200")
print("سطرهای گمشده :", missing, "| با آن‌ها:", high + low + missing)

rating >= 4  : 416
rating <  4  : 553
جمع دو تکه   : 969 | کل جدول: 1200
سطرهای گمشده : 231 | با آن‌ها: 1200

در فصلِ ۳ همین آزمون را روی category زدیم و جمعش ۱۲۰۰ شد. اینجا ۹۶۹ شد.

دو شرطی که ظاهراً مکمل هم‌اند — «چهار و بالاتر» و «کمتر از چهار» — با هم کلِ جدول را پوشش نمی‌دهند، چون برای ۲۳۱ سطر هیچ‌کدام درست نیست. هر دو نامعلوم‌اند.

این ساده‌ترین و پرتکرارترین راهِ ساختنِ یک گزارشِ غلط است، و همان آزمونِ نیم‌دقیقه‌ایِ «جمعِ اجزا برابرِ کل» مچش را می‌گیرد.

✅ چک کن: ۴۱۶ + ۵۵۳ باید بشود ۹۶۹، و ۹۶۹ + ۲۳۱ باید بشود ۱۲۰۰. اگر خطِ دوم درنیامد، شرطِ سومت IS NULL نیست بلکه = NULL است — و آن، خودش صفر برمی‌گرداند و هیچ خطایی هم نمی‌دهد.

و همان مکانیزم، NOT IN را کاملاً از کار می‌اندازد:

in_list = int(q("""
    SELECT COUNT(*) FROM books WHERE category IN (?, ?)
""", ("شعر", "رمان")).iloc[0, 0])
not_in_clean = int(q("""
    SELECT COUNT(*) FROM books WHERE category NOT IN (?, ?)
""", ("شعر", "رمان")).iloc[0, 0])
not_in_null = int(q("""
    SELECT COUNT(*) FROM books WHERE category NOT IN (?, ?, NULL)
""", ("شعر", "رمان")).iloc[0, 0])

print("IN (شعر, رمان)          :", in_list)
print("NOT IN (شعر, رمان)      :", not_in_clean)
print("جمع                     :", in_list + not_in_clean)
print("NOT IN (شعر, رمان, NULL):", not_in_null)

IN (شعر, رمان)          : 310
NOT IN (شعر, رمان)      : 890
جمع                     : 1200
NOT IN (شعر, رمان, NULL): 0

یک NULL در فهرست، و NOT IN از ۸۹۰ به صفر رسید.

دلیلش همان تعریف است. x NOT IN (a, b, NULL) یعنی «x نه a است، نه b، و نه آن چیزِ نامعلوم». تکهٔ آخر هیچ‌وقت قطعاً نادرست نمی‌شود، پس کلِ AND هیچ‌وقت قطعاً درست نمی‌شود. هیچ سطری نمی‌ماند، و هیچ خطایی هم نمی‌گیری. در ترمِ ۳ که فهرست از یک پرسشِ دیگر می‌آید، این تله واقعاً خطرناک می‌شود.

۵. AVG مخرج را عوض می‌کند#

avg_rated = float(q("SELECT AVG(rating) FROM books").iloc[0, 0])
print(q("""
    SELECT ROUND(AVG(rating), 4)                AS only_rated,
           ROUND(AVG(COALESCE(rating, 0)), 4)   AS missing_as_zero,
           ROUND(AVG(COALESCE(rating, ?)), 4)   AS missing_as_average
    FROM books
""", (avg_rated,)))

   only_rated  missing_as_zero  missing_as_average
0      3.7869           3.0579              3.7869

سه عدد، سه تصمیمِ متفاوت دربارهٔ همان ۲۳۱ سطر — و هیچ‌کدام «پیش‌فرضِ بی‌طرف» نیست.

AVG بی‌طرف نیست؛ انتخاب کرده که سطرهای خالی را نشمارد. ستونِ دوم انتخاب می‌کند که آن‌ها صفر باشند و میانگین را هفت‌دهم پایین می‌آورد. ستونِ سوم انتخاب می‌کند که میانگین باشند و به همین دلیل عددِ نهایی را اصلاً تکان نمی‌دهد.

🌱 ریشه‌اش کجاست: میانگین یک تقسیم است و کسر یک عدد است، نه دو عدد سوارِ هم — و همین است که می‌گذارد ببینی چرا عوض شدنِ مخرج جوابِ کاملاً دیگری می‌سازد، حتی وقتی صورت دست‌نخورده مانده. اگر این نگاه برایت تازه است، ریشه ترمِ ۲ فصل ۱ از اول بازش می‌کند.

۶. آن ۲۳۱ کتاب چه کسانی‌اند؟#

قانونِ سومِ این دوره: قبل از باور، بپرس چه کسی از قلم افتاد. حالا واقعاً می‌پرسیم.

print("بی‌امتیازها، به‌ترتیب پرامانت‌ترین:")
print(q("""
    SELECT id, borrowed, rating
    FROM books
    WHERE rating IS NULL
    ORDER BY borrowed DESC, id ASC
    LIMIT 3
"""))
print()
print("امتیازدارها، به‌ترتیب کم‌امانت‌ترین:")
print(q("""
    SELECT id, borrowed, rating
    FROM books
    WHERE rating IS NOT NULL
    ORDER BY borrowed ASC, id ASC
    LIMIT 3
"""))
print()
print("میانگین امانت — بی‌امتیازها :",
      float(q("SELECT AVG(borrowed) FROM books WHERE rating IS NULL").iloc[0, 0]))
print("میانگین امانت — امتیازدارها:",
      float(q("SELECT AVG(borrowed) FROM books WHERE rating IS NOT NULL").iloc[0, 0]))

بی‌امتیازها، به‌ترتیب پرامانت‌ترین:
   id  borrowed rating
0  27         2   None
1  49         2   None
2  63         2   None

امتیازدارها، به‌ترتیب کم‌امانت‌ترین:
    id  borrowed  rating
0   69         3     5.0
1  115         3     4.4
2  185         3     3.9

میانگین امانت — بی‌امتیازها : 0.9783549783549783
میانگین امانت — امتیازدارها: 96.97110423116615

پرامانت‌ترینِ گروهِ بی‌امتیاز ۲ بار امانت رفته و کم‌امانت‌ترینِ گروهِ امتیازدار ۳ بار. دو گروه یک سطرِ مشترک هم ندارند.

و میانگینِ امانت: تقریباً ۱ در برابرِ تقریباً ۹۷.

پس آن ۲۳۱ سطر نمونهٔ تصادفی نیستند؛ دقیقاً کتاب‌هایی هستند که تقریباً هیچ‌کس نبرده. و این کاملاً منطقی است: کتابی که کسی نبرده، کسی هم امتیازش نمی‌دهد.

نتیجه: عددِ ۳٫۷۹ «میانگینِ امتیازِ کتاب‌های این کتابخانه» نیست. «میانگینِ امتیازِ کتاب‌هایی است که دستِ‌کم سه بار امانت رفته‌اند». جملهٔ دوم طولانی‌تر است و همان جمله‌ای است که باید در گزارش بنویسی.

📏 اندازه بگیر: یک سطر یعنی چه؟ در بخشِ ۶ یک سطر یک کتاب است، ولی گروهی که رویش میانگین گرفتیم «کتاب‌های امتیازدار» بود، نه «کتاب‌ها». با کدام شمارشِ دوم سنجیدی؟ با COUNT(*) در برابرِ COUNT(rating) (۱۲۰۰ و ۹۶۹)، با آزمونِ جمعِ اجزا که ۹۶۹ درآمد نه ۱۲۰۰، و با مقایسهٔ دامنهٔ امانتِ دو گروه که هیچ هم‌پوشانی نداشتند. چه کسی از قلم افتاد؟ ۲۳۱ کتابِ کم‌امانت — به‌شکلِ سیستماتیک، نه تصادفی. اگر تصادفی بودند، حذفشان فقط عدد را نویزی‌تر می‌کرد؛ چون سیستماتیک‌اند، عدد را سوگیر می‌کنند.

۷. COALESCE و IFNULL و NULLIF#

COALESCE(a, b, ...) اولین مقدارِ غیرِخالی را برمی‌گرداند. IFNULL(a, b) همان است با دقیقاً دو ورودی. و NULLIF(a, b) برعکس عمل می‌کند: اگر a برابرِ b بود، NULL می‌دهد.

print(q("""
    SELECT id, rating, IFNULL(rating, -1) AS filled, NULLIF(borrowed, 0) AS borrowed_or_null
    FROM books
    WHERE borrowed = 0
    ORDER BY id
    LIMIT 3
"""))

   id rating  filled borrowed_or_null
0  23   None      -1             None
1  51   None      -1             None
2  68   None      -1             None

NULLIF عجیب به نظر می‌رسد تا وقتی کاربردش را ببینی: جلوگیری از تقسیم بر صفر. x / NULLIF(y, 0) وقتی y صفر باشد به‌جای خطا، NULL می‌دهد — یعنی «نامعلوم»، که دقیقاً همان چیزی است که هست. ترمِ ۲ مرتب از این استفاده می‌کند.

۸. NULL در ORDER BY و DISTINCT#

print(q("""
    SELECT id, rating
    FROM books
    ORDER BY rating ASC, id ASC
    LIMIT 3
"""))
print()
print(q("""
    SELECT id, rating
    FROM books
    ORDER BY rating DESC, id ASC
    LIMIT 3
"""))

   id rating
0   3   None
1  12   None
2  18   None

    id  rating
0   69     5.0
1   87     5.0
2  118     5.0

در SQLite، NULL از هر مقداری کوچک‌تر شمرده می‌شود — پس در ترتیبِ صعودی اول می‌آید و در نزولی آخر. این رفتار بینِ پایگاه داده‌های مختلف فرق می‌کند، پس اگر پرسشت قرار است جای دیگری هم اجرا شود، ترتیبِ NULL را صریح بنویس.

و توجه کن که چه اتفاقی افتاد: ORDER BY rating ASC LIMIT 3 سه کتابِ بی‌امتیاز داد، نه سه کتابِ کم‌امتیاز. اگر این را در گزارشی بگذاری، عنوانش دروغ است.

distinct_ratings = q("SELECT DISTINCT rating FROM books ORDER BY rating")
print("تعداد مقدارهای یکتای rating:", len(distinct_ratings))
print("اولین سه مقدار:")
print(distinct_ratings.head(3))
print("چندتاشان NULL هستند؟", int(distinct_ratings["rating"].isna().sum()))

تعداد مقدارهای یکتای rating: 26
اولین سه مقدار:
   rating
0     NaN
1     2.6
2     2.7
چندتاشان NULL هستند؟ 1

در DISTINCT، همهٔ NULLها یک مقدار حساب می‌شوند. بیست‌وشش مقدارِ یکتا داریم که یکی‌شان NULL است — یعنی ۲۵ امتیازِ واقعی. این با قاعدهٔ NULL = NULL → نامعلوم در تناقض به نظر می‌رسد و واقعاً یک استثنای مستند است: DISTINCT و GROUP BY مقایسه‌شان با IS است، نه با =.

🔧 اگر کار نکرد: COALESCE دستِ‌کم دو ورودی می‌خواهد و اگر یکی بدهی صریح اعتراض می‌کند:

try:
    q("SELECT COALESCE(rating) FROM books LIMIT 1")
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)

pandas.errors.DatabaseError
Execution failed on sql 'SELECT COALESCE(rating) FROM books LIMIT 1': wrong number of arguments to function COALESCE()

ولی خطای واقعیِ این فصل هیچ پیامی ندارد — همان NOT IN که صفر داد، همان جمعی که ۹۶۹ شد، و همان ORDER BY که کتابِ بی‌امتیاز را به‌جای کتابِ کم‌امتیاز بالا آورد. هر سه بی‌صدا بودند.

🤖 از دستیارت بپرس: «چرا در SQL مقایسهٔ NULL = NULL به‌جای TRUE مقدارِ UNKNOWN می‌دهد؟» بعد این را بپرس: «DISTINCT چطور NULLها را یکی می‌کند در حالی که NULL = NULL درست نیست؟» — جوابِ درست به تفاوتِ «تساویِ مقدار» و «یکسان بودن» اشاره می‌کند؛ اگر جواب فقط گفت «استثناست»، ناقص است.

واژه‌های تازهٔ این فصل#

در فصل بعد#

تمامِ این ترم روی جدولی کار کردیم که خودمان از قبل ساخته بودیم. فصلِ بعد سراغِ آن ۲۴۰ سطری می‌رود که از فصلِ ۱ روی دیسک منتظرند: می‌سازیمش، پرش می‌کنیم، و می‌بینیم که در ستونِ سالِ همان جدولِ تازه، ۲۷ سطر به‌جای عدد، متن ذخیره می‌شود — همان تلهٔ فصلِ ۵، این بار جلوی چشممان و در لحظهٔ وقوع.