Movatterモバイル変換

MySQL can perform the same optimization oncol_nameIS NULL that it can use forcol_name=constant_value. For example, MySQL can use indexes and ranges to search forNULL withIS NULL.

Examples:

SELECT * FROMtbl_name WHEREkey_col IS NULL;SELECT * FROMtbl_name WHEREkey_col <=> NULL;SELECT * FROMtbl_name  WHEREkey_col=const1 ORkey_col=const2 ORkey_col IS NULL;

If aWHERE clause includes acol_nameIS NULL condition for a column that is declared asNOT NULL, that expression is optimized away. This optimization does not occur in cases when the column might produceNULL anyway (for example, if it comes from a table on the right side of aLEFT JOIN).

MySQL can also optimize the combinationcol_name =expr ORcol_name IS NULL, a form that is common in resolved subqueries.EXPLAIN showsref_or_null when this optimization is used.

This optimization can handle oneIS NULL for any key part.

Some examples of queries that are optimized, assuming that there is an index on columnsa andb of tablet2:

SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;SELECT * FROM t1, t2  WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;SELECT * FROM t1, t2  WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);SELECT * FROM t1, t2  WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)  OR (t1.a=t2.a AND t2.a IS NULL AND ...);

ref_or_null works by first doing a read on the reference key, and then a separate search for rows with aNULL key value.

The optimization can handle only oneIS NULL level. In the following query, MySQL uses key lookups only on the expression(t1.a=t2.a AND t2.a IS NULL) and is not able to use the key part onb:

SELECT * FROM t1, t2  WHERE (t1.a=t2.a AND t2.a IS NULL)  OR (t1.b=t2.b AND t2.b IS NULL);