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


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.