Tutorialspoint.dev

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:

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



This article is attributed to GeeksforGeeks.org

leave a comment

code

0 Comments

load comments

Subscribe to Our Newsletter