Quick Contact
SQL & My SQL Tutorial
- INTRODUCATION
- What is SQL?
- SQL Microsoft
- SQL Management Tools
- SQL Developer
- Docker Commands
- Composite Key in SQL
- SQL Constraints
- Transactions in SQL Server
- SQL Server Data Types
- SQL Update Join
- SQL Operators
- SQL Clauses
- SQL Commands
- SQL Alter Command
- Distinct Keyword in SQL
- SQL Statements
- SQL Index
- SQL Injections
- Wildcards in SQL
- Alter and Truncate Command in SQL
- SQL Null Functions – ISNULL, IFNULL, Combine, & NULLIF
- SQL Sequence
- How to Find Duplicate Records in SQL
- Primary and Foreign Key in SQL with Examples
- COUNT Function
- SUM Function in SQL
- Dynamic SQL
- Database Tuning
- Pseudocolumn in Oracle SQL
- Triggers in SQL
- Embedded SQL in DBMS
SQL Null Functions – ISNULL, IFNULL, Combine, & NULLIF
What are NULL Values in SQL?
Null values are the placeholders in the database when we have the data missing, or the required data is not available.
A null value is not a part of any particular data type, it is a flexible data type and can be put in the column of any data type be it string, int, blob or CLOB datatype.
Null values come in handy while the cleaning of data during the exploratory analysis of data.
Null values help us in removing the ambiguity arising in data. Also, null values are beneficial to maintain uniform datatype across the column.
Example:
Imagine if the user by mistake enters the date of birth in the column of mobile number then ambiguity could arise when contact needs to be established.
To overcome this we run a check on data before insertion and update any data which is not of date datatype with the null value.
Why do we need NULL Functions?
Null functions are required to perform operations on the null values stored in our database. We can perform functions on NULL values, which explicitly recognize if a value is null or not.
Using this recognizing capacity, one can further perform operations on the null values like the aggregate functions in SQL. Some of the functions are as follows:
| Sr.No | Function | Description |
|---|---|---|
| 1 | ISNULL() | Helps us to replace NULL values with the desired value. |
| 2 | IFNULL() | Allows us to return the first value if the value is NULL, and otherwise returns the second value. |
| 3 | COALESCE() | Helps us to return the first non-null values in the arguments. |
| 4 | NVL() | Helps to replace the NULL value with the desired value given by the user. |
Demo Database
Let us now have a look at our demo database – ‘IBM’
Query:
SELECT * FROM IBM;
Examples
-
Let us see the use of the IFNULL() function.
Syntax:
SELECT col1,col2, IFNULL(col3, value_to_be_replaced) FROM tableName;
-
Let us see the use of ISNULL() function.
Syntax:
SELECT col1,col2, ISNULL(col3) FROM tableName;
-
Let us see the use of COALESCE() function.
Syntax:
SELECT col1,col2, COALESCE(col3, value_to_be_replaced) FROM tableName;
-
Let us see the use of NVL() function.
Syntax:
SELECT col1,col2, NVL(col3) FROM tableName;