Mệnh đề WHERE trong SQLite: lọc hàng với điều kiện, LIKE, IN

WHERE lọc từng hàng một

SELECT không có WHERE trả về mọi hàng trong bảng. Hiếm khi đó là điều bạn muốn. WHERE cho phép bạn chỉ giữ những hàng khớp với một điều kiện: SQLite duyệt qua bảng, tính điều kiện trên từng hàng và giữ những hàng có điều kiện đúng.

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;

Ba hàng được trả về: Neuromancer, Hyperion và The Martian. Điều kiện year > 1980 được kiểm tra trên từng hàng, và chỉ những hàng khớp còn lại.

Mô hình tư duy: WHERE là một bộ lọc nằm giữa FROM và các cột bạn chọn. Bất cứ hàng nào cho kết quả đúng đều đi qua.

Toán tử so sánh

Các toán tử cơ bản hoạt động đúng như bạn nghĩ:

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;

= để so sánh bằng, != hoặc <> cho "khác", và <, <=, >, >= để so sánh thứ tự. So sánh chuỗi dùng cùng các toán tử này: author = 'Asimov' khớp chính xác, từng ký tự một.

Một điều cần biết: SQL dùng dấu nháy đơn cho chuỗi. Dấu nháy kép dành cho định danh (tên cột hoặc tên bảng). WHERE author = "Asimov" có thể chạy trong SQLite vì lý do lịch sử, nhưng không dùng được ở nơi khác và có thể âm thầm sai khi "chuỗi" đó tình cờ trùng tên một cột. Hãy dùng dấu nháy đơn.

AND, OR và dấu ngoặc

Truy vấn thực tế thường kết hợp nhiều điều kiện. AND yêu cầu cả hai vế đều đúng; OR yêu cầu ít nhất một vế:

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';

Truy vấn thứ nhất lọc những cuốn sách mới và ngắn. Truy vấn thứ hai lấy sách của một trong hai tác giả.

Khi bạn trộn AND và OR, thứ tự ưu tiên hay gây rắc rối. AND được ưu tiên cao hơn OR, nên:

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;

được hiểu là Herbert OR (Gibson AND year > 1980): mọi cuốn của Herbert bất kể năm nào, cộng với sách của Gibson sau năm 1980. Có lẽ không phải ý bạn. Hãy bọc ý định của bạn trong ngoặc đơn:

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;

Khi còn phân vân, hãy thêm ngoặc. Trình tối ưu truy vấn không quan tâm, còn người đọc tiếp theo sẽ cảm ơn bạn.

NULL không hoạt động như một giá trị

Đây là cái bẫy của mệnh đề WHERE mà ai cũng mắc ít nhất một lần. NULL trong SQL nghĩa là "không xác định", và những thứ không xác định thì không so sánh được. column = NULL không phải là sai: nó là NULL, và WHERE coi đó là "bỏ qua hàng này".

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 và IS NOT NULL là hai toán tử duy nhất kiểm tra NULL trực tiếp. Hãy nhớ thật kỹ: mọi phép so sánh khác với NULL đều trả về NULL và âm thầm loại bỏ hàng.

Quy tắc này cũng áp dụng cho phép phủ định. WHERE author != 'Asimov' không trả về các hàng có author IS NULL, vì NULL != 'Asimov' cũng là NULL. Nếu muốn lấy cả NULL, hãy yêu cầu rõ ràng: WHERE author != 'Asimov' OR author IS NULL.

IN và BETWEEN: lối tắt bạn sẽ dùng hằng ngày

IN kiểm tra xem giá trị có nằm trong một danh sách không. Đây là cách viết gọn hơn cho một chuỗi 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 kiểm tra một khoảng, bao gồm cả hai đầu:

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 hoàn toàn giống year >= 1980 AND year <= 2000, chỉ ngắn hơn. Một điều cần nhớ: cả hai cận đều được tính. Nếu muốn loại trừ hai đầu, hãy viết các phép so sánh ra.

Lưu ý nhanh về IN và NULL: WHERE column NOT IN (1, 2, NULL) sẽ không bao giờ trả về hàng nào, vì so sánh bất cứ thứ gì với NULL đều cho ra NULL. Hãy loại NULL khỏi danh sách, hoặc xử lý chúng riêng bằng IS NULL.

LIKE để so khớp mẫu

LIKE so khớp chuỗi theo mẫu với hai ký tự đại diện:

• % khớp với dãy ký tự bất kỳ (kể cả rỗng).
• _ khớp với đúng một ký tự.

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 '____';

Mặc định, LIKE của SQLite không phân biệt chữ hoa chữ thường với chữ cái ASCII: 'Dune' LIKE 'dune' là đúng. Điều này gây bất ngờ nếu bạn quen Postgres, nơi LIKE phân biệt chữ hoa chữ thường và ILIKE là phiên bản không phân biệt. (SQLite không có ILIKE.)

Nếu bạn cần so khớp có phân biệt chữ hoa chữ thường, có hai cách. Bật pragma toàn cục:

PRAGMA case_sensitive_like = ON;

Hoặc dùng GLOB, toán tử luôn phân biệt chữ hoa chữ thường và dùng ký tự đại diện kiểu Unix (* cho dãy bất kỳ, ? cho một ký tự):

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*' sẽ không khớp gì ở đây, vì chữ hoa chữ thường có ý nghĩa.

Lọc theo ngày

SQLite lưu ngày dưới dạng văn bản (thường là YYYY-MM-DD hoặc ISO 8601 đầy đủ), nghĩa là phép so sánh chuỗi cũng tình cờ hoạt động như phép so sánh ngày, miễn là bạn giữ đúng định dạng 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';

Vì '2024-06-01' < '2024-11-08' đúng cả khi là chuỗi lẫn khi là ngày, các truy vấn này hoạt động như bạn mong đợi. Nếu bạn lưu ngày theo định dạng khác ('15/01/2024', 'Jan 15 2024'), phép so sánh sẽ âm thầm cho kết quả sai. Luôn dùng ISO 8601: bạn của tương lai sẽ cảm ơn bạn.

Với các phép tính ngày phức tạp hơn (lấy năm, so sánh với "hôm nay"), SQLite có các hàm date(), strftime() và julianday(). Ta sẽ nói về chúng trong chương ngày và giờ.

Kết hợp tất cả

Một truy vấn dùng nhiều thứ ở trên cùng lúc:

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%';

Đọc từng dòng: giữ các hàng có năm xác định, nằm trong khoảng, của một trong hai tác giả hoặc đủ dài, và không phải bản nháp. Đó là mệnh đề WHERE làm tốt nhất việc của nó: kết hợp các điều kiện nhỏ, dễ đọc thành những bộ lọc chính xác.

Hai thói quen nên giữ:

• Đặt mỗi điều kiện trên một dòng riêng có thụt lề. Một mệnh đề WHERE dài viết trên một dòng sẽ nhanh chóng trở nên khó đọc.
• Ghi chú thích cho ý định ở chỗ điều kiện không hiển nhiên. -- exclude drafts là một sự bảo đảm rẻ tiền.

Tiếp theo: toán tử và NULL chi tiết hơn

Mệnh đề WHERE phần lớn là các toán tử áp dụng lên cột, và NULL âm thầm thay đổi cách mọi toán tử hoạt động. Trang tiếp theo đi sâu hơn vào bộ toán tử của SQLite: số học, nối chuỗi bằng ||, họ toán tử IS, logic ba giá trị, để những điều bất ngờ không còn bất ngờ nữa.