{"id":4939,"date":"2003-01-13T06:57:17","date_gmt":"2003-01-13T13:57:17","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=4939"},"modified":"2023-12-29T05:50:45","modified_gmt":"2023-12-29T12:50:45","slug":"mysql-null","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-null\/","title":{"rendered":"MySQL NULL: The Beginner’s Guide"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to work with MySQL NULL<\/code> values. In addition, you’ll learn some useful functions to deal with the NULL<\/code> values effectively.<\/p>\n\n\n\n

Introduction to MySQL NULL values<\/h2>\n\n\n\n

In MySQL, a NULL<\/code> value means unknown. A NULL<\/code> value is different from zero (0<\/code>) or an empty string ''<\/code>.<\/p>\n\n\n\n

A NULL<\/code> value is not equal to anything, even itself. If you compare a NULL<\/code> value with another NULL<\/code> value or any other value, the result is NULL<\/code> because the value of each NULL<\/code> value is unknown.<\/p>\n\n\n\n

Generally, you use the NULL<\/code> value to indicate that the data is missing, unknown, or not applicable. For example, the phone number of a potential customer may be NULL<\/code> and can be added later.<\/p>\n\n\n\n

When you create a table<\/a>, you can specify whether a column accepts NULL<\/code> values or not by using the NOT NULL<\/a><\/code> constraint.<\/p>\n\n\n\n

For example, the following statement creates the leads<\/code> table:<\/p>\n\n\n

DROP<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> leads;\n\nCREATE<\/span> TABLE<\/span> leads (\n    id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    first_name VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n    last_name VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n    source<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    email VARCHAR<\/span>(100<\/span>),\n    phone VARCHAR<\/span>(25<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The first_name<\/code>, last_name<\/code>, and source<\/code> columns use the NOT NULL<\/code> constraints, hence, you cannot insert<\/a> any NULL<\/code> values into these columns, whereas the email<\/code> and phone<\/code> columns accept NULL values.<\/p>\n\n\n\n
You can use a NULL<\/code> value in the INSERT<\/code><\/a> statement to specify that the data is missing. For example, the following statement inserts a row into the leads<\/code> table. Because the phone number is missing, so a NULL<\/code> value is used.<\/p>\n\n\n
INSERT<\/span> INTO<\/span> leads(first_name,last_name,source<\/span>,email,phone)\nVALUE<\/span>('John'<\/span>,'Doe'<\/span>,'Web Search'<\/span>,'john.doe@acme.com'<\/span>,NULL<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Because the default value of the email column is NULL<\/code>, you can omit the email in the INSERT<\/code> statement as follows:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> leads(first_name,last_name,source<\/span>,phone)\nVALUES<\/span>\n    ('Lily'<\/span>,'Bush'<\/span>,'Cold Calling'<\/span>,'(408)-555-1234'<\/span>),\n    ('David'<\/span>,'William'<\/span>,'Web Search'<\/span>,'(408)-888-6789'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
MySQL SET NULL in UPDATE statement<\/h2>\n\n\n\n
To set the value of a column to NULL<\/code>, you use the assignment operator ( =<\/code>). For example, to update the phone of David William<\/code> to NULL<\/code>, you use the following  UPDATE<\/code><\/a> statement:<\/p>\n\n\n
UPDATE<\/span> leads \nSET<\/span> \n    phone = NULL<\/span>\nWHERE<\/span>\n    id<\/span> = 3<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL ORDER BY with NULL<\/h2>\n\n\n\n
If you use the ORDER BY<\/code><\/a> clause to sort the result set in the ascending order, MySQL considers NULL<\/code> values are lower than other values, therefore, it presents the NULL<\/code> values first.<\/p>\n\n\n\n
The following statement sorts the leads by phone number in ascending order.<\/p>\n\n\n
SELECT<\/span> \n    *\nFROM<\/span>\n    leads\nORDER<\/span> BY<\/span> phone;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
In case you use the ORDER BY DESC<\/code>, the NULL<\/code> values appear at last of the result set. See the following example:<\/p>\n\n\n
SELECT<\/span> \n    *\nFROM<\/span>\n    leads\nORDER<\/span> BY<\/span> phone DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
To test for NULL<\/code> in a query, you use the IS NULL<\/code> or IS NOT NULL<\/code> operator in the WHERE<\/code><\/a> clause.<\/p>\n\n\n\n
For example, to get the leads who have not yet provided the phone number, you use the IS NULL<\/a><\/code> operator as follows:<\/p>\n\n\n
SELECT<\/span> \n    *\nFROM<\/span>\n    leads\nWHERE<\/span>\n    phone IS<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
You can use the IS NOT<\/code> operator to get all leads who provided the email addresses.<\/p>\n\n\n
SELECT<\/span> \n    *\nFROM<\/span>\n    leads\nWHERE<\/span>\n    email IS<\/span> NOT<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Even though the NULL<\/code> is not equal to NULL<\/code>, two NULL<\/code> values are equal in the GROUP BY<\/code><\/a> clause.<\/p>\n\n\n
SELECT<\/span> \n    id<\/span>, first_name, last_name, email, phone\nFROM<\/span>\n    leads\nGROUP<\/span> BY<\/span> email;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The query returns only two rows because the rows whose email column is NULL<\/code> are grouped into one.<\/p>\n\n\n\n
MySQL NULL and UNIQUE index<\/h2>\n\n\n\n
When you use a UNIQUE constraint<\/a> or UNIQUE index<\/a> on a column, you can insert<\/a> multiple NULL<\/code> values into that column. It is perfectly fine because in this case, MySQL considers NULL<\/code> values are distinct.<\/p>\n\n\n\n
Let’s verify this point by creating a UNIQUE<\/code> index for the phone<\/code> column.<\/p>\n\n\n
CREATE<\/span> UNIQUE<\/span> INDEX<\/span> idx_phone ON<\/span> leads(phone);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL NULL functions<\/h3>\n\n\n\n
MySQL provides several useful functions that handle NULL effectively: IFNULL<\/code><\/a>, COALESCE<\/code><\/a>, and NULLIF<\/code><\/a>.<\/p>\n\n\n\n
The IFNULL<\/code> function accepts two parameters. The IFNULL<\/code> function returns the first argument if it is not NULL<\/code>, otherwise, it returns the second argument.<\/p>\n\n\n\n
For example, the following statement returns the phone number if it is not NULL<\/code> otherwise, it returns N\/A<\/code> instead of NULL<\/code>.<\/p>\n\n\n
SELECT<\/span> \n    id<\/span>, \n    first_name, \n    last_name, \n    IFNULL<\/span>(phone, 'N\/A'<\/span>) phone\nFROM<\/span>\n    leads;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The COALESCE<\/code> function accepts a list of arguments and returns the first non-NULL argument. For example, you can use the COALESCE<\/code> function to display the contact information of a lead based on the priority of the information in the following order: phone, email, and N\/A.<\/p>\n\n\n
SELECT<\/span> \n    id<\/span>,\n    first_name,\n    last_name,\n    COALESCE<\/span>(phone, email, 'N\/A'<\/span>) contact\nFROM<\/span>\n    leads;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The NULLIF<\/code> function accepts two arguments. If the two arguments are equal, the NULLIF<\/code> function returns NULL<\/code>. Otherwise, it returns the first argument.<\/p>\n\n\n\n
The NULLIF<\/code> function is useful when you have both NULL<\/code> and empty string values in a column. For example, by mistake, you insert a following row into the leads<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> leads(first_name,last_name,source<\/span>,email,phone)\nVALUE<\/span>('Thierry'<\/span>,'Henry'<\/span>,'Web Search'<\/span>,'thierry.henry@example.com'<\/span>,''<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The phone is an empty string instead of NULL<\/code>.<\/p>\n\n\n\n
If you want to get the contact information of leads, you end up with an empty phone instead of the email as the following query:<\/p>\n\n\n
SELECT<\/span> \n    id<\/span>,\n    first_name,\n    last_name,\n    COALESCE<\/span>(phone, email, 'N\/A'<\/span>) contact\nFROM<\/span>\n    leads;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
To fix this, you use the NULLIF<\/code> function to compare the phone with the empty string, if they are equal, it returns NULL<\/code>, otherwise, it returns the phone number.<\/p>\n\n\n
SELECT<\/span> \n    id<\/span>,\n    first_name,\n    last_name,\n    COALESCE<\/span>(NULLIF<\/span>(phone, ''<\/span>), email, 'N\/A'<\/span>) contact\nFROM<\/span>\n    leads;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this tutorial, you have learned how to work with MySQL NULL<\/code> and how to use some handy functions to handle NULL<\/code> in queries.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Notice that if you use the BDB storage engine<\/a>, MySQL considers the NULL<\/code> values are equal therefore you cannot insert multiple NULL<\/code> values into a column that has a unique constraint.<\/p>\n\n\n\n
In this leads<\/code> table, the column id<\/code> is the primary key<\/a> column, therefore, it does not accept any NULL<\/code> value.<\/p>\n\n\n\n