MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
1PLs Company - #1Payday.Loans Agency - Loans online and near me $100-$2500 (Same Day)
Powered by MySQL
 
Home / SQL Statements / Question No: 225

What does NULL value mean in MySQL?

Answer No: 225

NULL is unusual because it doesn't represent a specific value the way that numeric, string, or temporal values do. Instead, NULL stands for the absence of a known value. The special nature of NULL means that it often is handled differently than other values. This section describes how MySQL processes NULL values in various contexts.

Syntactically, NULL values are written in SQL statements without quotes. Writing NULL is different from writing 'NULL' or "NULL". The latter two values are actually strings that contain the word "NULL". Also, because it is an SQL keyword, NULL is not case sensitive. NULL and null both mean "a NULL value," whereas the string values 'NULL' and 'null' may be different or the same depending on whether they are non-binary or binary strings.

Note that some database systems treat the empty string and NULL as the same value. In MySQL, the two values are different.

Use of NULL values in arithmetic or comparison operations normally produces NULL results:

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

Even comparing NULL to itself results in NULL, because you cannot tell whether one unknown value is the same as another:

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

LIKE evaluates to NULL if either operand is NULL:

mysql> SELECT NULL LIKE '%', 'abc' LIKE NULL;
+---------------+-----------------+
| NULL LIKE '%' | 'abc' LIKE NULL |
+---------------+-----------------+
|          NULL |            NULL |
+---------------+-----------------+

The proper way to determine whether a value is NULL is to use the IS NULL or IS NOT NULL operators, which produce a true (non-zero) or false (zero) result:

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

You can also use the MySQL-specific <=> operator, which is like = except that it works with NULL operands by treating them as any other value:

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

ORDER BY, GROUP BY, and DISTINCT all perform comparisons implicitly. For purposes of these operations, NULL values are considered identical. That is, NULL values sort together, group together, and are not distinct.

Expressions that cannot be evaluated (such as 1/0) produce NULL as a result. However, in the context of inserting data into tables, division by zero can be treated as an error to prevent invalid data from being entered. This behavior is controlled by setting the SQL mode to enable strict mode in conjunction with the ERROR_FOR_DIVISION_BY_ZERO mode.

Related MySQL FAQs to the Above FAQ

What-does-alias-means-in-MySQL What does alias means in MySQL?

What-does-SQL-Expressions-mean-in-MySQL What does SQL Expressions mean in MySQL?

How-to-use-numeric-expressions-in-MySQL How to use numeric expressions in MySQL?

How-to-use-string-expressions-in-MySQL How to use string expressions in MySQL?

How-to-manage-case-sensitivity-in-string-comparisons How to manage case sensitivity in string comparisons?

How-to-use-LIKE-pattern-matching-operator How to use LIKE pattern-matching operator?

How-to-use-date-or-temporal-expressions How to use date or temporal expressions?

How-to-use-comments-in-SQL-statements How to use comments in SQL statements?

How-to-validate-user-login-in-case-sensitive-manner How to validate user login in case sensitive manner?

What-is-difference-between-delete-and-truncate-statements-of-MySQL What is difference between delete and truncate statements of MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2024  www.mysqlfaqs.net
All rights reserved.