NVL Function in SQL: How to Use NVL for Null Value Handling in SQL Queries

Practice Exams:

View All

NVL Function in SQL: How to Use NVL for Null Value Handling in SQL Queries

In SQL, the term NULL represents a missing or undefined value in a database field. It is important to understand that NULL is not the same as zero or an empty string. A field with a NULL value means that it has no data stored in it at all. This distinction is critical because NULL values affect how SQL queries return results and how data is processed.

When dealing with data, NULL values can complicate calculations and comparisons because SQL treats NULL as unknown. For example, any arithmetic operation involving NULL results in NULL. Similarly, comparing NULL to any value, even another NULL, yields unknown or false.

Why Handling NULL Values is Important

Handling NULL values correctly is essential for accurate data analysis and reporting. When queries do not account for NULL, the results can be misleading or incomplete. For instance, if a column representing sales has NULL values for some rows, simply summing that column without handling NULL might exclude those rows from calculations.

SQL provides several functions to detect and replace NULL values, ensuring that queries return meaningful results. Among these functions, NVL, ISNULL, COALESCE, and CASE statements are commonly used to manage NULLs effectively.

NVL Function in Oracle

The NVL function is specific to Oracle databases and is used to substitute a value when a NULL is encountered. It is a null substitution function that returns the first expression if it is not NULL; otherwise, it returns the second expression.

NVL helps you avoid NULL values in query results by replacing them with alternative values, such as zero, an empty string, or any other meaningful default.

Syntax of NVL

The basic syntax of the NVL function is:

sql

CopyEdit

NVL(expression1, expression2)

expression1: The value or column to check for NULL.
expression2: The replacement value if expression1 is NULL.

Example Usage

Consider a table named Vegetables with a column Costpkg representing the cost per kilogram. If some rows have NULL values in Costpkg, you can use NVL to replace those NULLs with zero.

sql

CopyEdit

SELECT Vname, NVL(Costpkg, 0) AS CostPerKg FROM Vegetables;

This query returns the vegetable name and the cost per kilogram, replacing NULL costs with zero. This way, calculations or reports using this query will not break due to NULLs.

Key Points About NVL

NVL is only available in Oracle and not supported in SQL Server or MySQL. It handles only two parameters: the value to check and the alternative to return if the first is NULL. It is not an acronym; NVL simply stands for its purpose—null value substitution.

ISNULL Function in SQL Server

Replacing NVL with ISNULL in SQL Server

SQL Server does not support Oracle’s NVL function. Instead, it uses the ISNULL function, which provides similar functionality by checking if an expression is NULL and replacing it with a specified alternative.

ISNULL returns the first expression if it is not NULL; otherwise, it returns the replacement value.

Syntax of ISNULL

The syntax of the ISNULL function is:

sql

CopyEdit

ISNULL(expression, replacement_value)

Expression: The value or column to check for NULL.
replacement_value: The value to return if the expression is NULL.

Example of ISNULL in Action

Using the same Vegetables table, you can replace NULL values in Costpkg with zero using ISNULL:

sql

CopyEdit

SELECT Vname, ISNULL(Costpkg, 0) AS CostPerKg FROM Vegetables;

The output will show the cost per kilogram for each vegetable, replacing any NULL values with 0.

How ISNULL Works

ISNULL evaluates whether the first expression is NULL. If it is, it returns the alternative value specified as the second parameter. Otherwise, it returns the original value. This function is particularly useful for handling missing data gracefully in SQL Server environments.

Differences Between NULL, Zero, and Empty Values

Distinguishing NULL from Zero and Empty Strings

It is crucial to understand the differences between NULL, zero, and empty strings:

NULL means no data exists in the field.
Zero (0) is a numeric value representing nothing or none.
An empty string (”) is a string data type with zero length but still has a value.

SQL treats these three differently, and queries must be written accordingly to handle each case appropriately.

Why the Difference Matters

When performing aggregations or filters, a NULL is ignored or treated as unknown, while zero and empty strings are treated as actual values. For example, summing a column that contains NULLs will ignore those rows, but zero values will be included in the sum.

Incorrectly treating NULL as zero or vice versa can lead to inaccurate query results and flawed business insights.

Introduction to NVL2 Function in Oracle

While NVL and ISNULL handle simple null substitution, Oracle provides a more advanced function called NVL2. This function allows you to specify two possible outcomes depending on whether the first expression is NULL or not.

NVL2 offers greater control over how null values are processed by allowing different results for the NULL and NOT NULL cases in a single expression.

Syntax of NVL2

The syntax for NVL2 is:

sql

CopyEdit

NVL2(expr1, expr2, expr3)

expr1: The expression checked for NULL.
expr2: The value returned if expr1 is NOT NULL.
expr3: The value returned if expr1 is NULL.

How NVL2 Works

The NVL2 function evaluates the first expression. If it is not NULL, NVL2 returns the second expression; if it is NULL, it returns the third expression. This conditional logic can be very useful in reporting or complex data transformations.

Example Using NVL2

Consider the Vegetables table with a column Costpkg. You want to return the cost if it exists, but if it is NULL, return a message indicating the price is unavailable.

sql

CopyEdit

SELECT Vname,

NVL2(Costpkg, Costpkg, ‘Price not available’) AS CostStatus

FROM Vegetables;

If Costpkg contains a value, that value is displayed. If it is NULL, the string ‘Price not available’ is shown instead.

Using CASE Statements as a Replacement for NVL2 in SQL Server

SQL Server does not support NVL2 directly. However, the same logic can be implemented using a CASE statement, which provides even more flexibility.

Syntax of CASE for Null Handling

The CASE statement syntax to mimic NVL2 is:

sql

CopyEdit

CASE

WHEN expression IS NOT NULL THEN result_if_not_null

ELSE result_if_null

END

Example Using CASE

To replicate the NVL2 example for SQL Server, the query would be:

sql

CopyEdit

SELECT Vname,

CASE

WHEN Costpkg IS NOT NULL THEN CAST(Costpkg AS VARCHAR)

ELSE ‘Price not available’

END AS CostStatus

FROM Vegetables;

The CASE statement checks whether Costpkg is NULL. If not NULL, it returns the cost; otherwise, it returns a descriptive string.

DECODE Function in Oracle

What is the DECODE Function?

Oracle provides the DECODE function, which is similar to a simplified IF-THEN-ELSE statement or CASE expression. DECODE evaluates an expression and compares it to one or more values, returning the corresponding result.

Syntax of DECODE

sql

CopyEdit

DECODE(expression, search_value1, result1, search_value2, result2, …, default_result)

Expression: The value to be compared.
search_value: Values to compare against.
Result: Result returned if the expression matches the search value.
default_result: Result returned if no match is found.

Example of DECODE

Suppose you want to classify vegetable prices:

sql

CopyEdit

SELECT Vname, Costpkg,

DECODE(

SIGN(Costpkg – 20),

1, ‘The price is Moderate’,

0, ‘The price is Moderate’,

-1, ‘The price is Affordable’,

‘The Price is not Updated’

) AS PriceCategory

FROM Vegetables;

This example uses the SIGN function to compare Costpkg to 20. Depending on whether Costpkg is above, equal to, or below 20, it assigns a category. If Costpkg is NULL or does not match, the default category is returned.

Using CASE Statements for Complex Conditional Logic

While DECODE is compact, CASE statements provide greater versatility, supporting complex logical conditions including multiple AND/OR combinations.

Syntax of CASE

sql

CopyEdit

CASE

WHEN condition1 THEN result1

WHEN condition2 THEN result2

…

ELSE default_result

END

Example of CASE for Multiple Conditions

Classify the price with multiple conditions using CASE:

sql

CopyEdit

SELECT Vname, Costpkg,

CASE

WHEN Costpkg IS NULL THEN ‘The Price is not Updated’

WHEN Costpkg > 20 THEN ‘The price is Moderate’

ELSE ‘The Price is Affordable’

END AS PriceCategory

FROM Vegetables;

This query returns one of three labels based on the cost value, handling NULL explicitly.

COALESCE Function in SQL

What is COALESCE?

COALESCE is a standard SQL function that returns the first non-NULL expression from a list of expressions. It is more flexible than NVL or ISNULL because it can take multiple arguments.

Syntax of COALESCE

sql

CopyEdit

COALESCE(expr1, expr2, expr3, …, exprN)

It evaluates each expression in order and returns the first one that is not NULL. If all expressions are NULL, it returns NULL.

Advantages of COALESCE Over NVL and ISNULL

Supports multiple expressions instead of just two.
Standard SQL function, supported across most databases.
Can be used for cascading default values.

Example Using COALESCE

Replace NULL values in Costpkg with zero, or if that is NULL, replace with a default:

sql

CopyEdit

SELECT Vname, Costpkg, unitPuc,

Costpkg * (unitPuc + COALESCE(Costpkg, 0)) AS TotalCost

FROM Vegetables;

In this example, COALESCE ensures any NULL in Costpkg is replaced by zero before calculations.

NULLIF Function

What Does NULLIF Do?

NULLIF compares two expressions and returns NULL if they are equal. Otherwise, it returns the first expression. This function is useful for avoiding division by zero errors or filtering out unwanted values.

Syntax of NULLIF

sql

CopyEdit

NULLIF(expr1, expr2)

Returns NULL if expr1 = expr2.
Returns expr1 otherwise.

Example of NULLIF

Check lengths of two columns, returning NULL if they are equal:

sql

CopyEdit

SELECT LEN(Vname) AS VegetableLength,

LEN(Shopid) AS ShopLength,

NULLIF(LEN(Vname), LEN(Shopid)) AS Result

FROM Vegetables;

If the lengths are the same, the Result will be NULL; otherwise, it will show the length of Vname.

LNNVL Function in Oracle

Purpose of LNNVL

LNNVL stands for “Logical Not Null Value Logic.” It evaluates conditions involving NULLs in WHERE clauses, where standard boolean logic might fail.

How LNNVL Works

LNNVL takes a condition as input and returns TRUE if the condition is FALSE or UNKNOWN (NULL), and FALSE otherwise. It helps filter rows where the condition might be NULL.

Usage Example

Count the number of rows where Costpkg is NULL or evaluates to FALSE:

sql

CopyEdit

SELECT COUNT(*)

FROM Vegetables

WHERE LNNVL(Costpkg);

This query counts rows where Costpkg is NULL or does not meet the condition.

NANVL Function in Oracle

What is NANVL?

NANVL is designed to handle NaN (Not a Number) values, which are specific to floating-point numbers like BINARY FLOAT or BINARY DOUBLE in Oracle.

Purpose of NANVL

If an expression evaluates to NaN, NANVL replaces it with a specified alternative value. If the expression is not NaN, it returns the original value.

Syntax of NANVL

sql

CopyEdit

NANVL(expr1, expr2)

Returns expr2 if expr1 is NaN.
Returns expr1 if it is not NaN.

Example of NANVL

Replace NaN values in Costpkg with zero:

sql

CopyEdit

SELECT VId, NANVL(Costpkg, 0)

FROM Vegetables;

This ensures that NaN values do not disrupt calculations.

Handling NULL Values in SQL: Deep Dive into Functions and Best Practices

In database management, NULL represents the absence of a value or unknown data. Handling NULLs properly is critical for accurate data processing, querying, and reporting. Without proper NULL handling, aggregate functions, conditional logic, and joins may produce incorrect results or unexpected behavior.

This part delves deeper into advanced NULL handling techniques, exploring additional functions, performance considerations, and best practices for using NVL, ISNULL, COALESCE, and other related functions effectively.

The Importance of NULL in SQL

What is NULL?

NULL is not zero or an empty string; it signifies “no value” or “unknown value.” It differs from zero (a numeric value) and empty strings (text value of length zero). Many misunderstandings arise because NULL propagates through expressions, causing the result to also become NULL.

Effects of NULL in SQL Operations

Arithmetic operations with NULL return NULL.
Comparisons with NULL (e.g., = NULL) do not return TRUE or FALSE; they return UNKNOWN.
NULL values can distort aggregate functions like SUM, AVG unless explicitly handled.
Joins involving NULLs require careful logic to avoid dropping rows unintentionally.

Understanding this behavior is essential when using NVL, ISNULL, COALESCE, or CASE for substitution or conditional checks.

Differences Between NVL, ISNULL, and COALESCE

NVL Function

Available in Oracle.
Accepts exactly two parameters: an expression and a replacement value.
Returns the replacement if the expression is NULL.
Only supports two arguments.

Example:

sql

CopyEdit

SELECT NVL(Costpkg, 0) FROM Vegetables;

Returns Costpkg or 0 if Costpkg is NULL.

ISNULL Function

Available in SQL Server.
Works similarly to NVL.
Two parameters: expression and replacement value.
Slightly different data type handling than NVL.

Example:

sql

CopyEdit

SELECT ISNULL(Costpkg, 0) FROM Vegetables;

Returns Costpkg or 0 if NULL.

COALESCE Function

ANSI SQL standard, supported in Oracle, SQL Server, MySQL, and PostgreSQL.
Accepts multiple arguments.
Returns the first non-NULL value in the list.
Preferred for complex expressions needing multiple fallbacks.

Example:

sql

CopyEdit

SELECT COALESCE(Costpkg, unitPuc, 0) FROM Vegetables;

Returns Costpkg if not NULL, else unitPuc if not NULL, else 0.

Key Differences

Function	Number of Arguments	Standard SQL Support	Returns	Notes
NVL	2	Oracle only	expr2	Simple null replacement
ISNULL	2	SQL Server only	expr2	Similar to NVL
COALESCE	2 or more	Most DBs	first non-NULL	More flexible, ANSI SQL standard

Using NULL Handling Functions in Real-World Queries

Handling NULLs in SELECT Queries

When displaying data, it is often necessary to replace NULLs with a default value for readability and usability.

Example showing vegetable names and costs, replacing NULL with zero:

sql

CopyEdit

SELECT Vname, NVL(Costpkg, 0) AS CostPerKg FROM Vegetables;

In SQL Server:

sql

CopyEdit

SELECT Vname, ISNULL(Costpkg, 0) AS CostPerKg FROM Vegetables;

This avoids NULL showing up in reports or dashboards.

Using COALESCE for Multiple Fallbacks

Sometimes there may be several columns with possible values, and you want to display the first available one.

Example:

sql

CopyEdit

SELECT Vname, COALESCE(Costpkg, AlternativeCost, 0) AS EffectiveCost FROM Vegetables;

Here, AlternativeCost might be a backup cost column if Costpkg is NULL.

Handling NULL in WHERE Clauses

Why NULLs Cause Issues in WHERE Clauses

Standard comparison operators do not behave as expected with NULL. For example:

sql

CopyEdit

SELECT * FROM Vegetables WHERE Costpkg = NULL;

This returns no rows because = NULL is UNKNOWN, not TRUE.

Proper NULL Checks

Use IS NULL or IS NOT NULL:

sql

CopyEdit

SELECT * FROM Vegetables WHERE Costpkg IS NULL;

sql

CopyEdit

SELECT * FROM Vegetables WHERE Costpkg IS NOT NULL;

Using NVL or COALESCE in WHERE Clauses

Sometimes it helps to replace NULLs with a default for comparisons:

sql

CopyEdit

SELECT * FROM Vegetables WHERE NVL(Costpkg, 0) > 10;

Or with COALESCE:

sql

CopyEdit

SELECT * FROM Vegetables WHERE COALESCE(Costpkg, 0) > 10;

This treats NULLs as zero in the comparison.

Handling NULLs in JOIN Conditions

INNER JOIN and NULLs

In INNER JOINs, rows with NULL values in join columns are usually excluded because NULL does not match any value, including another NULL.

Example:

sql

CopyEdit

SELECT a.. Vname, b.ShopName

FROM Vegetables a

JOIN Shops b ON a.Shopid = b.Shopid;

Rows with NULL in a.Shopid column will not appear.

LEFT JOIN and NULLs

LEFT JOIN returns all rows from the left table, and NULLs fill columns from the right table where no match exists.

Using NULL Handling in JOINs

To treat NULL as a match, use functions like NVL or COALESCE in the join condition:

sql

CopyEdit

SELECT a.. Vname, b.ShopName

FROM Vegetables a

JOIN Shops b ON NVL(a.Shopid, ‘UNKNOWN’) = NVL(b.Shopid, ‘UNKNOWN’);

This can allow NULLs to match a default placeholder.

Impact of NULL Handling on Performance

Function Calls in WHERE Clauses

Using functions like NVL, ISNULL, or COALESCE on columns in WHERE clauses can disable index usage, leading to full table scans and performance degradation.

Example of Poor Performance

sql

CopyEdit

SELECT * FROM Vegetables WHERE NVL(Costpkg, 0) > 10;

This might prevent index usage on Costpkg.

Performance-Friendly Alternatives

Use IS NULL checks without wrapping in functions.
Create computed columns with null substitution for indexing.
Use CASE statements carefully.
Ensure statistics and indexes are up to date.

Advanced NULL Handling Techniques

Using CASE for Complex NULL Logic

CASE statements allow combining multiple conditions and handling NULL explicitly.

Example:

sql

CopyEdit

SELECT Vname,

CASE

WHEN Costpkg IS NULL THEN ‘No Price’

WHEN Costpkg > 20 THEN ‘Expensive’

ELSE ‘Affordable’

END AS PriceCategory

FROM Vegetables;

This categorizes prices with explicit NULL handling.

Combining NULL Handling with Aggregations

Aggregate functions like SUM, AVG ignore NULLs by default.

Example:

sql

CopyEdit

SELECT SUM(Costpkg) FROM Vegetables;

Only sums non-NULL values.

Using NVL or COALESCE with Aggregates

To treat NULL as zero in aggregates:

sql

CopyEdit

SELECT SUM(NVL(Costpkg, 0)) FROM Vegetables;

sql

CopyEdit

SELECT SUM(COALESCE(Costpkg, 0)) FROM Vegetables;

This includes NULLs as zero.

NULL and Data Integrity

Importance of Handling NULLs Correctly

Incorrect NULL handling can cause data integrity issues like missing information, incorrect calculations, and misleading reports.

Enforcing NOT NULL Constraints

When a column must always have a value, enforce a NOT NULL constraint:

sql

CopyEdit

ALTER TABLE Vegetables MODIFY Costpkg NOT NULL;

Using Default Values to Avoid NULL

Setting default values prevents NULL insertion:

sql

CopyEdit

ALTER TABLE Vegetables MODIFY Costpkg DEFAULT 0;

This helps maintain data consistency.

Additional Examples

Example 1: Replacing NULL with Default Text

sql

CopyEdit

SELECT Vname,

COALESCE(Description, ‘No description available’) AS Description

FROM Vegetables;

Example 2: Filtering Rows with NULL in Join Columns

sql

CopyEdit

SELECT a.. Vname, b.ShopName

FROM Vegetables a

LEFT JOIN Shops b ON a.. Shopid = b.Shopid

WHERE a.. Shopid IS NOT NULL;

Example 3: Handling NULL in Calculations

sql

CopyEdit

SELECT Vname,

Costpkg,

unitPuc,

COALESCE(Costpkg, 0) * COALESCE(unitPuc, 0) AS TotalCost

FROM Vegetables;

Advanced NULL Handling in SQL: Comprehensive Guide to NVL, ISNULL, COALESCE, and Related Functions

Introduction

Handling NULL values accurately in SQL is a foundational skill for database professionals and developers. While previous parts covered the basics and intermediate usage of NVL, ISNULL, COALESCE, and CASE statements, this final part will explore advanced scenarios, troubleshooting, database-specific nuances, and optimization techniques to handle NULLs efficiently across different SQL platforms.

NULL Handling Across Different SQL Platforms

Oracle

Oracle uses NVL() as the primary function to replace NULL with a specified alternative. Oracle also supports NVL2(), COALESCE(), NULLIF(), and other NULL-handling functions.

NVL(expr1, expr2) returns expr2 if expr1 is NULL.
NVL2(expr1, expr2, expr3) returns expr2 if expr1 is not NULL; otherwise returns expr3.
COALESCE() returns the first non-null value in the argument list.
NULLIF(expr1, expr2) returns NULL if expr1 = expr2, else returns expr1.
Oracle’s optimizer is aware of these functions and usually handles them efficiently.

SQL Server

SQL Server uses ISNULL() similarly to Oracle’s NVL but also supports COALESCE() as the ANSI SQL standard. Unlike NVL, ISNULL() is a proprietary SQL Server function.

ISNULL(expression, replacement_value) replaces NULL with the replacement value.
COALESCE(expression1, expression2, …, expressionN) returns the first non-null.
NULLIF(expr1, expr2) works identically to Oracle.
SQL Server also has TRY_CAST, TRY_CONVERT for handling conversion issues with NULLs.

MySQL

MySQL supports IFNULL(), COALESCE(), and NULLIF():

IFNULL(expr1, expr2) works like NVL.
COALESCE() supports multiple arguments and returns the first non-null.
NULLIF() behaves as expected.
MySQL treats empty strings and NULLs differently, and performance considerations may differ.

PostgreSQL

PostgreSQL supports:

COALESCE() as primary null replacement.
NULLIF(), ISNULL() (a boolean check, not replacement).
CASE expressions.

Deep Dive into NVL2 Functionality

Understanding NVL2

NVL2 evaluates the first expression and returns one of two possible values based on whether the expression is NULL or not.
Syntax:

sql

CopyEdit

NVL2(expr1, expr2, expr3)

Returns expr2 if expr1 is not NULL.
Returns expr3 if expr1 is NULL.
Example:

sql

CopyEdit

SELECT Vname,

NVL2(Costpkg, ‘Price Available’, ‘No Price’) AS PriceStatus

FROM Vegetables;

This will display “Price Available” if Costpkg is not NULL; otherwise, “No Price.”

NVL2 Equivalent in SQL Server

SQL Server does not have NVL2. Instead, a CASE statement is used:

sql

CopyEdit

SELECT Vname,

CASE WHEN Costpkg IS NOT NULL THEN ‘Price Available’ ELSE ‘No Price’ END AS PriceStatus

FROM Vegetables;

This technique is portable across most SQL dialects.

Handling NULLs in Aggregation and Grouping

Impact of NULL on Aggregate Functions

Aggregate functions like SUM, COUNT, AVG, MIN, and MAX treat NULLs differently:

SUM, AVG ignore NULLs.
COUNT(column) ignores NULLs.
COUNT(*) counts rows regardless of NULL.
MIN and MAX ignore NULLs.

Example

sql

CopyEdit

SELECT COUNT(Costpkg) AS CountNonNullCost,

COUNT(*) AS TotalRows,

SUM(Costpkg) AS TotalCost,

AVG(Costpkg) AS AverageCost

FROM Vegetables;

Using COALESCE or NVL in Aggregates

To treat NULL as zero in sums or averages:

sql

CopyEdit

SELECT SUM(COALESCE(Costpkg, 0)) AS TotalCostWithDefaults

FROM Vegetables;

Handling NULL in Joins and Subqueries

NULLs in JOIN Conditions

NULL values in join columns often cause unmatched rows because NULL is not equal to anything, including NULL.

Example of LEFT JOIN including NULLs

SQL

CopyEdit

SELECT v.. Vname, s.ShopName

FROM Vegetables v

LEFT JOIN Shops s ON v.. Shopid = s.Shopid;

Rows with NULL Shopid will have NULL for ShopName.

Using NULL Handling Functions in JOIN

SQL

CopyEdit

SELECT v.. Vname, s.ShopName

FROM Vegetables v

LEFT JOIN Shops s ON COALESCE(v.Shopid, ‘UNKNOWN’) = COALESCE(s.Shopid, ‘UNKNOWN’);

This matches rows where Shopid is NULL to a placeholder value.

NULL Handling in Filtering and Sorting

Filtering NULLs with WHERE

WHERE clause requires explicit IS NULL or IS NOT NULL checks for NULL values.

sql

CopyEdit

SELECT * FROM Vegetables WHERE Costpkg IS NULL;

Sorting and NULL Values

By default, in most SQL databases:

NULL values appear first or last, depending on the database.
Use ORDER BY Costpkg ASC NULLS LAST (Oracle, PostgreSQL).
SQL Server does not support NULLS LAST; workaround involves CASE expressions.
Example for SQL Server:

sql

CopyEdit

SELECT * FROM Vegetables

ORDER BY CASE WHEN Costpkg IS NULL THEN 1 ELSE 0 END, Costpkg;

NULL in Data Modification Statements

Inserting NULLs Explicitly

sql

CopyEdit

INSERT INTO Vegetables (Vname, Costpkg) VALUES (‘Tomato’, NULL);

Updating NULL Values

sql

CopyEdit

UPDATE Vegetables SET Costpkg = 0 WHERE Costpkg IS NULL;

Dealing with NULL in Constraints

NOT NULL constraints disallow NULL.
Unique constraints treat NULLs differently across DBMSs.
Foreign key constraints may allow NULL as “no relationship.”

Advanced Techniques: Using NULLIF and LNNVL

NULLIF Function

NULLIF(expr1, expr2) returns NULL if expr1 = expr2, otherwise returns expr1.
Example:

sql

CopyEdit

SELECT Vname, NULLIF(Costpkg, 0) AS CostpkgOrNull FROM Vegetables;

This converts zero values to NULL.

LNNVL Function (Oracle Specific)

LNNVL(condition) returns TRUE if condition is FALSE or UNKNOWN. Useful in WHERE clauses to test conditions involving NULLs.
Example:

sql

CopyEdit

SELECT * FROM Vegetables WHERE LNNVL(Costpkg > 20);

Returns rows where Costpkg is less than or NULL.

NaN and NANVL Function

NaN Handling in Floating-Point Data

NaN (Not a Number) can appear in floating-point computations.

NANVL Function (Oracle)

NANVL(expr1, expr2) returns expr2 if expr1 is NaN; else returns expr1.
Example:

sql

CopyEdit

SELECT VId, NANVL(Costpkg, 0) FROM Vegetables;

This replaces NaN values with zero.

Optimizing Queries with NULL Handling

Avoid Functions on Indexed Columns in WHERE

Using functions like NVL, ISNULL on indexed columns can prevent index usage.

Use Computed Columns or Persisted Columns

Create computed columns with NULL-handling logic for indexing.

Analyze Query Plans

Use EXPLAIN PLAN or query analyzer tools to check optimization.

Practical Use Cases and Examples

Replacing NULLs in Reporting

sql

CopyEdit

SELECT Vname, COALESCE(Costpkg, 0) AS Cost, COALESCE(unitPuc, 1) AS Unit

FROM Vegetables;

Categorizing Data with NULL Handling

sql

CopyEdit

SELECT Vname,

CASE

WHEN Costpkg IS NULL THEN ‘Unknown Price’

WHEN Costpkg > 50 THEN ‘Expensive’

ELSE ‘Affordable’

END AS PriceCategory

FROM Vegetables;

Joining Tables with Nullable Keys

sql

CopyEdit

SELECT v.. Vname, s.ShopName

FROM Vegetables v

LEFT JOIN Shops s ON v.. Shopid = s.Shopid

WHERE v.. Shopid IS NOT NULL;

Summary

Handling NULLs properly is essential for reliable SQL queries. Functions like NVL, ISNULL, COALESCE, NULLIF, and NVL2 provide powerful tools to deal with missing data, but their correct use depends on understanding their differences and implications on performance and results. This guide covered platform-specific nuances, advanced functions, optimization techniques, and real-world examples, empowering you to write robust SQL that handles NULLs effectively.