MySQL SELECT column FROM table WHERE column IS NULL

Question

This is not working for me, using Toad for MySQL. I'm using MySQL 5.5 from XAMPP 1.83 on Windows.

I have a table with column InstitutionState defined as VARCHAR(20). Some rows appear to have this column "empty", meaning LENGTH(InstitutionState) = 0.

If I SELECT ... WHERE InstitutionState IS NULL, I get no rows.

If I SELECT ... WHERE InstitutionState = '', It works. Why is this?

Here's sample data.

mysql> select InstitutionState, ISNULL(InstitutionState), length(InstitutionState)
    ->   from institution;
+----------------------+--------------------------+--------------------------+
| InstitutionState     | ISNULL(InstitutionState) | length(InstitutionState) |
+----------------------+--------------------------+--------------------------+
| NY                   |                        0 |                        2 |
| NY                   |                        0 |                        2 |
| NY                   |                        0 |                        2 |
| IL                   |                        0 |                        2 |
| NC                   |                        0 |                        2 |
| TX                   |                        0 |                        2 |
| DC                   |                        0 |                        2 |
| NY                   |                        0 |                        2 |
| CA                   |                        0 |                        2 |
|                      |                        0 |                        0 |
| KS                   |                        0 |                        2 |
|                      |                        0 |                        0 |
| NY                   |                        0 |                        2 |
| ND                   |                        0 |                        2 |
| PA                   |                        0 |                        2 |
| WI                   |                        0 |                        2 |
| PA                   |                        0 |                        2 |
| MD                   |                        0 |                        2 |
| IN                   |                        0 |                        2 |
| PA                   |                        0 |                        2 |
| NE                   |                        0 |                        2 |
| ID                   |                        0 |                        2 |
| CA                   |                        0 |                        2 |
|                      |                        0 |                        0 |
| FL                   |                        0 |                        2 |
| MO                   |                        0 |                        2 |
|                      |                        0 |                        0 |
| OH                   |                        0 |                        2 |
| IL                   |                        0 |                        2 |
| OH                   |                        0 |                        2 |

cause you have empty string("") as column values not NULL, NULL means no values all at(value not defined) — avisheks
– avisheks, Commented Feb 27, 2014 at 16:44
And it's only in Oracle for legacy reasons, since they've had that behavior since before there was a SQL standard. — Mike Christensen
– Mike Christensen, Commented Feb 27, 2014 at 16:46

analyticalpicasso · Accepted Answer · 2014-02-27 18:23:35Z

Conceptually, NULL means “a missing unknown value”

OR

NULL means no data, emptiness, nothing, unknown, missing value, etc. The value empty string means an empty string.

Confusing the NULL value and the empty string may cause data integrity problem.

What NULL means in the context of a relational database is that the pointer to the character field is set to 0x00 in the row's header, therefore no data to access.

NULL and '' take up the exact same number of bytes on the disk.

Hence, there is no space savings.

You can add an index on a column that can have NULL values. Otherwise, you must declare an indexed column NOT NULL, and you cannot insert NULL into the column.

Furthermore, allowing NULL is a less restrictive configuration than disallowing NULL. It only follows that if any entity integrity issues are to arise, it would be from FEWER checks that the data are sound. Therefore, logically, allowing NULL should always have a good, solid reason, and disallowing NULL is a good practice.

mysql> INSERT INTO ... (InstitutionState) VALUES (NULL);

mysql> INSERT INTO ... (InstitutionState) VALUES ('');

Both statements will insert a value into the InstitutionState column, but the first inserts a NULL value and the second inserts an empty string. The meaning of the first can be regarded as “InstitutionState is not known” and the meaning of the second can be regarded as “the Institution is known to have no state, and thus no InstitutionState.”

To search for column values that are NULL, you cannot use an expr = NULL test. The following statement returns no rows, because expr = NULL is never true for any expression:

mysql> SELECT ... WHERE InstitutionState = NULL;

To look for NULL values, you must use the IS NULL test. The following statements show how to find the NULL InstitutionState and the empty InstitutionState:

mysql> SELECT ... WHERE InstitutionState IS NULL;

mysql> SELECT ... WHERE InstitutionState = '';

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;

+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL. To demonstrate this for yourself, try the following query:

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;

+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

In addition,

In MyISAM MYSQL you save one bit per row not using NULL.
While a NULL itself does not require any storage space, NDBCLUSTER reserves 4 bytes per row if the table definition contains any columns defined as NULL, up to 32 NULL columns. (If a MySQL Cluster table is defined with more than 32 NULL columns up to 64 NULL columns, then 8 bytes per row is reserved.)
It also makes the database work faster.

To get '' AND NULLs,

We would use:

 SELECT ... WHERE IFNULL(InstitutionState , '') = '';

Which says if the field is NULL pretend that it is an empty string i.e. ''.

>IFNULL(InstitutionState , '') = '' That is what I was looking for. Thanks.

Mike Christensen · Accepted Answer · 2014-02-27 16:51:35Z

0

The NULL value isn't an actual value in SQL, but the lack of a value. One can think of it as unknown. For that reason, not even NULL is equal to another null.

Null values are actually implemented as a bitmask on the row, which indicate which columns have null values. So, these values aren't even stored on the heap table in the same way as other values, which is one of the reasons why you have to explicitly declare a column as nullable.

The string '' is actually known. It's known to be ''. This isn't null, nor is that null bit set on the tuple.

For this reason, querying for rows where a column IS NULL will not return rows with a value of '' nor will querying for rows where a column is '' return null values. They are two completely different things.

There are actually a few exceptions. For example, in Oracle, any reference to '' will be implicitly cast to NULL. This behavior was implemented back in the 80s before a real SQL standard, so Oracle has had to maintain it for backwards compatibility reasons.

answered Feb 27, 2014 at 16:51

Mike Christensen

91.8k52 gold badges223 silver badges349 bronze badges

2 Comments

Mike Christensen Over a year ago

@Strawberry - I think NULL == NULL resolves to NULL, which makes sense. "Is this unknown equal to that unknown?" - "I don't know!"

Peter Nosko Over a year ago

Mike, I'm having to reconsider my Oracle-centric position that null and an empty string should mean the same thing. If you have a numeric column that holds a quantity, then NULL should mean unknown while 0 means zero. I think an empty string is the character-equivalent of a numerical zero. For example, a middle name field (I have no middle name) if NULL means the middle name is unknown, while set to an empty string, means there is none (hence, numerical 0). BUT, I don't know how you could distinguish NULL from empty when the data is coming from a field on a webpage.

Collectives™ on Stack Overflow

MySQL SELECT column FROM table WHERE column IS NULL

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related