بتل کد Battle Codeدارد باز می‌شود…

جلسه‌ی 6: وصل کردن جدول‌ها Joining Tables

جدول loans به‌جای اسم‌ها فقط شماره‌ها (id) را نگه می‌دارد. چرا؟ و چطور دوباره اسم دانش‌آموز و عنوان کتاب را کنارش بیاوریم؟ با JOIN ... ON جدول‌ها را به هم وصل می‌کنیم، اسم جدول را جلوی ستون می‌نویسیم (s.name) و با LEFT JOIN و IS NULL کسانی را پیدا می‌کنیم که هیچ امانتی ندارند.

در این جلسه یاد می‌گیری

درس

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

🔁 یادت هست؟ GROUP BY. GROUP BY ردیف‌ها را دسته می‌کند و COUNT(*) برای هر دسته جدا می‌شمارد؛ هر دسته یک ردیف جواب می‌شود. امروز همین را روی جدول‌های وصل‌شده می‌زنیم تا بفهمیم هر کس چند کتاب برده.

from db import show

show("SELECT grade, COUNT(*) AS kids FROM students GROUP BY grade ORDER BY grade")

چرا loans شماره نگه می‌دارد؟ هر ردیف loans می‌گوید چه کسی (student_id) چه کتابی (book_id) را کِی (day) برد و آیا برگرداند (returned: ۱ یعنی برگرداند، ۰ یعنی هنوز پیش اوست). اگر به‌جای شماره اسم می‌نوشتیم، اسم سارا بارها تکرار می‌شد، یک غلط تایپی یک «سارای دیگر» می‌ساخت و اگر دو سارا داشتیم قاطی می‌شدند. حالا اسم هر کس فقط یک جا نوشته شده: در students.

from db import show

show("SELECT * FROM loans WHERE student_id = 1")

وصل کردن با JOIN ... ON. FROM loans JOIN students ON loans.student_id = students.id یعنی «هر ردیف loans را کنار همان ردیف students بگذار که idاش با student_id برابر است». بعد از ON می‌گویی کدام ستون‌ها باید برابر باشند. چون هر دو جدول ستون id دارند، اسم جدول را جلوی ستون می‌نویسیم: students.name، loans.day.

from db import show

show("SELECT students.name, loans.day FROM loans JOIN students ON loans.student_id = students.id WHERE loans.book_id = 8 ORDER BY loans.day")

اسم کوتاه و سه جدول. نوشتن students. هر بار طولانی است؛ بعد از اسم جدول یک اسم کوتاه بگذار: FROM loans l JOIN students s ... و بعد s.name. می‌شود JOIN دوم هم زد تا عنوان کتاب هم بیاید. سؤال بلند را در پایتون می‌شود داخل """ ... """ در چند خط نوشت تا خواناتر شود.

from db import show

show("""
SELECT l.day, s.name, b.title
FROM loans l
JOIN students s ON l.student_id = s.id
JOIN books b ON l.book_id = b.id
WHERE s.city = 'Shiraz'
ORDER BY l.day
""")

JOIN با GROUP BY. «هر دانش‌آموز چند کتاب برده؟» اول loans را به students وصل کن، بعد با اسم گروه کن و بشمار. همه‌ی چیزهایی که بلد بودی (WHERE، GROUP BY، HAVING، ORDER BY، LIMIT) بعد از JOIN هم کار می‌کنند.

from db import show

show("""
SELECT s.name, COUNT(*) AS borrowed
FROM loans l
JOIN students s ON l.student_id = s.id
GROUP BY s.name
ORDER BY borrowed DESC, s.name
""")

LEFT JOIN و IS NULL. JOIN فقط ردیف‌هایی را نگه می‌دارد که جفت دارند؛ لیلا که هیچ کتابی نبرده، اصلاً در جواب نیست. LEFT JOIN همه‌ی ردیف‌های جدول چپ (اولی) را نگه می‌دارد و جایی که جفتی نیست، ستون‌های جدول دوم NULL (هیچ) می‌شوند. با WHERE l.id IS NULL همان‌هایی را می‌گیری که جفت ندارند. برای پرسیدن «هیچ است» همیشه IS NULL بنویس، نه = NULL.

from db import show

show("""
SELECT s.name, l.id
FROM students s
LEFT JOIN loans l ON l.student_id = s.id
WHERE l.id IS NULL
""")

اشتباه رایج: ON غلط. ON l.book_id = s.id شماره‌ی کتاب را با شماره‌ی دانش‌آموز مقایسه می‌کند؛ خطا نمی‌دهد، ولی جواب بی‌معنی است (کتاب ۳ را به دانش‌آموز ۳ می‌چسباند). همیشه بپرس: «این شماره، شماره‌ی چیست؟» student_id با s.id، book_id با b.id. اشتباه دیگر: نوشتن id تنها وقتی دو جدول id دارند؛ خطای ambiguous column name: id یعنی «کدام id؟».

مثال‌های این جلسه

چالش‌ها

  1. How Many Genres? (آسان)
  2. Read a Loan (آسان)
  3. Who Took Python for Kids? (متوسط)
  4. The Last Days (متوسط)
  5. Favourite Genres (سخت)
  6. Books Nobody Took (سخت)

بیشتر بخوان

← جلسه‌ی 5: شمردن و گروه کردن جلسه‌ی 7: عوض کردن داده →