`WHERE`: کدام سطرها

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

دو پرسش می‌نویسیم که تنها تفاوتشان یک جفت پرانتز است. اولی ۱۸۱ سطر برمی‌گرداند، دومی ۳۵ سطر. هیچ‌کدام خطا نمی‌دهند، هیچ‌کدام هشدار نمی‌دهند، و اگر عددشان را در یک گزارش بگذاری هیچ‌کس از روی خودِ عدد نمی‌فهمد کدامش را نوشته‌ای.

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

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

• با WHERE فقط سطرهایی را بگیری که شرطی را برآورده می‌کنند
• بگویی AND و OR در یک شرطِ ترکیبی به چه ترتیبی خوانده می‌شوند
• با شکستنِ یک شرطِ ترکیبی به تکه‌هایش، درستیِ عددت را ثابت کنی
• BETWEEN و IN و LIKE را جای زنجیرهٔ طولانیِ شرط بنشانی
• سطرهایی را که مقدارشان خالی است پیدا کنی

قبل از شروع#

از فصلِ ۲: SELECT و FROM و LIMIT و AS، و COUNT(*).

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

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

۱. WHERE: بندی که سطرها را غربال می‌کند#

WHERE بعد از FROM می‌آید و یک شرط می‌گیرد. هر سطری که شرط برایش درست باشد می‌ماند، بقیه نه.

عملگرهای مقایسه همان‌هایی هستند که در پایتون می‌شناسی، با یک استثنا: «نامساوی» در SQL معمولاً <> نوشته می‌شود (اگرچه != هم کار می‌کند).

print(q("SELECT title, category, year, borrowed FROM books WHERE borrowed > 180 LIMIT 5"))
print()
print("چند کتاب بیشتر از ۱۸۰ بار امانت رفته‌اند؟",
      int(q("SELECT COUNT(*) FROM books WHERE borrowed > 180").iloc[0, 0]))

              title    category  year  borrowed
0  در جست‌وجوی آینه       فلسفه  2008       185
1     کوچه پشت آینه         هنر  2002       188
2       خاک و ستاره  زندگی‌نامه  2019       182
3    بازگشت به خانه       فلسفه  2019       190
4      روزهای باران        رمان  2017       183

چند کتاب بیشتر از ۱۸۰ بار امانت رفته‌اند؟ 41

دقت کن که LIMIT 5 و COUNT(*) دو کارِ متفاوت کردند. اولی پنج سطر از ۴۱ سطر را نشان داد، دومی گفت آن ۴۱ چندتاست. اگر فقط اولی را می‌دیدی، هیچ تصوری از اندازهٔ نتیجه نداشتی. عادتِ خوب: هر پرسشِ فیلترداری را یک بار با COUNT(*) هم بزن.

و به ترتیبِ کارها دقت کن، چون از اینجا به بعد مدام به کارت می‌آید: WHERE قبل از شمردن اجرا می‌شود. یعنی COUNT(*) سطرهای بازمانده را می‌شمارد، نه سطرهای جدول را. همین یک جمله توضیح می‌دهد که چرا عددِ ۴۱ با عددِ ۱۲۰۰ی که در فصلِ ۲ گرفتیم فرق دارد — و در ترمِ ۲، وقتی GROUP BY و HAVING هم اضافه شوند، ترتیبِ این مرحله‌ها خودش موضوعِ یک فصل می‌شود.

و حالا مقدار را با ? می‌فرستیم:

print(q("SELECT COUNT(*) AS n FROM books WHERE category = ?", ("شعر",)))

     n
0  164

مقدارِ "شعر" داخلِ متنِ پرسش نیست؛ در یک tuple جدا رفت. دقت کن که آن tuple حتماً باید کاما داشته باشد — ("شعر",) یک tuple یک‌عضوی است، ولی ("شعر") فقط یک رشته است و sqlite3 آن را به‌عنوانِ فهرستی از تک‌کاراکترها می‌بیند.

۲. تلهٔ تقدم: AND قبل از OR می‌بندد#

پرسشِ واقعی: «کتاب‌های شعر یا رمان که از سال ۲۰۲۰ به بعد منتشر شده‌اند چندتاست؟»

جملهٔ فارسی مبهم نیست: «از سال ۲۰۲۰ به بعد» آشکارا به هر دو دسته می‌خورد، نه فقط به دومی. کسی که این جمله را می‌شنود یک لحظه هم شک نمی‌کند.

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

loose = int(q("""
    SELECT COUNT(*) FROM books
    WHERE category = ? OR category = ? AND year >= ?
""", ("شعر", "رمان", 2020)).iloc[0, 0])

tight = int(q("""
    SELECT COUNT(*) FROM books
    WHERE (category = ? OR category = ?) AND year >= ?
""", ("شعر", "رمان", 2020)).iloc[0, 0])

print("بدون پرانتز:", loose)
print("با پرانتز  :", tight)
print("اختلاف     :", loose - tight, "سطر")

بدون پرانتز: 181
با پرانتز  : 35
اختلاف     : 146 سطر

صد و چهل و شش سطر اختلاف، و هیچ‌کدام از این دو پرسش خطا نداد.

قاعده این است: AND محکم‌تر از OR می‌چسبد. یعنی پرسشِ اول در واقع این‌طور خوانده شد:

WHERE category = 'شعر'  OR  (category = 'رمان' AND year >= 2020)

یعنی «همهٔ شعرها، به‌اضافهٔ رمان‌های ۲۰۲۰ به بعد» — که اصلاً پرسشِ ما نبود. فیلترِ سال روی شعرها اعمال نشد.

🌱 ریشه‌اش کجاست: این دقیقاً همان چیزی است که در حساب می‌گوید «ضرب قبل از جمع». ۲ + ۳ × ۴ می‌شود ۱۴ نه ۲۰، چون ضرب محکم‌تر می‌چسبد — و این یک کشفِ ریاضی نیست، یک توافق است که آدم‌ها کردند تا یک نوشته دو خوانش نداشته باشد. AND و OR هم دقیقاً همان توافق را دارند. داستانِ کاملش در ریشه ترمِ ۱ فصل ۷ آمده، و اگر آن فصل را خوانده باشی این بخش برایت تکرارِ یک ایدهٔ آشناست، نه یک قاعدهٔ تازه.

قاعدهٔ عملیِ این دوره: هر جا AND و OR در یک شرط با هم آمدند، پرانتز بگذار — حتی وقتی لازم نیست. پرانتزِ اضافه هیچ هزینه‌ای ندارد؛ پرانتزِ جاافتاده ۱۴۶ سطر هزینه دارد.

۳. حالا ثابتش کن: شمارشِ دوم#

نگفتنِ «کدام درست است» کافی نیست. آن دو عدد را از راهِ دیگری بساز و ببین کدام می‌خواند.

شرطِ ترکیبی را به تکه‌های ساده‌اش می‌شکنیم و هرکدام را جدا می‌شماریم:

print("شعرهای همه سال‌ها      :",
      int(q("SELECT COUNT(*) FROM books WHERE category = ?", ("شعر",)).iloc[0, 0]))
print("رمان‌های ۲۰۲۰ به بعد   :",
      int(q("SELECT COUNT(*) FROM books WHERE category = ? AND year >= ?",
            ("رمان", 2020)).iloc[0, 0]))
print("شعرهای ۲۰۲۰ به بعد     :",
      int(q("SELECT COUNT(*) FROM books WHERE category = ? AND year >= ?",
            ("شعر", 2020)).iloc[0, 0]))

شعرهای همه سال‌ها      : 164
رمان‌های ۲۰۲۰ به بعد   : 17
شعرهای ۲۰۲۰ به بعد     : 18

حالا هر دو عدد را با دست می‌سازیم:

• خوانشِ بدونِ پرانتز = همهٔ شعرها + رمان‌های ۲۰۲۰ به بعد = ۱۶۴ + ۱۷ = ۱۸۱ ✔
• خوانشِ با پرانتز = شعرهای ۲۰۲۰ به بعد + رمان‌های ۲۰۲۰ به بعد = ۱۸ + ۱۷ = ۳۵ ✔

هر دو دقیقاً درآمدند. این کارِ نیم‌دقیقه‌ای، تفاوتِ بینِ «فکر می‌کنم درست است» و «می‌دانم درست است» را می‌سازد.

جمع کردنِ دو تکه اینجا مجاز بود چون «شعر» و «رمان» هیچ هم‌پوشانی ندارند — یک کتاب نمی‌تواند هر دو باشد. اگر دو شرط هم‌پوشانی داشته باشند، جمعِ ساده دوباره‌شماری می‌کند؛ ترمِ ۳ ابزارِ درستش را می‌دهد.

✅ چک کن: عددِ 18 و 17 را با هم جمع کن. اگر ۳۵ نشد، یکی از سه پرسشِ بالا را دستکاری کرده‌ای. و اگر شد ولی tight عددِ دیگری داد، پرانتزت جای دیگری است.

۴. BETWEEN: کوتاه‌تر، و دو سرش داخل است#

BETWEEN a AND b همان >= a AND <= b است. هر دو سر داخل بازه‌اند و این تنها چیزی است که باید دربارهٔ BETWEEN حفظ کنی.

between = int(q("SELECT COUNT(*) FROM books WHERE pages BETWEEN ? AND ?",
                (200, 400)).iloc[0, 0])
manual = int(q("SELECT COUNT(*) FROM books WHERE pages >= ? AND pages <= ?",
               (200, 400)).iloc[0, 0])
open_ended = int(q("SELECT COUNT(*) FROM books WHERE pages > ? AND pages < ?",
                   (200, 400)).iloc[0, 0])

print("BETWEEN 200 AND 400 :", between)
print(">= 200 AND <= 400   :", manual)
print(">  200 AND <  400   :", open_ended)

BETWEEN 200 AND 400 : 274
>= 200 AND <= 400   : 274
>  200 AND <  400   : 269

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

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

۵. IN: زنجیرهٔ OR را کوتاه کن#

in_list = int(q("SELECT COUNT(*) FROM books WHERE category IN (?, ?, ?)",
                ("شعر", "رمان", "کودک")).iloc[0, 0])
or_chain = int(q("""
    SELECT COUNT(*) FROM books
    WHERE category = ? OR category = ? OR category = ?
""", ("شعر", "رمان", "کودک")).iloc[0, 0])
print("IN  :", in_list)
print("OR  :", or_chain)

IN  : 477
OR  : 477

عمداً هر دو را نوشتیم و عددشان را کنارِ هم گذاشتیم. ادعای «این دو یکی‌اند» را نباید باور کنی؛ باید ببینی.

IN علاوه بر کوتاهی یک مزیتِ واقعی دارد: جای پرانتزگذاری برای اشتباه نمی‌گذارد. زنجیرهٔ OR وقتی با یک AND ترکیب شود دقیقاً همان تلهٔ بخشِ ۲ را می‌سازد؛ IN خودش یک واحدِ بسته است و هر چیزی که داخلش باشد، با هم گروه می‌ماند.

و یک نکتهٔ عملی دربارهٔ فهرست‌های بلند: به‌ازای هر مقدار یک ? لازم داری، پس برای فهرستی که طولش را از قبل نمی‌دانی باید علامت‌ها را در پایتون بسازی — چیزی شبیهِ "(" + ",".join("?" * len(values)) + ")". دقت کن که آنچه ساخته می‌شود فقط علامت‌های ? است، نه خودِ مقدارها؛ مقدارها همچنان جدا می‌روند. مرزِ دقیقِ اینکه چه چیزی را می‌شود به یک پرسش چسباند و چه چیزی را نه، در فصلِ ۷ کامل می‌شود.

⚠️ مواظب باش: NOT IN تا وقتی هیچ مقدارِ خالی در کار نباشد رفتارِ قابلِ‌انتظاری دارد. اگر خالی وارد ماجرا شود، NOT IN کارِ عجیبی می‌کند که در فصلِ ۶ با عدد نشانش می‌دهیم. تا آن فصل، NOT IN را روی ستون‌های خالی‌دار به‌کار نبر.

۶. LIKE و %: جست‌وجو در متن#

% یعنی «هر تعداد کاراکتر، از جمله هیچ». پس '%باران%' یعنی «باران هرجای متن»، و 'روزهای%' یعنی «متن با روزهای شروع شود».

print("عنوان‌هایی که «باران» دارند :",
      int(q("SELECT COUNT(*) FROM books WHERE title LIKE ?", ("%باران%",)).iloc[0, 0]))
print("عنوان‌هایی که با «روزهای» شروع می‌شوند:",
      int(q("SELECT COUNT(*) FROM books WHERE title LIKE ?", ("روزهای%",)).iloc[0, 0]))
print(q("SELECT title FROM books WHERE title LIKE ? LIMIT 3", ("%باران%",)))

عنوان‌هایی که «باران» دارند : 257
عنوان‌هایی که با «روزهای» شروع می‌شوند: 210
             title
0      مه در باران
1  بازگشت به باران
2     رود در باران

قبل از هر تفسیری، عددِ ۲۱۰ را یک بار با ذهنت بسنج. عنوان‌های این جدول از شش الگوی ثابت ساخته شده‌اند و یکی از آن شش الگو با «روزهای» شروع می‌شود. یعنی انتظار داری حدودِ یک‌ششمِ ۱٬۲۰۰ سطر — نزدیکِ ۲۰۰ — با آن شروع شوند، و ۲۱۰ دقیقاً در همان محدوده است. این ارزان‌ترین شکلِ شمارشِ دوم است: قبل از دیدنِ عدد، حدس بزن چه بازه‌ای منتظرش هستی. اگر عدد بیرونِ آن بازه درآمد، یا حدست غلط بوده یا پرسشت — و هر دو حالت ارزشِ فهمیدن دارد.

دو هشدارِ صادقانه دربارهٔ LIKE در متنِ فارسی:

اول اینکه LIKE تطابقِ دقیقِ کاراکتری است. اگر همان کلمه جایی با نویسهٔ عربی نوشته شده باشد، پیدا نمی‌شود. این یک مسئلهٔ فرضی نیست — در همین جدول واقعاً وجود دارد و فصلِ ۴ عددش را درمی‌آورد.

دوم اینکه LIKE با % در ابتدای الگو گران است، چون پایگاه داده مجبور می‌شود تک‌تکِ سطرها را نگاه کند. روی ۱٬۲۰۰ سطر اصلاً حس نمی‌شود؛ ترمِ ۵ نشان می‌دهد کجا حس می‌شود و چقدر.

۷. NOT، و شرطی که جمعِ دو تکه‌اش کل نمی‌شود#

NOT شرط را وارونه می‌کند و مثلِ AND و OR تقدم دارد — محکم‌تر از هر دوی آن‌ها می‌چسبد. پس NOT a AND b یعنی (NOT a) AND b، نه NOT (a AND b). این هم یکی دیگر از جاهایی است که پرانتزِ اضافه رایگان است و پرانتزِ جاافتاده گران.

فایدهٔ عملیِ NOT فقط وارونه کردن نیست؛ یک آزمونِ کنترلی می‌سازد. انتظارِ طبیعی این است که «شرط درست است» و «شرط درست نیست» روی هم کلِ جدول شوند، و اگر نشوند، یعنی سطرهایی هستند که هیچ‌کدام از دو شرط برایشان درست نیست:

picked = int(q("SELECT COUNT(*) FROM books WHERE category = ? AND year >= ?",
               ("شعر", 2020)).iloc[0, 0])
rest = int(q("""
    SELECT COUNT(*) FROM books
    WHERE NOT (category = ? AND year >= ?)
""", ("شعر", 2020)).iloc[0, 0])
total = int(q("SELECT COUNT(*) FROM books").iloc[0, 0])

print("شرط درست است   :", picked)
print("شرط درست نیست  :", rest)
print("جمع دو تکه     :", picked + rest, "| کل جدول:", total)

شرط درست است   : 18
شرط درست نیست  : 1182
جمع دو تکه     : 1200 | کل جدول: 1200

اینجا جمع درآمد. این را به خاطر بسپار، چون در فصلِ ۶ همین آزمون را روی ستونِ rating تکرار می‌کنیم و جمع درنمی‌آید.

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

print("rating خالی است    :",
      int(q("SELECT COUNT(*) FROM books WHERE rating IS NULL").iloc[0, 0]))
print("rating خالی نیست   :",
      int(q("SELECT COUNT(*) FROM books WHERE rating IS NOT NULL").iloc[0, 0]))
print("rating = NULL      :",
      int(q("SELECT COUNT(*) FROM books WHERE rating = NULL").iloc[0, 0]))

rating خالی است    : 231
rating خالی نیست   : 969
rating = NULL      : 0

سه خط، و خطِ سوم عجیب است: rating = NULL صفر سطر می‌دهد، در حالی که ۲۳۱ سطر واقعاً NULL هستند. خطایی هم نگرفتیم.

فعلاً یک قاعدهٔ مکانیکی را بردار و برو: برای مقدارِ خالی همیشه IS NULL و IS NOT NULL بنویس، هرگز = NULL و <> NULL. فصلِ ۶ می‌گوید چرا، و آنجا معلوم می‌شود که این یک استثنای نحوی نیست بلکه از یک تصمیمِ عمیق‌تر می‌آید.

و آن ۹۶۹ آشناست: همان عددی است که در فصلِ ۲ فهمیدیم AVG(rating) بر آن تقسیم کرده.

📏 اندازه بگیر: یک سطر یعنی چه؟ نتیجهٔ هر پرسشِ این فصل که LIMIT دارد، یک کتاب است؛ نتیجهٔ هر پرسشی که COUNT(*) دارد، یک سطر است که یعنی «کلِ زیرمجموعهٔ فیلترشده». با کدام شمارشِ دوم سنجیدی؟ ۱۸۱ و ۳۵ را از راهِ جمعِ تکه‌های مستقل بازساختیم (۱۶۴+۱۷ و ۱۸+۱۷)، و IN را با زنجیرهٔ OR سنجیدیم (۴۷۷ و ۴۷۷). چه کسی از قلم افتاد؟ سه گروه: ۲۴۰ سطرِ arrivals.csv؛ ۲۳۱ سطری که rating ندارند و در هر شرطی روی rating بی‌صدا کنار می‌روند؛ و هر کتابی که نامِ نویسنده‌اش با نویسهٔ عربی وارد شده و LIKE پیدایش نمی‌کند.

🔧 اگر کار نکرد: غلطِ تایپی در WHERE همان خطای فصلِ ۲ را می‌دهد، ولی این بار جای دیگری از پرسش:

try:
    q("SELECT COUNT(*) FROM books WHERE categoy = ?", ("شعر",))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)

pandas.errors.DatabaseError
Execution failed on sql 'SELECT COUNT(*) FROM books WHERE categoy = ?': no such column: categoy

و یک خطای بی‌صدا که پیام ندارد: اگر به‌جای ("شعر",) بنویسی ("شعر")، پایتون آن را یک رشتهٔ ساده می‌بیند و sqlite3 می‌گوید تعدادِ مقدارها با تعدادِ ?ها نمی‌خواند. آن پیام را در فصلِ ۷ کامل می‌بینی.

🤖 از دستیارت بپرس: «چرا در SQL عملگرِ AND تقدمِ بالاتری از OR دارد؟» بعد این را بپرس: «اگر در یک شرط سه‌تا OR و یک AND داشته باشم و پرانتز نگذارم، دقیقاً چه چیزی با چه چیزی گروه می‌شود؟» — جواب را باور نکن؛ همان شرط را با هر دو پرانتزگذاری روی همین جدول بزن و دو عدد را مقایسه کن.

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

در فصل بعد#

حالا می‌توانی سطرها را غربال کنی، ولی هنوز نمی‌توانی بگویی «پرامانت‌ترین». فصلِ بعد ORDER BY و DISTINCT را می‌آورد و در همان راه یک عیبِ واقعیِ همین جدول را بیرون می‌کشد: فهرستِ نویسنده‌های یکتا ۱۲۳ نام دارد، ولی نویسنده‌های واقعی ۱۲۰ نفرند.