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
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Funtions and Operators / Question No: 236

What are NULL value related functions in MySQL?

Answer No: 236

Functions intended specifically for use with NULL values include ISNULL() and IFNULL().  ISNULL() is true if its argument is NULL and false otherwise:

mysql> SELECT ISNULL(NULL), ISNULL(0), ISNULL(1);
+--------------+-----------+-----------+
| ISNULL(NULL) | ISNULL(0) | ISNULL(1) |
+--------------+-----------+-----------+
|            1 |         0 |         0 |
+--------------+-----------+-----------+

IFNULL() takes two arguments. If the first argument is not NULL, that argument is returned; otherwise, the function returns its second argument:

mysql> SELECT IFNULL(NULL,'a'), IFNULL(0,'b');
+------------------+---------------+
| IFNULL(NULL,'a') | IFNULL(0,'b') |
+------------------+---------------+
| a                | 0             |
+------------------+---------------+

Other functions handle NULL values in various ways, so you have to know how a given function behaves. In many cases, passing a NULL value to a function results in a NULL return value. For example, any NULL argument passed to CONCAT() causes it to return NULL:

mysql> SELECT CONCAT('a','b'), CONCAT('a',NULL,'b');
+-----------------+----------------------+
| CONCAT('a','b') | CONCAT('a',NULL,'b') |
+-----------------+----------------------+
| ab              | NULL                 |
+-----------------+----------------------+

But not all functions behave that way. CONCAT_WS() (concatenate with separator) simply ignores NULL arguments entirely:

mysql> SELECT CONCAT_WS('/','a','b'), CONCAT_WS('/','a',NULL,'b');
+------------------------+-----------------------------+
| CONCAT_WS('/','a','b') | CONCAT_WS('/','a',NULL,'b') |
+------------------------+-----------------------------+
| a/b                    | a/b                         |
+------------------------+-----------------------------+

Related MySQL FAQs to the Above FAQ

What-is-MySQL-date-format-and-its-specifiers What is MySQL date format and its specifiers?

How-to-get-comma-separated-all-values-in-a-single-row-against-each-ID How to get comma separated all values in a single row against each ID?

How-to-concat-values-in-MySQL How to concat values in MySQL?

How-to-convert-time-zone-in-MySQL How to convert time zone in MySQL?

What-are-comparison-functions-in-MySQL What are comparison functions in MySQL

How-to-set-conditions-on-query-data-with-CASE-construct How to set conditions on query data with CASE construct?

How-to-set-control-conditions-on-query-data-with-IF-funtion How to set control conditions on query data with IF funtion?

What-are-mathematical-functions-in-MySQL What are mathematical functions in MySQL?

How-are-aggregate-functions-in-MySQL How are aggregate functions in MySQL?

What-are-string-functions-in-MySQL What are string functions in MySQL?

What-are-temporal-or-date-time-functions-in-MySQL What are temporal or date time functions in MySQL?

How-does-union-work-in-MySQL How does union work in 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

© 2021  www.mysqlfaqs.net
All rights reserved.