SQL | NULL functions

Following are the NULL functions defined in SQL:

1. ISNULL(): The ISNULL function have different uses in SQL Server and MySQL. In SQL Server, ISNULL() function is used to replace NULL values.
   Syntax:

   SELECT column(s), ISNULL(column_name, value_to_replace)
   FROM table_name;

   Example:
   Consider the following Employee table,

   

   Query: Find the sum of salary of all Employee, if Salary of any employee is not available (or NULL value), use salary as 10000.

   SELECT SUM(ISNULL(Salary, 10000) AS Salary
   FROM Employee;

   Output:

   

   In MySQL, ISNULL() function is used to test whether an expression is NULL or not. If the expression is NULL it returns TRUE, else FALSE.
   Syntax:

   SELECT column(s)
   FROM table_name
   WHERE ISNULL(column_name);

   Example:
   Consider the following Employee table,

   

   Query: Fetch the name of all employee whose salary is available in the table (not NULL).

   SELECT Name
   FROM Employee
   WHERE ISNULL(Salary);

   Output:

   
2. IFNULL(): This function is available in MySQL, and not in SQL Server or Oracle. This function take two arguments. If the first argument is not NULL, the function returns the first argument. Otherwise, the second argument is returned. This function is commonly used to replace NULL value with another value.
   Syntax:

   SELECT column(s), IFNULL(column_name, value_to_replace)
   FROM table_name;

   Example:
   Consider the following Employee table,

   

   Query: Find the sum of salary of all Employee, if Salary of any employee is not available (or NULL value), use salary as 10000.

   SELECT SUM(IFNULL(Salary, 10000) AS Salary
   FROM Employee;

   Output:

   

3. COALESCE(): COALESCE function in SQL returns the first non-NULL expression among its arguments. If all the expressions evaluate to null, then the COALESCE function will return null.
   Syntax:

   SELECT column(s), CAOLESCE(expression_1,....,expression_n)
   FROM table_name;

   Example:
   Consider the following Contact_info table,

   

   Query: Fetch the name, contact number of each employee.

   SELECT Name, COALESCE(Phone1, Phone2) AS Contact
   FROM Contact_info;

   Output:

   

4. NULLIF(): The NULLIF function takes two argument. If the two arguments are equal, then NULL is returned. Otherwise the first argument is returned.
   Syntax:

   SELECT column(s), NULLIF(expression1, expression2)
   FROM table_name;

   Example:
   Consider the following Sales table,

   

   SELECT Store, NULLIF(Actual, Goal)
   FROM Sales;

   Output:

   

This article is contributed by Anuj Chauhan. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

My Personal Notes arrow_drop_up

Save