NULL and Its Tantrum in SQL

NULLs are always tricky to handle, Yes! you are right. But don’t worry I got something to explain it all.

In SQL, “NULL” represents the absence of a value in a database table column. It is not the same as an empty string, zero, or any specific value. Instead, it is used to signify that the data for a particular column in a specific row is missing, unknown, or undefined.

Let’s understand with key points:

Representation: NULL is a reserved keyword in SQL and is used to represent the absence of data. It is typically displayed as “NULL” in query results. A NULL value is not equivalent to a value of ZERO if the data type is a number and is not equivalent to spaces if the data type is a character.

Nullability: Each column in a database table can be defined as either “nullable” or “not nullable” when the table is created. If a column is nullable, it can contain NULL values. If a column is defined as not nullable, it must have a valid data value, and NULL is not allowed in that column.

Comparing NULL: Comparing NULL values with other values in SQL requires special handling. When you compare a NULL value with another value using equality (=), the result is neither true nor false; it is NULL. For example, NULL = NULL is not true in SQL; it’s NULL.

1. Get total record count from table where a column value is null.
       SELECT COUNT(*) FROM Table WHERE Column IS NULL
2. Get total record count from table where a column value is not null.
       SELECT COUNT(*) FROM Table WHERE Column IS NOT NULL

Usage: NULL can be used in various contexts in SQL, such as:

• When a piece of data is not available.
• When a value is missing in a record.
• When the value is unknown or undefined.

Functions and NULL: SQL provides functions to work with NULL values, such as:

• IS NULL: Used to check if a value is NULL.
• IS NOT NULL: Used to check if a value is not NULL.
• COALESCE: Used to replace NULL values with a specified default value.
• NULLIF: Used to replace one value with NULL if it matches another value.

Aggregate Functions: Aggregate functions like SUM, COUNT, AVG, etc., often ignore NULL values when performing calculations. This behavior can be controlled using functions like ISNULL or COALESCE.

JOINs: When joining tables in SQL, NULL values in the join columns can affect the results. Depending on the type of JOIN used (e.g., INNER JOIN, LEFT JOIN, RIGHT JOIN), records with NULL values may or may not be included in the result set.

Indexing: The presence of NULL values in columns can affect the indexing of those columns, which can, in turn, impact query performance. Some database systems may treat NULL values differently when it comes to indexing.

Some Null scenarios encountered during my work.

Arithmetic Operation:

SELECT 5 + NULL As Addition ;
SELECT 5 - NULL As Substration ;
SELECT 5 * NULL As Multiplication ;
SELECT 5 / NULL As Divide ;

Comparison Operation:

SELECT 
 CASE WHEN NULL = NULL THEN 'Pass Equal' ELSE 'Fail Equal' END As Equal
,CASE WHEN 5 > NULL THEN 'Pass Grater Than' ELSE 'Fail  Grater Than' END As  Grater_Than

Understanding how NULL values work in SQL is crucial for database design, query writing, and data handling. It’s essential to consider the behavior of NULL in the specific database system you’re using, as it can vary slightly between different database management systems.

Feedback is always appreciated. Please do comment if you have any.
Cheers 🥂