آوند — داده: SQL، مدل‌سازی و تحلیل برای تصمیم

فصل ۲ از ۸

پیشرفت ترم
۰٪

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

فصلِ قبل با یک اعتراف تمام شد: شعبهٔ آفتاب از نتیجهٔ JOIN بیرون افتاد و هیچ عددی به ما نگفت. حالا می‌شماریمشان.

۲۰۰ کتاب هرگز امانت نرفته‌اند، ۴۲ عضو هرگز کتابی نبرده‌اند، و ۱ شعبه هیچ کتابی ندارد. هر سه گروه با INNER JOIN بی‌صدا ناپدید می‌شوند — و هر سه گروه دقیقاً همان‌هایی هستند که یک گزارشِ خوب باید دربارهٔ آن‌ها حرف بزند. کتابی که هیچ‌کس نمی‌برد، مسئله است؛ عضوی که هیچ‌وقت برنمی‌گردد، مسئله است.

و بعد یک تله: پرسشی که با LEFT JOIN نوشته شده و یک شرطِ ساده در جای اشتباه، بی‌صدا به INNER JOIN برش می‌گرداند. تفاوتِ دو نسخه در این فصل ۵۱۶ در برابرِ ۱٬۲۰۰ کتاب است.

دو صافیِ پشتِ سرِ هم که یکی همهٔ دانه‌ها را رد می‌کند و دیگری فقط دانه‌های جفت‌دار را نگه می‌دارد

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

  • با LEFT JOIN تضمین کنی که هیچ سطری از جدولِ چپ گم نمی‌شود
  • سطرهایی را که جفت ندارند با IS NULL پیدا کنی و بشماری
  • بگویی چرا COUNT(*) بعد از یک LEFT JOIN عددِ غلط می‌دهد
  • تشخیص بدهی یک شرط باید در ON بنشیند یا در WHERE، و تفاوتش را با دو عدد نشان بدهی

قبل از شروع#

از فصلِ ۱: INNER JOIN ... ON، نامِ مستعارِ جدول، و اینکه INNER فقط جفت‌های کامل را نگه می‌دارد.

از ترمِ ۱ فصلِ ۶: NULL یعنی «نمی‌دانیم»، و IS NULL تنها راهِ درستِ پرسیدن از آن است. این فصل همان درس را در جای تازه‌ای به کار می‌گیرد.

دانه‌بندی: یک سطر از loans یعنی یک بار امانت رفتنِ یک کتاب توسطِ یک عضو. یک کتاب می‌تواند صفر، یک، یا ده‌ها سطر در این جدول داشته باشد.

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

جدول تعدادِ سطر زمانِ ساخت
books ۱٬۲۰۰ کمتر از یک ثانیه
branches ۷ آنی
members ۳۰۰ آنی
loans ۳٬۹۹۸ کمتر از یک ثانیه

۱. پرسشی که INNER JOIN نمی‌تواند جواب بدهد#

«هر شعبه چند کتاب دارد؟» — با ابزارِ فصلِ قبل جوابش را می‌گیریم:

print(q("""
    SELECT br.name, COUNT(*) AS books
    FROM branches AS br
    JOIN books AS b ON b.branch_id = br.id
    GROUP BY br.id, br.name
    ORDER BY books DESC
"""))
    name  books
0   کوشا    225
1   نسیم    208
2   پویا    204
3  مرکزی    197
4   دانش    192
5   بهار    174

شش سطر، در حالی که هفت شعبه داریم.

این گزارش غلط نیست؛ ناقص است، و بدتر از غلط بودن این است که ناقص بودنش از رویش پیدا نیست. کسی که این جدول را می‌بیند فرض می‌کند شش شعبه وجود دارد. شعبهٔ آفتاب — که تازه باز شده و هنوز کتابی ندارد — دقیقاً همان چیزی است که مدیرِ کتابخانه باید ببیند، و دقیقاً همان چیزی است که INNER JOIN انداخته بیرون.

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

۲. LEFT JOIN: هیچ سطری از جدولِ چپ گم نمی‌شود#

LEFT JOIN می‌گوید: همهٔ سطرهای جدولِ چپ را نگه دار. اگر سطری در جدولِ راست جفت داشت، کنارش بگذار؛ اگر نداشت، ستون‌های راست را NULL بگذار و سطر را دور نینداز.

print(q("""
    SELECT br.name, COUNT(b.id) AS books
    FROM branches AS br
    LEFT JOIN books AS b ON b.branch_id = br.id
    GROUP BY br.id, br.name
    ORDER BY books DESC
"""))
    name  books
0   کوشا    225
1   نسیم    208
2   پویا    204
3  مرکزی    197
4   دانش    192
5   بهار    174
6  آفتاب      0

هفت سطر، و صفرِ آخر یک عددِ واقعی است، نه یک جای خالی.

«چپ» و «راست» یعنی ترتیبی که در پرسش نوشته‌ای: جدولِ بعد از FROM چپ است، جدولِ بعد از JOIN راست. و همین‌جاست که تقارنِ فصلِ قبل از بین می‌رود: برای INNER JOIN جای دو جدول فرقی نمی‌کرد؛ برای LEFT JOIN جای دو جدول کلِ پرسش را عوض می‌کند، چون تعیین می‌کند کدام طرف حقِ گم شدن ندارد.

حالا همان کار روی جدولِ بزرگ‌تر. اینجا شمارشِ سطرهای خام گویاتر است:

inner_rows = int(q("""
    SELECT COUNT(*) AS n FROM books AS b JOIN loans AS l ON l.book_id = b.id
""").iloc[0, 0])
left_rows = int(q("""
    SELECT COUNT(*) AS n FROM books AS b LEFT JOIN loans AS l ON l.book_id = b.id
""").iloc[0, 0])

print("سطرهای loans           :", int(q("SELECT COUNT(*) AS n FROM loans").iloc[0, 0]))
print("سطرهای نتیجهٔ INNER JOIN:", inner_rows)
print("سطرهای نتیجهٔ LEFT JOIN :", left_rows)
print("اختلاف                 :", left_rows - inner_rows)
سطرهای loans           : 3998
سطرهای نتیجهٔ INNER JOIN: 3998
سطرهای نتیجهٔ LEFT JOIN : 4198
اختلاف                 : 200

اختلاف دقیقاً ۲۰۰ سطر است، و این عدد شمارشِ دومِ ماست: هر کتابی که هیچ امانتی ندارد، در نتیجهٔ LEFT JOIN دقیقاً یک سطر می‌گیرد — سطری با ستون‌های خالی. پس ۴٬۱۹۸ منهای ۳٬۹۹۸ باید برابرِ تعدادِ کتاب‌های بی‌امانت باشد. بخشِ ۴ همان عدد را از راهِ کاملاً دیگری هم می‌گیرد.

✅ چک کن: INNER JOIN اینجا دقیقاً ۳٬۹۹۸ سطر داد، یعنی برابرِ کلِ loans. این اتفاقی نیست: هر سطرِ loans به یک کتابِ موجود اشاره می‌کند، پس هیچ امانتی گم نشد. اگر عددِ تو کمتر بود، یعنی سطرهایی در loans هست که به کتابی اشاره می‌کنند که وجود ندارد — و آن مسئلهٔ دیگری است که ترمِ ۴ نامش را می‌گذارد.

۳. تلهٔ COUNT(*) بعد از LEFT JOIN#

در بخشِ ۲ نوشتیم COUNT(b.id)، نه COUNT(*). این انتخاب نبود، اجبار بود:

print(q("""
    SELECT b.id,
           COUNT(*)     AS with_star,
           COUNT(l.id)  AS with_column
    FROM books AS b
    LEFT JOIN loans AS l ON l.book_id = b.id
    WHERE b.id IN (?, ?)
    GROUP BY b.id
""", (2, 14)))
   id  with_star  with_column
0   2          1            0
1  14          1            0

این دو کتاب هرگز امانت نرفته‌اند، و COUNT(*) برایشان ۱ گفت.

دلیلش دقیقاً همان تعریفِ ترمِ ۱ است: COUNT(*) سطرها را می‌شمارد، و LEFT JOIN برای این کتاب یک سطر ساخته — سطری که تمامِ ستون‌های سمتِ راستش NULL است. COUNT(l.id) سطرهایی را می‌شمارد که در آن ستون مقدار دارند، و اینجا هیچ‌کدام ندارند.

این خطا هیچ پیامی ندارد. یک گزارشِ «کم‌محبوب‌ترین کتاب‌ها» که با COUNT(*) نوشته شده باشد، هیچ کتابی با صفر امانت پیدا نمی‌کند — چون همه‌شان یک شده‌اند. بعد از هر LEFT JOIN، ستونی از جدولِ راست را بشمار، نه ستاره را.

۴. الگوی «آنچه نیست»#

اگر بعد از یک LEFT JOIN فقط سطرهایی را نگه داری که سمتِ راستشان NULL است، دقیقاً همان‌هایی می‌مانند که جفت ندارند. این پرکاربردترین الگوی این فصل است:

never_borrowed = int(q("""
    SELECT COUNT(*) AS n
    FROM books AS b
    LEFT JOIN loans AS l ON l.book_id = b.id
    WHERE l.id IS NULL
""").iloc[0, 0])
quiet_members = int(q("""
    SELECT COUNT(*) AS n
    FROM members AS m
    LEFT JOIN loans AS l ON l.member_id = m.id
    WHERE l.id IS NULL
""").iloc[0, 0])
empty_branches = int(q("""
    SELECT COUNT(*) AS n
    FROM branches AS br
    LEFT JOIN books AS b ON b.branch_id = br.id
    WHERE b.id IS NULL
""").iloc[0, 0])

print("کتاب‌هایی که هرگز امانت نرفته‌اند:", never_borrowed, "از 1200")
print("اعضایی که هرگز کتابی نبرده‌اند  :", quiet_members, "از 300")
print("شعبه‌هایی که هیچ کتابی ندارند   :", empty_branches, "از 7")
کتاب‌هایی که هرگز امانت نرفته‌اند: 200 از 1200
اعضایی که هرگز کتابی نبرده‌اند  : 42 از 300
شعبه‌هایی که هیچ کتابی ندارند   : 1 از 7

۲۰۰ همان عددی است که در بخشِ ۲ از تفریقِ ۴٬۱۹۸ و ۳٬۹۹۸ درآمد — دو مسیرِ کاملاً مستقل، یک جواب. این شمارشِ دوم است و بدونِ آن هیچ‌کدام از این سه عدد را نباید باور کنی.

و توجه کن این سه عدد چه چیزی هستند: هر سه دربارهٔ چیزی حرف می‌زنند که در جدولِ loans وجود ندارد. ۴۲ عضوی که هرگز کتابی نبردند، در هیچ سطری از loans نیستند؛ برای پیدا کردنشان باید از جدولی شروع می‌کردی که هستند.

۵. شرطی که LEFT را بی‌صدا به INNER تبدیل می‌کند#

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

con.execute("DROP TABLE IF EXISTS tiny_books")
con.execute("DROP TABLE IF EXISTS tiny_loans")
con.execute("CREATE TABLE tiny_books (id INTEGER PRIMARY KEY, title TEXT)")
con.execute("CREATE TABLE tiny_loans (id INTEGER PRIMARY KEY, book_id INTEGER, loaned_at TEXT)")
con.executemany("INSERT INTO tiny_books VALUES (?, ?)",
                [(1, "الف"), (2, "ب"), (3, "پ")])
con.executemany("INSERT INTO tiny_loans VALUES (?, ?, ?)",
                [(1, 1, "2025-03-02"), (2, 1, "2024-05-11"), (3, 2, "2024-08-20")])
con.commit()

print(q("""
    SELECT t.title, n.loaned_at
    FROM tiny_books AS t
    LEFT JOIN tiny_loans AS n ON n.book_id = t.id
    ORDER BY t.id, n.id
"""))
  title   loaned_at
0   الف  2025-03-02
1   الف  2024-05-11
2     ب  2024-08-20
3     پ         NaN

چهار سطر: الف دو بار، ب یک بار، و پ با یک NaN — این همان NULLِ پایگاه داده است. loaned_at ستونِ متنی است و نسخه‌های تازهٔ pandas در چنین ستونی هم NaN چاپ می‌کنند؛ نسخه‌های قدیمی‌تر None نشان می‌دهند. معنی هر دو یکی است.

حالا پرسشِ کاملاً معقولِ «امانت‌های سالِ ۲۰۲۵ را نشانم بده» را به همین اضافه کن — یک بار در WHERE و یک بار در ON:

in_where = q("""
    SELECT t.title, n.loaned_at
    FROM tiny_books AS t
    LEFT JOIN tiny_loans AS n ON n.book_id = t.id
    WHERE n.loaned_at >= ?
    ORDER BY t.id
""", ("2025-01-01",))
in_on = q("""
    SELECT t.title, n.loaned_at
    FROM tiny_books AS t
    LEFT JOIN tiny_loans AS n ON n.book_id = t.id AND n.loaned_at >= ?
    ORDER BY t.id
""", ("2025-01-01",))

print("شرط در WHERE —", len(in_where), "سطر")
print(in_where)
print()
print("شرط در ON    —", len(in_on), "سطر")
print(in_on)
شرط در WHERE — 1 سطر
  title   loaned_at
0   الف  2025-03-02

شرط در ON    — 3 سطر
  title   loaned_at
0   الف  2025-03-02
1     ب         NaN
2     پ         NaN

یک سطر در برابرِ سه سطر. جای یک شرط عوض شد، و دو کتاب از سه کتاب ناپدید شدند.

مکانیزمش دقیقاً همان منطقِ سه‌ارزشیِ ترمِ ۱ است. LEFT JOIN برای کتابِ «پ» سطری ساخت که loaned_atش NULL است. بعد WHERE پرسید «آیا این NULL از ۲۰۲۵ بزرگ‌تر است؟» و جواب نامعلوم بود — نه درست. WHERE سطرهایی را نگه می‌دارد که شرطشان درست است، پس «پ» افتاد بیرون. کتابِ «ب» هم افتاد چون امانتش مالِ سالِ قبل بود.

و نتیجه‌اش این است: یک LEFT JOIN که شرطی روی جدولِ راستش در WHERE دارد، دقیقاً یک INNER JOIN است — با ظاهری که خلافش را می‌گوید.

روی جدولِ واقعی، هزینه‌اش این است. دقت کن COUNT(DISTINCT b.id) نوشتیم، نه COUNT(*): یک کتاب می‌تواند چند بار امانت رفته باشد و در نتیجهٔ این پیوند چند سطر داشته باشد، و ما داریم کتاب می‌شماریم نه سطر. فصلِ بعد کاملاً دربارهٔ همین است.

where_version = int(q("""
    SELECT COUNT(DISTINCT b.id) AS n
    FROM books AS b
    LEFT JOIN loans AS l ON l.book_id = b.id
    WHERE l.loaned_at >= ?
""", ("2025-01-01",)).iloc[0, 0])
on_version = int(q("""
    SELECT COUNT(DISTINCT b.id) AS n
    FROM books AS b
    LEFT JOIN loans AS l ON l.book_id = b.id AND l.loaned_at >= ?
""", ("2025-01-01",)).iloc[0, 0])

print("کتاب‌هایی که در گزارش دیده می‌شوند — شرط در WHERE:", where_version)
print("کتاب‌هایی که در گزارش دیده می‌شوند — شرط در ON   :", on_version)
print("کتاب‌هایی که فقط در نسخهٔ دوم دیده می‌شوند       :", on_version - where_version)
کتاب‌هایی که در گزارش دیده می‌شوند — شرط در WHERE: 516
کتاب‌هایی که در گزارش دیده می‌شوند — شرط در ON   : 1200
کتاب‌هایی که فقط در نسخهٔ دوم دیده می‌شوند       : 684

۶۸۴ کتاب از گزارشِ «امانت‌های امسال» غایب‌اند، و دقیقاً همان‌هایی هستند که امسال امانت نرفته‌اند. یعنی گزارشی که قرار بود کم‌کارترین کتاب‌ها را نشان بدهد، همان‌ها را حذف کرده.

🔧 اگر کار نکرد: یک اشتباهِ رایج در همین ساختار خطا می‌دهد و خوش‌شانسی است که می‌دهد — وقتی شرط را در ON بگذاری ولی نامِ مستعار را جا بیندازی:

try:
    q("""
        SELECT t.title, n.loaned_at
        FROM tiny_books AS t
        LEFT JOIN tiny_loans AS n ON n.book_id = t.id AND loan_date >= ?
    """, ("2025-01-01",))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
pandas.errors.DatabaseError
Execution failed on sql '
        SELECT t.title, n.loaned_at
        FROM tiny_books AS t
        LEFT JOIN tiny_loans AS n ON n.book_id = t.id AND loan_date >= ?
    ': no such column: loan_date

ولی خطای واقعیِ این فصل همان ۵۱۶ است که هیچ پیامی نداشت.

۶. پس شرط را کجا بگذارم#

قاعده یک جمله است و از خودِ معنیِ دو بند بیرون می‌آید. ON می‌گوید کدام سطرها با هم جفت‌اند؛ WHERE می‌گوید کدام سطرها در نتیجه بمانند.

  • شرط دربارهٔ جدولِ چپ است؟ در WHERE بگذار. سطرهای چپی که شرط را ندارند، اصلاً نباید در گزارش باشند.
  • شرط دربارهٔ جدولِ راست است و می‌خواهی سطرهای بی‌جفت بمانند؟ در ON بگذار.
  • شرط دربارهٔ جدولِ راست است و واقعاً می‌خواهی سطرهای بی‌جفت بیفتند؟ در WHERE بگذار — ولی آن‌وقت صادقانه‌تر است که همان اول INNER JOIN بنویسی، چون همان است و خواننده‌ات گمراه نمی‌شود.

و یک استثنای مهم که در بخشِ ۴ از آن استفاده کردیم: WHERE l.id IS NULL هم شرطی روی جدولِ راست در WHERE است، و عمداً همان کار را می‌کند. تفاوتش این است که آن شرط فقط برای سطرهای بی‌جفت درست است، پس به‌جای اینکه LEFT را به INNER تبدیل کند، دقیقاً برعکسش می‌کند: فقط سطرهای بی‌جفت را نگه می‌دارد. همان ابزار، همان جا، دو نتیجهٔ متضاد — و تفاوت در این است که شرط با NULL چه می‌کند.

📏 اندازه بگیر: یک سطر یعنی چه؟ در جدولِ بخشِ ۲ یک سطر یعنی یک شعبه؛ در نتیجهٔ خامِ LEFT JOIN بخشِ ۲ یک سطر یعنی «یک امانت، یا یک کتابِ بی‌امانت» — دو چیزِ متفاوت در یک ستون، و دقیقاً به همین دلیل COUNT(*) رویش بی‌معنی است. با کدام شمارشِ دوم سنجیدی؟ عددِ ۲۰۰ را دو بار گرفتیم: یک بار از تفریقِ ۴٬۱۹۸ و ۳٬۹۹۸، و یک بار مستقیم با WHERE l.id IS NULL. چه کسی از قلم افتاد؟ در نسخهٔ WHEREی بخشِ ۵، ۶۸۴ کتاب — و هیچ‌کدام از عددهای آن گزارش این را لو نمی‌داد.

🤖 از دستیارت بپرس: «چرا یک شرط روی جدولِ راست در WHERE، یک LEFT JOIN را به INNER JOIN تبدیل می‌کند؟» بعد این را بپرس: «اگر شرط IS NULL باشد چه؟ آن هم LEFT را به INNER تبدیل می‌کند؟» — جوابِ درست می‌گوید نه، و دلیلش را به این برمی‌گرداند که این تنها شرطی است که برای سطرهای بی‌جفت درست است. اگر جواب فقط قاعده‌ای تکرار کرد و به NULL اشاره نکرد، ناقص است.

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

کلمه تلفظ به حروف فارسی یعنی چه
LEFT JOIN لفت جوین همهٔ سطرهای جدولِ چپ می‌مانند، جفت داشته باشند یا نه
outer join آوتر جوین نامِ خانوادگیِ پیوندهایی که سطرِ بی‌جفت را نگه می‌دارند
anti-join آنتی‌جوین الگوی «آنچه نیست»: LEFT JOIN به‌علاوهٔ IS NULL
unmatched row آن‌مچد رو سطری که در طرفِ دیگر هیچ جفتی ندارد

تمرین‌ها

اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.

در فصل بعد#

در همین فصل یک عدد بی‌سروصدا از کنارمان رد شد: پیوندِ books به loans نتیجه‌ای با ۴٬۱۹۸ سطر ساخت، در حالی که books فقط ۱٬۲۰۰ سطر دارد. تعدادِ سطرها بیشتر شد و ما فقط از آن برای شمردنِ کتاب‌های بی‌امانت استفاده کردیم.

فصلِ بعد نشان می‌دهد همان اتفاق چطور یک SUM را خراب می‌کند: جمعِ صفحاتِ این کتابخانه ۵۸۸٬۰۴۹ است و بعد از یک JOIN می‌شود ۱٬۹۴۶٬۱۰۳ — بدونِ هیچ خطایی، و با رتبه‌بندیِ شعبه‌ها که کاملاً وارونه می‌شود.

به آخر این فصل رسیدی!

اگر ساختی و جواب داد، این دکمه مال توست.