Lọc dữ liệu: WHERE, LIMIT, IN, BETWEEN, LIKE, IS NULL

Lọc dữ liệu

← Truy vấn dữ liệu - SELECT, alias, ORDER BY, DISTINCT | → Kết hợp nhiều bảng - JOIN

Bạn sẽ học được

Chọn đúng những dòng cần thiết bằng WHERE và các toán tử AND, OR, IN, BETWEEN, LIKE, IS NULL; giới hạn kết quả bằng LIMIT/FETCH; và hiểu logic ba trạng thái của NULL — nguồn gốc của phần lớn lỗi truy vấn.

---

1. WHERE

WHERE lọc dòng trước khi nhóm và trước khi SELECT được tính.

SELECT danh_sach_cot
FROM ten_bang
WHERE dieu_kien;

dieu_kien là một biểu thức boolean — nó cho ra true, false, hoặc NULL. Chỉ những dòng cho true mới được giữ lại.

1.1. Toán tử so sánh

Toán tử	Ý nghĩa	Ví dụ
=	Bằng	rating = 'R'
<> hoặc !=	Khác	rating <> 'R'
< > <= >=	So sánh	length > 120
AND	Và	length > 120 AND rating = 'R'
OR	Hoặc	rating = 'R' OR rating = 'PG'
NOT	Phủ định	NOT (length > 120)
IN	Thuộc danh sách	rating IN ('R','PG')
BETWEEN	Trong khoảng	length BETWEEN 90 AND 120
LIKE / ILIKE	Khớp mẫu	title LIKE 'A%'
IS NULL	Là NULL	return_date IS NULL

1.2. Ví dụ cơ bản

SELECT first_name, last_name
FROM customer
WHERE first_name = 'Jamie';

 first_name | last_name
------------+-----------
 Jamie      | Rice
 Jamie      | Waugh
(2 rows)

1.3. Kết hợp AND / OR

SELECT title, length, rating
FROM film
WHERE length > 180
  AND rating = 'R'
ORDER BY length DESC, film_id
LIMIT 5;

AND có độ ưu tiên cao hơn OR — luôn dùng ngoặc

-- ⚠️ Không như bạn tưởng!
WHERE rating = 'R' OR rating = 'PG' AND length > 120
-- Postgres hiểu thành:
WHERE rating = 'R' OR (rating = 'PG' AND length > 120)
-- → trả về TẤT CẢ phim R, bất kể độ dài
 
-- ✅ Ý bạn muốn:
WHERE (rating = 'R' OR rating = 'PG') AND length > 120

Đây là lỗi logic phổ biến nhất trong SQL và nó không báo lỗi cú pháp — chỉ trả về kết quả sai lặng lẽ.

1.4. Bảng chân trị với NULL

PostgreSQL dùng logic ba trạng thái: TRUE, FALSE, NULL (unknown).

AND:

AND	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

OR:

OR	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

Điểm mấu chốt: FALSE AND NULL = FALSE (biết chắc sai), nhưng TRUE AND NULL = NULL (không biết).

---

2. LIMIT và OFFSET

SELECT ...
FROM ...
ORDER BY ...
LIMIT so_dong OFFSET so_dong_bo_qua;

SELECT film_id, title, length
FROM film
ORDER BY film_id
LIMIT 5;

 film_id |      title       | length
---------+------------------+--------
       1 | Academy Dinosaur |     86
       2 | Ace Goldfinger   |     48
       3 | Adaptation Holes |     50
       4 | Affair Prejudice |    117
       5 | African Egg      |    130
(5 rows)

Bỏ qua 3 dòng đầu rồi lấy 5 dòng tiếp:

SELECT film_id, title
FROM film
ORDER BY film_id
LIMIT 5 OFFSET 3;

LIMIT mà không có ORDER BY là vô nghĩa

Không có ORDER BY, PostgreSQL trả về dòng theo thứ tự vật lý — có thể đổi sau mỗi lần UPDATE hay VACUUM. LIMIT 10 sẽ cho 10 dòng khác nhau giữa các lần chạy.

2.1. OFFSET chậm dần theo trang

OFFSET 100000 buộc PostgreSQL đọc và bỏ đi 100.000 dòng trước. Phân trang sâu sẽ ngày càng chậm.

Giải pháp — keyset pagination (con trỏ):

-- Trang 1
SELECT film_id, title
FROM film
ORDER BY film_id
LIMIT 20;
-- giả sử film_id cuối cùng là 20
 
-- Trang 2: dùng giá trị cuối làm mốc, KHÔNG dùng OFFSET
SELECT film_id, title
FROM film
WHERE film_id > 20
ORDER BY film_id
LIMIT 20;

Cách này giữ tốc độ không đổi dù ở trang thứ mấy, vì index đưa thẳng tới vị trí cần.

2.2. FETCH — cú pháp chuẩn SQL

FETCH làm y hệt LIMIT nhưng theo chuẩn SQL:

OFFSET so_dong_bo_qua ROWS
FETCH FIRST so_dong ROWS ONLY;

SELECT film_id, title
FROM film
ORDER BY title
OFFSET 5 ROWS
FETCH FIRST 5 ROWS ONLY;

LIMIT ngắn hơn và phổ biến hơn trong hệ sinh thái PostgreSQL; FETCH có tính di động cao hơn giữa các hệ CSDL.

WITH TIES — chỉ FETCH mới có:

SELECT title, length
FROM film
ORDER BY length DESC
FETCH FIRST 3 ROWS WITH TIES;

Nếu có 5 phim cùng độ dài 185 phút, câu này trả về cả 5 thay vì cắt cụt ở 3. LIMIT không làm được điều này.

---

3. IN

IN kiểm tra giá trị có thuộc một danh sách hay không — là cách viết gọn của nhiều OR.

gia_tri IN (v1, v2, v3)
-- tương đương
gia_tri = v1 OR gia_tri = v2 OR gia_tri = v3

SELECT customer_id, rental_id, return_date
FROM rental
WHERE customer_id IN (1, 2)
ORDER BY return_date DESC
LIMIT 5;

3.1. IN với subquery

SELECT customer_id, first_name, last_name
FROM customer
WHERE customer_id IN (
   SELECT customer_id
   FROM rental
   WHERE CAST(return_date AS DATE) = '2005-05-27'
);

→ Truy vấn con - Subquery, ANY, ALL, EXISTS

3.2. Cạm bẫy chết người: NOT IN với NULL

-- Nếu subquery trả về BẤT KỲ giá trị NULL nào,
-- câu này trả về 0 dòng — luôn luôn!
SELECT * FROM film
WHERE film_id NOT IN (SELECT film_id FROM inventory);

Vì sao? x NOT IN (1, 2, NULL) được khai triển thành:

x <> 1 AND x <> 2 AND x <> NULL

Mà x <> NULL luôn cho NULL, nên toàn bộ AND không bao giờ thành true.

Cách sửa — dùng NOT EXISTS (an toàn với NULL):

SELECT f.film_id, f.title
FROM film f
WHERE NOT EXISTS (
   SELECT 1 FROM inventory i WHERE i.film_id = f.film_id
);

Hoặc lọc NULL ra khỏi subquery:

WHERE film_id NOT IN (
   SELECT film_id FROM inventory WHERE film_id IS NOT NULL
)

Quy tắc thực hành

Dùng IN thoải mái. Với NOT IN + subquery, luôn đổi sang NOT EXISTS. → Truy vấn con - Subquery, ANY, ALL, EXISTS

3.3. = ANY — tương đương của IN

WHERE customer_id = ANY (ARRAY[1, 2, 3])

Dạng này tiện khi truyền mảng tham số từ ứng dụng (thay vì nối chuỗi danh sách IN, vốn dễ bị SQL injection). → Truy vấn con - Subquery, ANY, ALL, EXISTS

---

4. BETWEEN

gia_tri BETWEEN thap AND cao
-- tương đương
gia_tri >= thap AND gia_tri <= cao

BETWEEN bao gồm cả hai đầu mút.

SELECT customer_id, amount, payment_date
FROM payment
WHERE amount BETWEEN 8 AND 9
ORDER BY payment_id
LIMIT 5;

Phủ định:

WHERE amount NOT BETWEEN 8 AND 9

4.1. BETWEEN với ngày — cạm bẫy nghiêm trọng

-- ⚠️ SAI nếu payment_date là TIMESTAMP
SELECT * FROM payment
WHERE payment_date BETWEEN '2007-02-01' AND '2007-02-28';

'2007-02-28' được hiểu là 2007-02-28 00:00:00. Mọi giao dịch trong ngày 28/02 sau nửa đêm — tức gần như cả ngày — đều bị bỏ sót.

Cách đúng — dùng nửa khoảng mở:

SELECT * FROM payment
WHERE payment_date >= '2007-02-01'
  AND payment_date <  '2007-03-01';

Mẫu >= đầu AND < đầu_kỳ_sau luôn đúng, không phụ thuộc độ chính xác của timestamp (giây, mili giây, micro giây) và không cần lo năm nhuận. → Kiểu dữ liệu chuyên sâu

BETWEEN chỉ an toàn với kiểu rời rạc

Với INTEGER hay DATE (không có phần giờ), BETWEEN hoàn toàn ổn. Chỉ với TIMESTAMP và số thực nó mới nguy hiểm.

---

5. LIKE — khớp mẫu

chuoi LIKE mau
chuoi NOT LIKE mau

Hai ký tự đại diện:

Ký tự	Ý nghĩa
%	Không hoặc nhiều ký tự bất kỳ
_	Đúng một ký tự bất kỳ

5.1. Ví dụ

SELECT first_name, last_name
FROM customer
WHERE first_name LIKE 'Jen%';

 first_name | last_name
------------+-----------
 Jennifer   | Davis
 Jennie     | Terry
 Jenny      | Castro
(3 rows)

Các mẫu thường dùng:

first_name LIKE 'Jen%'    -- bắt đầu bằng Jen
first_name LIKE '%er'     -- kết thúc bằng er
first_name LIKE '%en%'    -- chứa en ở bất kỳ đâu
first_name LIKE '_her%'   -- ký tự bất kỳ + "her" + phần còn lại  (Cheryl, Sherry, Theresa)
first_name LIKE 'A___'    -- đúng 4 ký tự, bắt đầu bằng A

5.2. ILIKE — không phân biệt hoa thường

SELECT first_name FROM customer
WHERE first_name ILIKE 'BAR%';
-- khớp Barbara, Barry, ...

ILIKE là mở rộng riêng của PostgreSQL, không có trong chuẩn SQL.

Các toán tử viết tắt:

Toán tử	Tương đương
~~	LIKE
~~*	ILIKE
!~~	NOT LIKE
!~~*	NOT ILIKE

5.3. Escape ký tự đặc biệt

Muốn tìm ký tự % hay _ theo nghĩa đen:

-- Tìm chuỗi chứa dấu gạch dưới thật sự
SELECT * FROM san_pham
WHERE ma LIKE '%\_%';
 
-- Hoặc chỉ định ký tự escape riêng
SELECT * FROM san_pham
WHERE ma LIKE '%!_%' ESCAPE '!';

5.4. Hiệu năng của LIKE

Mẫu	Dùng được index B-tree?
'abc%'	✅ Có (nếu index tạo với text_pattern_ops hoặc collation C)
'%abc'	❌ Không — buộc quét toàn bảng
'%abc%'	❌ Không
ILIKE bất kỳ	❌ Không với B-tree thường

Với tìm kiếm dạng '%abc%' trên bảng lớn, dùng index GIN + pg_trgm:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
 
CREATE INDEX idx_customer_name_trgm
ON customer USING GIN (first_name gin_trgm_ops);
 
-- Bây giờ câu này dùng được index
SELECT * FROM customer WHERE first_name ILIKE '%enn%';

5.5. Biểu thức chính quy

Khi LIKE không đủ mạnh:

SELECT first_name FROM customer
WHERE first_name ~ '^J.*r$';      -- regex, phân biệt hoa thường
 
SELECT first_name FROM customer
WHERE first_name ~* '^j';         -- regex, không phân biệt hoa thường

Toán tử	Ý nghĩa
~	Khớp regex, phân biệt hoa thường
~*	Khớp regex, không phân biệt hoa thường
!~	Không khớp regex
!~*	Không khớp regex, không phân biệt hoa thường

---

6. IS NULL

NULL nghĩa là “không biết”, không phải “rỗng” hay “bằng 0”.

6.1. Vì sao = NULL không hoạt động

SELECT NULL = NULL;      -- NULL  (không phải true!)
SELECT NULL <> NULL;     -- NULL
SELECT NULL = 1;         -- NULL
SELECT 1 + NULL;         -- NULL
SELECT 'abc' || NULL;    -- NULL

Vì NULL là “không biết”, so sánh hai thứ không biết cũng cho ra “không biết”. Và WHERE chỉ giữ dòng cho true — nên WHERE cot = NULL không bao giờ trả về dòng nào.

6.2. Cú pháp đúng

gia_tri IS NULL
gia_tri IS NOT NULL

-- Tìm các lượt thuê chưa trả đĩa
SELECT rental_id, customer_id, rental_date
FROM rental
WHERE return_date IS NULL
ORDER BY rental_date;

 rental_id | customer_id |     rental_date
-----------+-------------+---------------------
     11496 |         155 | 2006-02-14 15:16:03
     11541 |         335 | 2006-02-14 15:16:03
...
(183 rows)

6.3. Bảng đối chiếu

DROP TABLE IF EXISTS lien_he;
CREATE TABLE lien_he (
   id SERIAL PRIMARY KEY,
   ho_ten VARCHAR NOT NULL,
   email VARCHAR NOT NULL,
   dien_thoai VARCHAR
);
 
INSERT INTO lien_he (ho_ten, email, dien_thoai)
VALUES
   ('An',  'an@example.com',  '0901234567'),
   ('Bình','binh@example.com', NULL),
   ('Cường','cuong@example.com', ''),
   ('Dung','dung@example.com',  '0987654321');

Truy vấn	Kết quả	Giải thích
WHERE dien_thoai IS NULL	Bình	Đúng cách
WHERE dien_thoai = NULL	(không dòng nào)	Sai — luôn cho NULL
WHERE dien_thoai = ''	Cường	Chuỗi rỗng khác NULL
WHERE dien_thoai IS NOT NULL	An, Cường, Dung	Cường có chuỗi rỗng, vẫn tính là NOT NULL
WHERE dien_thoai <> '0901234567'	Cường, Dung	⚠️ Thiếu Bình!

Dòng cuối là bẫy: bạn hỏi “ai không có số 0901234567” nhưng Bình bị loại vì NULL <> '0901...' cho ra NULL.

Cách sửa:

WHERE dien_thoai IS DISTINCT FROM '0901234567';

6.4. IS DISTINCT FROM — so sánh an toàn với NULL

Biểu thức	Kết quả
NULL = NULL	NULL
NULL IS NOT DISTINCT FROM NULL	true ✅
1 <> NULL	NULL
1 IS DISTINCT FROM NULL	true ✅

IS DISTINCT FROM coi NULL như một giá trị bình thường. Cực kỳ hữu ích khi so sánh cột có thể NULL, ví dụ khi phát hiện thay đổi dữ liệu:

UPDATE muc_tieu t
SET gia_tri = s.gia_tri
FROM nguon s
WHERE t.id = s.id
  AND t.gia_tri IS DISTINCT FROM s.gia_tri;   -- chỉ update khi thật sự khác

6.5. Xử lý NULL trong kết quả

-- Thay NULL bằng giá trị mặc định
SELECT ho_ten, COALESCE(dien_thoai, 'Chưa có') AS sdt
FROM lien_he;
 
-- Coi chuỗi rỗng như NULL
SELECT ho_ten, COALESCE(NULLIF(dien_thoai, ''), 'Chưa có') AS sdt
FROM lien_he;

→ Biểu thức điều kiện - CASE, COALESCE, NULLIF, CAST

6.6. NULL trong hàm tổng hợp

Các hàm tổng hợp bỏ qua NULL:

SELECT
   count(*)            AS tong_dong,      -- 4
   count(dien_thoai)   AS co_sdt,         -- 3  (bỏ qua NULL của Bình)
   avg(length(dien_thoai)) AS do_dai_tb   -- tính trên 3 giá trị, không phải 4
FROM lien_he;

Đây là lý do count(*) và count(cot) cho kết quả khác nhau. → Nhóm dữ liệu - GROUP BY, HAVING, hàm tổng hợp

---

7. Bài tập

1. Tìm các phim có thời lượng từ 90 đến 100 phút và xếp hạng PG hoặc G.
2. Liệt kê khách hàng có first_name bắt đầu bằng K và last_name kết thúc bằng y.
3. Đếm số lượt thuê chưa trả đĩa (return_date là NULL).
4. Lấy các thanh toán trong toàn bộ tháng 3/2007, viết đúng cách với TIMESTAMP.
5. Lấy 10 phim dài nhất, nhưng nếu có phim bằng điểm ở vị trí thứ 10 thì lấy hết.
6. Vì sao câu sau trả về 0 dòng? Sửa lại:

   SELECT * FROM customer
   WHERE customer_id NOT IN (
      SELECT customer_id FROM payment WHERE amount > 100
   );

7. Viết truy vấn tìm các phim không có bản đĩa nào trong inventory.

Đáp án

1.

SELECT title, length, rating
FROM film
WHERE length BETWEEN 90 AND 100
  AND rating IN ('PG', 'G')
ORDER BY length, film_id;

Chú ý cặp ngoặc là không cần vì IN đã gom nhóm sẵn — nhưng nếu viết rating = 'PG' OR rating = 'G' thì bắt buộc phải có ngoặc.

2.

SELECT first_name, last_name
FROM customer
WHERE first_name LIKE 'K%'
  AND last_name LIKE '%y';

3.

SELECT count(*) FROM rental WHERE return_date IS NULL;
-- 183

4.

SELECT payment_id, amount, payment_date
FROM payment
WHERE payment_date >= '2007-03-01'
  AND payment_date <  '2007-04-01';

Không dùng BETWEEN '2007-03-01' AND '2007-03-31' — sẽ mất gần trọn ngày 31.

5.

SELECT title, length
FROM film
ORDER BY length DESC
FETCH FIRST 10 ROWS WITH TIES;

Trả về nhiều hơn 10 dòng vì có 5 phim cùng 185 phút.

6. Bảng payment không có customer_id nào là NULL, nên câu này thực ra chạy đúng. Nhưng nó mong manh: chỉ cần một NULL xuất hiện là kết quả về 0. Viết an toàn:

SELECT c.* FROM customer c
WHERE NOT EXISTS (
   SELECT 1 FROM payment p
   WHERE p.customer_id = c.customer_id AND p.amount > 100
);

7.

SELECT f.film_id, f.title
FROM film f
WHERE NOT EXISTS (
   SELECT 1 FROM inventory i WHERE i.film_id = f.film_id
)
ORDER BY f.title;

Có 42 phim không có bản đĩa nào. Cách khác dùng LEFT JOIN ... IS NULL → Kết hợp nhiều bảng - JOIN.

---

8. Tóm tắt

• WHERE lọc dòng, chạy trước SELECT → không dùng được bí danh cột.
• AND ưu tiên hơn OR — luôn đóng ngoặc khi trộn hai toán tử.
• LIMIT cần ORDER BY xác định; phân trang sâu nên dùng keyset thay OFFSET.
• FETCH ... WITH TIES lấy trọn nhóm bằng điểm — LIMIT không làm được.
• NOT IN + subquery có NULL → luôn 0 dòng. Dùng NOT EXISTS.
• BETWEEN nguy hiểm với TIMESTAMP — dùng >= đầu AND < đầu_kỳ_sau.
• LIKE 'abc%' dùng được index; '%abc%' cần GIN + pg_trgm.
• NULL không so sánh được bằng =; dùng IS NULL hoặc IS DISTINCT FROM.

---

← Truy vấn dữ liệu - SELECT, alias, ORDER BY, DISTINCT | → Kết hợp nhiều bảng - JOIN