چرا داده در یک جدول نمی‌ماند، و اولین `JOIN`

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

یک پرسشِ کاملاً معمولی: شعبهٔ کوشا چند صندلی دارد؟

جدولی که جلوی ماست دو جواب می‌دهد. ۱۲۷ سطر می‌گویند ۶۰ و ۹۸ سطر می‌گویند ۹۶. هیچ‌کدام خطا نمی‌دهند، هیچ‌کدام هشدار نمی‌دهند، و اگر پرسشت اتفاقاً سراغِ یکی از آن دو دسته برود، عددی می‌گیری که کاملاً قابلِ باور است و کاملاً هم ممکن است غلط باشد.

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

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

• بگویی چرا نگه داشتنِ یک واقعیت در هزار سطر دیر یا زود به دو جوابِ متناقض می‌رسد
• دو جدول را با INNER JOIN ... ON کنارِ هم بیاوری
• با نامِ مستعارِ جدول، ابهامِ نامِ ستون را حل کنی و پیامِ خطایش را بخوانی
• ثابت کنی که یک JOIN مشخص، تعدادِ سطرها را عوض نکرده

قبل از شروع#

از ترمِ ۱ و ۲: SELECT و WHERE و GROUP BY، COUNT(*) و COUNT(DISTINCT ...) و SUM، DISTINCT، و اینکه هر مقدار با پارامترِ ? به پرسش می‌رود.

هیچ آشناییِ قبلی با JOIN فرض نشده. تا امروز هر پرسشی که نوشتی یک FROM داشت با یک نامِ جدول. از این فصل به بعد بیشتر از یکی.

دانه‌بندیِ جدول‌هایی که با آن‌ها کار می‌کنیم: یک سطر از books یعنی یک عنوانِ ثبت‌شده در یکی از شعبه‌ها. یک سطر از branches یعنی یک شعبه. یک سطر از loans یعنی یک بار امانت رفتنِ یک کتاب.

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

این همان کتابخانهٔ ترمِ ۱ است، این بار در چند جدول و با یک برشِ تازه از داده — پس عددهای کلی با ترمِ ۱ یکی نیستند و هیچ ادعایی در این ترم به عددهای آنجا گره نمی‌خورد. داده ساختگی است و با یک SEED ثابت ساخته می‌شود، پس روی هر ماشینی همان عددها را می‌گیری.

۱. یک جدولِ پهن، و دو جوابِ متناقض#

books_wide همان چیزی است که آدم اولین بار می‌سازد: یک جدول که همه‌چیز در آن است — مشخصاتِ کتاب، و کنارش مشخصاتِ شعبه‌ای که کتاب در آن است.

print(q("SELECT id, title, branch_name, branch_seats FROM books_wide LIMIT 3"))

   id           title branch_name  branch_seats
0   1       روزهای پل        نسیم           120
1   2  خانه پشت باران        پویا            45
2   3         شب و مه        دانش            90

خواندنش راحت است و برای یک نگاهِ سریع هم بد نیست. مشکل جای دیگری است: branch_seats یک واقعیت دربارهٔ شعبه است، نه دربارهٔ کتاب — و اینجا به‌ازای هر کتاب یک بار نوشته شده.

شعبهٔ کوشا سالِ گذشته سالنِ تازه‌ای اضافه کرد و صندلی‌هایش از ۶۰ به ۹۶ رسید. کسی که این جدول را نگه می‌داشت، سطرهایی را که آن روز جلوی چشمش بود به‌روز کرد. حالا ببین چه چیزی در جدول مانده:

print(q("""
    SELECT branch_name, branch_seats, COUNT(*) AS rows_saying_so
    FROM books_wide
    WHERE branch_name = ?
    GROUP BY branch_name, branch_seats
""", ("کوشا",)))

  branch_name  branch_seats  rows_saying_so
0        کوشا            60             127
1        کوشا            96              98

یک شعبه، دو عددِ صندلی، و جدول هیچ نظری ندارد کدام درست است.

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

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

print("شهرهای متمایز در books_wide:",
      int(q("SELECT COUNT(DISTINCT branch_city) AS n FROM books_wide").iloc[0, 0]))
print("شهرهای متمایز در branches  :",
      int(q("SELECT COUNT(DISTINCT city) AS n FROM branches").iloc[0, 0]))
print()
print(q("SELECT branch_city, COUNT(*) AS n FROM books_wide GROUP BY branch_city ORDER BY n DESC"))

شهرهای متمایز در books_wide: 9
شهرهای متمایز در branches  : 7

  branch_city    n
0      اصفهان  225
1        مشهد  208
2    کرمانشاه  199
3       تهران  197
4       شیراز  187
5       تبریز  168
6       تبريز    6
7    كرمانشاه    5
8       شيراز    5

نُه شهر، در حالی که این کتابخانه شش شهر دارد که کتاب در آن‌ها هست.

سه سطرِ آخر همان نام‌هایند با نویسهٔ عربی — همان تلهٔ فصلِ ۴ ترمِ ۱، این بار در نامِ شهر. شانزده سطر از ۱٬۲۰۰. و دقیقاً به همین دلیل که نامِ شهر ۱٬۲۰۰ بار تایپ شده، ۱٬۲۰۰ فرصت برای غلط تایپ کردنش هم وجود داشته.

✅ چک کن: جمعِ ستونِ n باید دقیقاً ۱۲۰۰ شود. اگر نشد، پرسش را عوض کرده‌ای؛ اگر شد، مطمئنی که هیچ سطری از قلم نیفتاده و آن نُه دسته واقعاً کلِ جدول‌اند.

۲. درمان: هر واقعیت فقط یک جا#

راهِ درست این است که واقعیت‌های شعبه در جدولِ شعبه‌ها بنشینند و در جدولِ کتاب‌ها فقط یک اشاره به آن‌ها بماند.

print(q("SELECT id, name, city, seats FROM branches"))
print()
print(q("SELECT id, title, branch_id, pages FROM books LIMIT 3"))

   id   name      city  seats
0   1  مرکزی     تهران    240
1   2   نسیم      مشهد    120
2   3   دانش     شیراز     90
3   4   بهار     تبریز     75
4   5   کوشا    اصفهان     96
5   6   پویا  کرمانشاه     45
6   7  آفتاب       یزد     30

   id           title  branch_id  pages
0   1       روزهای پل          2    311
1   2  خانه پشت باران          6    206
2   3         شب و مه          3    762

حالا نامِ هر شهر یک بار نوشته شده، نه ۱٬۲۰۰ بار. اگر کوشا فردا صندلی اضافه کند، یک سطر عوض می‌شود و کارِ تمام است. غلطِ تایپی هم اگر پیش بیاید، در یک سطر است و همان‌جا دیده می‌شود.

دو نامِ فنی که از همین‌جا شروع می‌شوند و ترمِ ۴ کاملشان می‌کند — فعلاً همین‌قدر بدان:

• کلیدِ اصلی: ستونی که هر سطر را یکتا می‌کند. در branches این id است.
• کلیدِ خارجی: ستونی که در جدولِ دیگری به آن کلیدِ اصلی اشاره می‌کند. در books این branch_id است.

branch_id عددِ بی‌معنایی نیست؛ یک ارجاع است. و کاری که JOIN می‌کند دقیقاً دنبال کردنِ همین ارجاع است.

۳. کوچک‌ترین JOIN ممکن#

قبل از ۱٬۲۰۰ سطر، روی چیزی که با انگشت می‌شود شمرد. سه کتاب، سه شعبه:

con.execute("DROP TABLE IF EXISTS tiny_books")
con.execute("DROP TABLE IF EXISTS tiny_branches")
con.execute("CREATE TABLE tiny_branches (branch_id INTEGER PRIMARY KEY, city TEXT)")
con.execute("CREATE TABLE tiny_books (id INTEGER PRIMARY KEY, title TEXT, branch_id INTEGER)")
con.executemany("INSERT INTO tiny_branches VALUES (?, ?)",
                [(1, "تهران"), (2, "مشهد"), (3, "شیراز")])
con.executemany("INSERT INTO tiny_books VALUES (?, ?, ?)",
                [(1, "الف", 1), (2, "ب", 1), (3, "پ", 2)])
con.commit()

print(q("SELECT * FROM tiny_books"))
print()
print(q("SELECT * FROM tiny_branches"))

   id title  branch_id
0   1   الف          1
1   2     ب          1
2   3     پ          2

   branch_id   city
0          1  تهران
1          2   مشهد
2          3  شیراز

جوابِ «هر کتاب در چه شهری است؟» را با چشم می‌شود داد: الف و ب در تهران، پ در مشهد. حالا همان را از پایگاه داده بخواه:

print(q("""
    SELECT tiny_books.title, tiny_branches.city
    FROM tiny_books
    JOIN tiny_branches ON tiny_branches.branch_id = tiny_books.branch_id
"""))

  title   city
0   الف  تهران
1     ب  تهران
2     پ   مشهد

سه سطر رفت، سه سطر برگشت — و همان سه‌تایی که با انگشت شمردی.

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

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

وقتی ستونِ کلید در هر دو جدول دقیقاً یک نام داشته باشد، USING کوتاه‌ترش می‌کند:

print(q("""
    SELECT title, city
    FROM tiny_books
    JOIN tiny_branches USING (branch_id)
"""))

  title   city
0   الف  تهران
1     ب  تهران
2     پ   مشهد

همان نتیجه، با نوشتنِ کمتر. ولی USING فقط وقتی کار می‌کند که دو ستون هم‌نام باشند، و جدول‌های واقعیِ ما نیستند: در books نامش branch_id است و در branches نامش id. برای همین ON ابزارِ عمومی است و USING یک میان‌بُر.

۴. همان کار روی ۱٬۲۰۰ سطر#

print(q("""
    SELECT b.title, br.name AS branch, br.city
    FROM books AS b
    JOIN branches AS br ON br.id = b.branch_id
    ORDER BY b.id
    LIMIT 3
"""))
print()
print("سطرهای books                :",
      int(q("SELECT COUNT(*) AS n FROM books").iloc[0, 0]))
print("سطرهای نتیجهٔ JOIN          :",
      int(q("""
          SELECT COUNT(*) AS n
          FROM books AS b
          JOIN branches AS br ON br.id = b.branch_id
      """).iloc[0, 0]))

            title branch      city
0       روزهای پل   نسیم      مشهد
1  خانه پشت باران   پویا  کرمانشاه
2         شب و مه   دانش     شیراز

سطرهای books                : 1200
سطرهای نتیجهٔ JOIN          : 1200

۱۲۰۰ سطر رفت، ۱۲۰۰ سطر برگشت. یک ستون اضافه شد و تعدادِ سطرها دست‌نخورده ماند.

این جمله را جدی بگیر، چون ادعای این فصل است، نه قاعدهٔ عمومیِ JOIN. اینجا درست است چون هر کتاب دقیقاً به یک شعبه اشاره می‌کند: از سمتِ books رابطه «یک‌به‌یک» است. فصلِ ۳ همین JOIN را به جدولی می‌زند که یک کتاب چند سطر در آن دارد، و آنجا تعدادِ سطرها عوض می‌شود — بی‌صدا.

نامِ مستعارِ جدول (AS b و AS br) از این فصل به بعد در هر پرسشی می‌بینی. کارش فقط کوتاه کردن نیست؛ در پرسشی که یک جدول دو بار در آن آمده — فصلِ ۴ — تنها راهِ حرف زدن دربارهٔ هرکدام است.

۵. ابهامِ نامِ ستون#

هر دو جدول ستونی به نامِ id دارند. اگر بدونِ پیشوند صدایش کنی، SQLite نمی‌داند کدام را می‌خواهی — و برخلافِ خیلی از خطاهای این دوره، این یکی صریح اعتراض می‌کند:

🔧 اگر کار نکرد: پیامِ زیر یعنی نامِ ستون بینِ دو جدول مشترک است و باید بگویی از کدام جدول:

try:
    q("""
        SELECT id, name
        FROM books AS b
        JOIN branches AS br ON br.id = b.branch_id
        LIMIT 1
    """)
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)

pandas.errors.DatabaseError
Execution failed on sql '
        SELECT id, name
        FROM books AS b
        JOIN branches AS br ON br.id = b.branch_id
        LIMIT 1
    ': ambiguous column name: id

b.id یا br.id — هر کدام را که بخواهی، باید بگویی. جالب اینکه name مشکلی نداشت، چون فقط branches ستونی به این نام دارد.

و یک عادت که از همین‌جا برایت می‌سازیم: در پرسشی که بیشتر از یک جدول دارد، هر ستون را با پیشوندِ جدولش بنویس، حتی وقتی مجبور نیستی. پرسشی که امروز بی‌ابهام است، فردا با اضافه شدنِ یک ستون به یکی از دو جدول مبهم می‌شود — و آن‌وقت پرسشی که سالِ گذشته کار می‌کرد، ناگهان خطا می‌دهد.

۶. ترتیبِ نوشتنِ جدول‌ها نتیجه را عوض نمی‌کند#

سؤالِ طبیعیِ هر کسی که اولین JOINش را نوشته: کدام جدول باید اول بیاید؟

left_first = q("""
    SELECT COUNT(*) AS rows, SUM(b.pages) AS pages
    FROM books AS b
    JOIN branches AS br ON br.id = b.branch_id
""")
right_first = q("""
    SELECT COUNT(*) AS rows, SUM(b.pages) AS pages
    FROM branches AS br
    JOIN books AS b ON b.branch_id = br.id
""")
print("books اول :", int(left_first["rows"][0]), "سطر |", int(left_first["pages"][0]), "صفحه")
print("branches اول:", int(right_first["rows"][0]), "سطر |", int(right_first["pages"][0]), "صفحه")
print("یکی‌اند؟", left_first.equals(right_first))

books اول : 1200 سطر | 588049 صفحه
branches اول: 1200 سطر | 588049 صفحه
یکی‌اند؟ True

برای INNER JOIN جای دو جدول فرقی نمی‌کند. مجموعهٔ سطرهای نتیجه یکی است.

دو نکته که این جمله را از یک شعار به یک ادعای دقیق تبدیل می‌کنند: اولاً ترتیبِ نمایشِ سطرها تضمین‌شده نیست — همان قاعدهٔ فصلِ ۴ ترمِ ۱، که اگر ترتیب برایت مهم است ORDER BY بنویس. و ثانیاً این تقارن مالِ INNER است؛ برای LEFT JOIN که فصلِ بعد می‌آید، جای دو جدول کاملاً نتیجه را عوض می‌کند، و این تفاوت دلیلِ وجودِ آن فصل است.

📏 اندازه بگیر: یک سطر یعنی چه؟ یک سطر از نتیجهٔ بخشِ ۴ یعنی «یک کتاب، به‌همراهِ مشخصاتِ شعبه‌اش» — دقیقاً همان دانه‌بندیِ books، نه چیزِ تازه‌ای. با کدام شمارشِ دوم سنجیدی؟ با COUNT(*) قبل و بعد از JOIN که هر دو ۱۲۰۰ شدند، و با SUM(pages) که از دو ترتیبِ متفاوتِ نوشتن یک عدد داد. چه کسی از قلم افتاد؟ شعبهٔ آفتاب: هفت شعبه داریم و در نتیجهٔ JOIN فقط شش‌تا دیده می‌شوند، چون هیچ کتابی به آفتاب اشاره نمی‌کند. INNER JOIN بی‌صدا انداختش بیرون و هیچ عددی در این فصل به تو نگفت.

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

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

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

در فصل بعد#

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