Reading Rows with SELECT

SELECT lists columns or expressions, FROM names the table, and WHERE keeps rows whose condition is true. AS names a computed column.

A computed column, a case-insensitive match, and NULLSQL

SELECT sku, price * stock AS stock_value FROM products WHERE title = 'indexing DEEP dive';
SELECT id, body = NULL AS eq_null, body IS NULL AS is_null FROM reviews WHERE id IN (4, 5);

Output

+-----------+-------------+
| sku       | stock_value |
+-----------+-------------+
| BK-SQL-02 |        0.00 |
+-----------+-------------+
1 row in set (0.000 sec)
+----+---------+---------+
| id | eq_null | is_null |
+----+---------+---------+
|  4 |    NULL |       0 |
|  5 |    NULL |       1 |
+----+---------+---------+
2 rows in set (0.000 sec)

The title matched despite the scrambled case: the default collation utf8mb4_0900_ai_ci ignores accents and case. That suits titles and emails; give tokens and codes a _bin collation (Data Types). The second query is the trap everyone meets. NULL means unknown, so body = NULL is unknown even for review 5, which has no body, and WHERE body = NULL returns an empty set. Use IS NULL and IS NOT NULL. In application code, name columns instead of SELECT *, so a new column cannot break your PHP.

MENU