WHERE ב-SQLite: סינון שורות עם תנאים, LIKE, IN ו-BETWEEN

WHERE מסננת שורות אחת אחת

SELECT בלי WHERE מחזיר כל שורה בטבלה. זה רק לעיתים רחוקות מה שרוצים. WHERE מאפשרת לשמור רק את השורות שמתאימות לתנאי: SQLite עובר על הטבלה, בודק את התנאי מול כל שורה, ושומר את אלה שבהן הוא אמת.

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

SELECT title, year FROM books WHERE year > 1980;

שלוש שורות חוזרות: Neuromancer, Hyperion ו-The Martian. התנאי year > 1980 נבדק מול כל שורה, ורק ההתאמות שרדו.

המודל המנטלי: WHERE היא מסנן שיושב בין FROM לבין העמודות שבוחרים. כל מה שמתקבל כאמת עובר.

אופרטורי השוואה

הבסיס עובד כמו שהייתם מצפים:

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

SELECT title, pages FROM books WHERE pages >= 400;
SELECT title FROM books WHERE author = 'Asimov';
SELECT title, year FROM books WHERE year != 1965;

= לשוויון, != או <> ל"לא שווה", ו-<, <=, >, >= לסדר. השוואות מחרוזות משתמשות באותם אופרטורים: author = 'Asimov' מתאים בדיוק, תו אחר תו.

דבר אחד שכדאי לדעת: SQL משתמש בגרשיים בודדים לליטרלים של מחרוזות. מירכאות כפולות הן למזהים (שמות של עמודות או טבלאות). WHERE author = "Asimov" עשוי לעבוד ב-SQLite מסיבות היסטוריות, אבל זה לא נייד ויכול להתנהג לא נכון בשקט כשה"מחרוזת" במקרה זהה לשם של עמודה. היצמדו לגרשיים בודדים.

AND, OR וסוגריים

שאילתות אמיתיות בדרך כלל משלבות תנאים. AND דורש ששני הצדדים יהיו אמת. OR דורש לפחות אחד:

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

SELECT title FROM books
WHERE year > 1980 AND pages < 400;

SELECT title FROM books
WHERE author = 'Herbert' OR author = 'Asimov';

השאילתה הראשונה מסננת ספרים חדשים וגם קצרים. השנייה שולפת ספרים של אחד משני הסופרים.

כשמערבבים AND ו-OR, סדר הקדימויות נושך אנשים. AND קושר חזק יותר מ-OR, כך ש:

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

SELECT title FROM books
WHERE author = 'Herbert' OR author = 'Gibson' AND year > 1980;

נקרא כ-Herbert OR (Gibson AND year > 1980): כל ספר של Herbert בלי קשר לשנה, ועוד ספרים של Gibson אחרי 1980. כנראה לא מה שהתכוונתם. עטפו את הכוונה שלכם בסוגריים:

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

SELECT title FROM books
WHERE (author = 'Herbert' OR author = 'Gibson') AND year > 1980;

כשיש ספק, הוסיפו סוגריים. לאופטימייזר של השאילתות זה לא משנה, והאדם הבא שיקרא את זה יודה לכם.

NULL לא מתנהג כמו ערך

זו המלכודת של פסוקית WHERE שתופסת כל אחד פעם אחת. NULL ב-SQL פירושו "לא ידוע", ואי אפשר להשוות ערכים לא ידועים. column = NULL הוא לא שקר: הוא NULL, ו-WHERE מתייחסת לזה כ"דלג על השורה הזו".

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Untitled Draft', 'Anonymous', NULL, 120);

-- Returns nothing, even though we have a NULL year:
SELECT title FROM books WHERE year = NULL;

-- This is the right way:
SELECT title FROM books WHERE year IS NULL;
SELECT title FROM books WHERE year IS NOT NULL;

IS NULL ו-IS NOT NULL הם האופרטורים היחידים שבודקים NULL ישירות. חרטו אותם באצבעות: כל השוואה אחרת עם NULL מחזירה NULL וזורקת שורות בשקט.

אותו כלל חל על שלילה. WHERE author != 'Asimov' לא מחזיר שורות שבהן author IS NULL, כי גם NULL != 'Asimov' הוא NULL. אם אתם רוצים לכלול ערכי NULL, בקשו אותם במפורש: WHERE author != 'Asimov' OR author IS NULL.

IN ו-BETWEEN: קיצורי דרך שתשתמשו בהם כל יום

IN בודק שייכות לרשימה. זו דרך נקייה יותר לכתוב שרשרת של OR:

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

SELECT title, author FROM books
WHERE author IN ('Herbert', 'Asimov', 'Weir');

SELECT title FROM books
WHERE year NOT IN (1965, 1984);

BETWEEN בודק טווח, כולל בשני הקצוות:

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

SELECT title, year FROM books
WHERE year BETWEEN 1980 AND 2000;

SELECT title, pages FROM books
WHERE pages NOT BETWEEN 300 AND 500;

year BETWEEN 1980 AND 2000 זהה ל-year >= 1980 AND year <= 2000, רק קצר יותר. דבר אחד לזכור: שני הגבולות כלולים. אם רוצים גבולות לא כוללים, כתבו את ההשוואות במלואן.

הערה קצרה על IN ו-NULL: WHERE column NOT IN (1, 2, NULL) לעולם לא יחזיר שורות, כי השוואה של כל דבר ל-NULL מניבה NULL. סננו ערכי NULL מהרשימה שלכם, או טפלו בהם בנפרד עם IS NULL.

LIKE להתאמת תבניות

LIKE מבצע התאמת תבניות של מחרוזות עם שני תווים כלליים:

• % מתאים לכל רצף של תווים (כולל רצף ריק).
• _ מתאים לתו אחד בדיוק.

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

-- Titles starting with "The":
SELECT title FROM books WHERE title LIKE 'The %';

-- Titles containing "ion" anywhere:
SELECT title FROM books WHERE title LIKE '%ion%';

-- Four-letter author names:
SELECT author FROM books WHERE author LIKE '____';

כברירת מחדל, LIKE של SQLite לא רגיש לאותיות גדולות וקטנות באותיות ASCII: 'Dune' LIKE 'dune' הוא אמת. זה מפתיע אם אתם מגיעים מ-Postgres, שם LIKE רגיש לאותיות ו-ILIKE היא הגרסה שלא רגישה. (ל-SQLite אין ILIKE.)

אם אתם צריכים התאמה רגישה לאותיות, יש שתי אפשרויות. להחליף את ה-pragma הגלובלי:

PRAGMA case_sensitive_like = ON;

או להשתמש ב-GLOB, שתמיד רגיש לאותיות ומשתמש בתווים כלליים בסגנון Unix (* לכל רצף, ? לתו אחד):

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369);

SELECT title FROM books WHERE title GLOB 'The *';
SELECT title FROM books WHERE title GLOB 'D*';

GLOB 'd*' לא היה מתאים כאן לכלום: אותיות גדולות וקטנות משנות.

סינון תאריכים

SQLite שומר תאריכים כטקסט (בדרך כלל YYYY-MM-DD או ISO 8601 מלא), כלומר השוואות מחרוזות עובדות במקרה גם כהשוואות תאריכים, כל עוד נצמדים לפורמט ISO:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    name TEXT,
    occurred_on TEXT
);

INSERT INTO events (name, occurred_on) VALUES
    ('Launch', '2024-01-15'),
    ('Patch', '2024-03-22'),
    ('Release', '2024-11-08'),
    ('Hotfix', '2025-02-01');

SELECT name FROM events
WHERE occurred_on >= '2024-06-01';

SELECT name FROM events
WHERE occurred_on BETWEEN '2024-01-01' AND '2024-12-31';

מכיוון ש-'2024-06-01' < '2024-11-08' הוא אמת גם כמחרוזות וגם כתאריכים, השאילתות האלה עושות את מה שהייתם מצפים. אם שומרים תאריכים בכל פורמט אחר ('15/01/2024', 'Jan 15 2024'), ההשוואות יפיקו בשקט תוצאות שגויות. תמיד השתמשו ב-ISO 8601: העצמי העתידי שלכם יהיה מרוצה.

לחישובי תאריכים מסובכים יותר (חילוץ השנה, השוואה ל"היום"), ל-SQLite יש את הפונקציות date(), strftime() ו-julianday(). נכסה אותן בפרק על תאריכים ושעות.

מחברים הכול יחד

שאילתה שמשתמשת בכמה מאלה בבת אחת:

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER,
    pages INTEGER
);

INSERT INTO books (title, author, year, pages) VALUES
    ('Dune', 'Herbert', 1965, 688),
    ('Neuromancer', 'Gibson', 1984, 271),
    ('Foundation', 'Asimov', 1951, 244),
    ('Hyperion', 'Simmons', 1989, 482),
    ('The Martian', 'Weir', 2011, 369),
    ('Untitled Draft', 'Anonymous', NULL, 120);

SELECT title, author, year, pages
FROM books
WHERE year IS NOT NULL
  AND year BETWEEN 1950 AND 2000
  AND (author IN ('Herbert', 'Gibson') OR pages > 400)
  AND title NOT LIKE '%Draft%';

קראו אותה שורה אחר שורה: שומרים שורות עם שנה ידועה, בטווח, של אחד משני סופרים או ארוכות מספיק, ושאינן טיוטה. זו פסוקית WHERE שעושה את מה שהיא עושה הכי טוב: משלבת תנאים קטנים וקריאים לסינונים מדויקים.

שני הרגלים ששווה לשמור:

• הזיחו כל תנאי בשורה משלו. פסוקיות WHERE ארוכות הופכות לבלתי קריאות מהר כשורה ענקית אחת.
• כתבו הערה על הכוונה כשהתנאי לא ברור מאליו. -- exclude drafts הוא ביטוח זול.

הבא בתור: אופרטורים ו-NULL לעומק

פסוקית WHERE היא בעיקר אופרטורים שמופעלים על עמודות, ו-NULL משנה בשקט את ההתנהגות של כל אופרטור. הדף הבא נכנס לעומק לסט האופרטורים של SQLite: חשבון, שרשור מחרוזות עם ||, משפחת ה-IS, לוגיקה תלת ערכית, כדי שההפתעות יפסיקו להיות הפתעות.