mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL; +----------+-----------+----------+----------+ | 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL | +----------+-----------+----------+----------+ | NULL | NULL | NULL | NULL | +----------+-----------+----------+----------+ 

Because the result of any arithmetic comparison with NULL  is also NULL , you cannot obtain any meaningful results from such comparisons. 

In MySQL, 0  or NULL  means false and anything else means true. The default truth value from a boolean operation is 1 . 

This special treatment of NULL  is why, in the previous section, it was necessary to determine which animals are no longer alive using death IS NOT NULL  instead of death <> NULL . 

Two NULL  values are regarded as equal in a GROUP BY . 

When doing an ORDER BY , NULL  values are presented first if you do ORDER BY ... ASC  and last if you do ORDER BY ... DESC . 

A common error when working with NULL  is to assume that it is not possible to insert a zero or an empty string into a column defined as NOT NULL , but this is not the case. These are in fact values, whereas NULL means “not having a value.”  You can test this easily enough by using IS [NOT] NULL  as shown: 

mysql> SELECT 0 IS NULL, 0 IS NOT NULL, ’’ IS NULL, ’’ IS NOT NULL; +-----------+---------------+------------+----------------+ | 0 IS NULL | 0 IS NOT NULL | ’’ IS NULL | ’’ IS NOT NULL | +-----------+---------------+------------+----------------+ | 0 | 1 | 0 | 1 | +-----------+---------------+------------+----------------+ 

Thus it is entirely possible to insert a zero or empty string into a NOT NULL  column, as these are in fact NOT NULL . See Section B.3.4.3, “Problems with NULL Values” . 

## 3.3.4.7 Pattern Matching 

MySQL provides standard SQL pattern matching as well as a form of pattern matching based on extended regular expressions similar to those used by Unix utilities such as vi , grep , and sed . 

SQL pattern matching enables you to use _  to match any single character and %  to match an arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case-insensitive by default. Some examples are shown here. Do not use =  or <>  when you use SQL patterns. Use the LIKE  or NOT LIKE  comparison operators instead. 

To find names beginning with b : 

mysql> SELECT * FROM pet WHERE name LIKE ’b%’; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ 

To find names ending with fy : 

mysql> SELECT * FROM pet WHERE name LIKE ’%fy’; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ 

