SELECT WHERE IN với NULL trong MySQL

Cú pháp kết hợp IN và IS NULL:

SELECT cot1, cot2, cot3 FROM tenBang
WHERE giaTri IN (cot1, cot2) OR cot1 IS NULL;

Tạo bảng demo60:

mysql> CREATE TABLE demo60 (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> first_name VARCHAR(20),
    -> last_name VARCHAR(20)
    -> );

Chèn dữ liệu (có giá trị NULL):

mysql> INSERT INTO demo60(first_name,last_name) VALUES('John','Smith');
mysql> INSERT INTO demo60(first_name,last_name) VALUES('John','Doe');
mysql> INSERT INTO demo60(first_name,last_name) VALUES(NULL,'Brown');
mysql> INSERT INTO demo60(first_name,last_name) VALUES('David','Miller');
mysql> INSERT INTO demo60(first_name,last_name) VALUES('David','Smith');
mysql> INSERT INTO demo60(first_name,last_name) VALUES('Chris','Brown');

Hiển thị dữ liệu:

mysql> SELECT * FROM demo60;

+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 1  | John       | Smith     |
| 2  | John       | Doe       |
| 3  | NULL       | Brown     |
| 4  | David      | Miller    |
| 5  | David      | Smith     |
| 6  | Chris      | Brown     |
+----+------------+-----------+

Truy vấn: lấy hàng có 'John' trong first_name hoặc last_name, HOẶC first_name IS NULL:

mysql> SELECT id, first_name, last_name
    -> FROM demo60
    -> WHERE 'John' IN (first_name, last_name) OR first_name IS NULL;

Kết quả:

+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 1  | John       | Smith     |
| 2  | John       | Doe       |
| 3  | NULL       | Brown     |
+----+------------+-----------+