In the database, null values serve as placeholders for data that is either missing or not available. a null value is a flexible data type that can be placed in the column of any data type, including string, int, blob, and CLOB datatypes. It is not a component of any specific data type. Null values are helpful when cleaning the data prior to exploratory analysis.
Null values assist us in eradicating data ambiguity. Null values are also useful for maintaining a consistent datatype across the column. We will learn about the necessity and guidelines for using Null values in this article. Now let’s use examples to try to better understand null values and null functions in SQL.
Some key points regarding the use of NULL in SQL:
- Comparison with NULL: Comparisons with NULL using regular comparison operators like “=”, “<>”, “<“, “>” do not yield true or false but rather produce a result of unknown or NULL. Instead, you need to use the IS NULL or IS NOT NULL operators to check for NULL values.
- Handling NULL in expressions: When performing arithmetic or other operations involving NULL values, the result typically becomes NULL. For example, any arithmetic operation that involves a NULL operand will result in a NULL result.
- Aggregating NULL values: Most aggregate functions in SQL, such as SUM, AVG, COUNT, etc., ignore NULL values when calculating results. However, there are some aggregate functions like COUNT(*) that consider NULL values.
- Indexing and NULL: Some database systems handle NULL values differently when it comes to indexing.
- It’s important to consult the specific documentation of your database management system to understand how NULL values are treated in index structures. Potential pitfalls: The presence of NULL values can introduce complexity and potential pitfalls when querying or manipulating data. It requires careful consideration to handle NULL values appropriately in SQL queries to avoid unexpected or incorrect results.
Why do We Need NULL Values?
Null functions are required to perform operations on null values ??stored in the database. With NULL values, we can perform operations that clearly identify whether the value is null or not. With this ability to recognize null data, operations similar to SQL’s join methods can be performed on them.
Following are the NULL functions defined in SQL:
ISNULL()
The ISNULL function has 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,
Find the sum of the salary of all Employees, if the Salary of any employee is not available (or NULL value), use salary as 10000.
Query:
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
Fetch the name of all employees whose salary is available in the table (not NULL).
Query:
SELECT Name FROM Employee WHERE ISNULL(Salary);
Output:
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,
Find the sum of the salary of all Employees, if the Salary of any employee is not available (or NULL value), use salary as 10000.
Query;
SELECT SUM(IFNULL(Salary, 10000) AS Salary FROM Employee;
Output:
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,
Fetch the name and contact number of each employee.
Query:
SELECT Name, COALESCE(Phone1, Phone2) AS Contact FROM Contact_info;
Output:
NULLIF()
The NULLIF function takes two arguments. 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:
Conclusion
In this article, we learned what null values are and why we should use them. We now know that using NULL values is fundamental to databases and is done so in order to preserve their integrity. Following this, we learned more about the different functions that can be used with NULL values. In conclusion, the NULL value in SQL represents the absence or unknown value for a particular data field. It requires special consideration when writing queries and expressions to handle NULL values properly and avoid potential pitfalls.
Similar Reads
PL/SQL Functions
PL/SQL functions are reusable blocks of code that can be used to perform specific tasks. They are similar to procedures but must always return a value. A function in PL/SQL contains:Function Header: The function header includes the function name and an optional parameter list. It is the first part o
4 min read
MySQL ISNULL( ) Function
The MySQL ISNULL() function is used for checking whether an expression is NULL or not. This function returns 1 if the expression passed is NULL; otherwise, it returns 0. The ISNULL() function in MySQL accepts the expression as a parameter and returns an integer with a value of a value 0 or 1 dependi
2 min read
SQL General Functions
SQL general functions are built-in tools provided by SQL databases to perform a variety of operations on data. These functions are crucial for manipulating, analyzing, and transforming data efficiently. In this article, We will learn about the what are the SQL General Functions along with the exampl
5 min read
SQL | Numeric Functions
SQL Numeric Functions are essential tools for performing mathematical and arithmetic operations on numeric data. These functions allow you to manipulate numbers, perform calculations, and aggregate data for reporting and analysis purposes. Understanding how to use SQL numeric functions is important
4 min read
SQL MIN() Function
The MIN() function in SQL is a powerful tool that allows us to determine the smallest or lowest value from a specified column or expression. It is widely used in data analysis to extract minimum values for decision-making, reporting, and business insights. This function automatically excludes NULL v
8 min read
SQL LAG() Function
The LAG() function in SQL is a powerful window function that allows you to retrieve the value of a column from the previous row in the result set. It is commonly used for tasks like calculating differences between rows, tracking trends, and comparing data within specific partitions. In this article,
4 min read
PLSQL | LN Function
The LN function is an inbuilt function in PLSQL which is used to return the natural logarithm of a given input number. The natural logarithm of a number is the logarithm of that number to the base e, where e is the mathematical constant approximately equal to 2.718. This is written using the notatio
2 min read
LN() Function in MySQL
LN() function : It is the function in MySQL is used to calculate the natural logarithm of a specific number with base e . The number must be greater than 0, otherwise it will return NULL. Syntax : LN(X) Parameter : LN() function accepts one parameter as mentioned above in the syntax and described be
2 min read
PLSQL | LOG Function
The PLSQL LOG function is used for returning the logarithm of n base m. The LOG function accepts two parameters which are used to calculate the logarithmic value. The LOG function returns a value of the numeric data type. This function takes as an argument any numeric data type as well as any non-nu
2 min read
SQL LTRIM() Function
The SQL LTRIM() function is an essential tool used in data cleaning and manipulation tasks. This function helps remove unwanted leading spaces or specific characters from the left side of a string or string expression. It's commonly used to tidy up data by eliminating unnecessary spaces or character
4 min read