Detailed explanation of the use of IF(), IFNULL(), NULLIF(), and ISNULL() functions in MySQL

In MySQL, you can use IF(), IFNULL(), NULLIF(), and ISNULL() functions to control the process.

1. Use of IF() function

IF(expr1, expr2, expr3) returns the value of expr2 if the value of expr1 is true, and returns the value of expr3 if the value of expr1 is false.

SELECT IF(TRUE,'A','B'); -- Output: A
SELECT IF(FALSE,'A','B'); -- Output: B

2. Use of IFNULL() function

IFNULL(expr1, expr2) returns the value of expr2 if the value of expr1 is null, and returns the value of expr1 if the value of expr1 is not null.

Example 1

      mysql> SELECT IFNULL(1,0);   
                            -> 1   
      mysql> SELECT IFNULL(NULL,10);   
                            -> 10   
      mysql> SELECT IFNULL(1/0,10);   
                            -> 10   
      mysql> SELECT IFNULL(1/0,'yes');   
                            -> 'yes'  

Example 2

SELECT IFNULL(NULL,'B'); -- Output: B
SELECT IFNULL('HELLO','B'); -- Output: HELLO

3. Use of NULLIF() function

NULLIF(expr1, expr2), if expr1=expr2, the return value is null, otherwise the return value is the value of expr1.

SELECT NULLIF('A','A'); -- Output: null
SELECT NULLIF('A','B'); -- Output: A

4. Use of ISNULL() function

ISNULL(expr) returns 1 if the value of expr is null, and returns 0 if the value of expr1 is not null.

SELECT ISNULL(NULL); -- Output: 1
SELECT ISNULL('HELLO'); -- Output: 0

This is the end of this article about the detailed usage of IF(), IFNULL(), NULLIF(), and ISNULL() functions in MySQL. For more information about IF(), IFNULL(), NULLIF(), and ISNULL() in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!