NVL in SQL: Understanding the NVL Function in SQL

Syntax

ISNULL( Value /Expression, alternative value )

ISNULL() takes two parameters. The first parameter is the value, or an expression checked for null. The second parameter is the value to be replaced if the result is true.

Code:

Select Vname,ISNULL(Costpkg,0),Shopid From Vegetables;

The Figure 1 table provides the details of vegetables and in the column cost per kg, it takes null values.

Figure 1: VEG_INFO_TABLE

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

After Applying ISNULL()

After applying the ISNULL to the table in Figure 1, the result is produced and provided in a table in Table 1. Here, all the null values are replaced with 0 using ISNULL().

Code:

Select Vname,ISNULL (Costpkg,0), shopid FROM Vegetables;

Table 1: VEG_INFO_TABLE 

NVL2

NVL2 works similar to ISNULL() but instead of checking and replacing one pair, NVL2 can check and replace two pairs of such cases in a single statement. Oracle NVL2 function is replaced by Case Statement in SQL Server.

The NVL2 function evaluates the first expression (expr1, expr2, expr3). The NVL2 function returns the second expression if the first expression is not null. If the first expression is null, the third expression is returned. That is, NVL2 returns expr2 if expr1 is not null. NVL2 returns expr3 if expr1 is null. It can include any data form in the expr1 statement.

Syntax:

SELECT ColumnName1,ColumnName2,…..ColumnNameN,

CASE conditi

WHEN  value/expression1 NOT NULL  THEN AlternateValue1

WHEN value/expression2  NOT NULL  THEN AlternateValue2

ELSE AlternateValue

END

FROM table_name;

Code:

SELECT Vname,Vid,

CASE

WHEN Costpkg is NOT NULL  THEN  costpkg

ELSE 0

END

FROM Vegetables

Table 2. Veg_info_Table after modification

DECODE()

It performs the function of a CASE or IF-THEN-ELSE argument, making conditional queries easier.

The DECODE function decodes an expression using IF-THEN-ELSE logic, which is common in programming languages. After comparing each search value to the expression, the DECODE function decodes it. The result is returned if the expression is the same as the quest.

DECODE() is a comparison feature that allows you to compare a base value of up to 255 evaluation values in a single call. If the base value does not equal any of the evaluation values, a default value can be passed at the end of the function to be returned.

Syntax:

SELECT ColumnName1,ColumnName2,…..ColumnNameN,

CASE conditi

WHEN  value/expression1 NOT NULL  THEN AlternateValue1

WHEN value/expression2  NOT NULL  THEN AlternateValue2

…..

WHEN value/expressionN NOT NULL  THEN AlternateValueN

ELSE AlternateValue

END

FROM table_name;

Code:

SELECT Vname,Vid,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 Cost

FROM Vegetables;

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

COALESCE

The COALESCE() function examines the first expression and, if it is not null, returns it; otherwise, it performs a COALESCE of the remaining expressions. The COALESCE() function has an advantage over the NVL() function, which is that it can take multiple alternative values. COALESCE() returns the first non-null expression in the sequence, in simple terms.

Code:

SELECT Vname,Vid,Costpkg,unitPuc,

Costpkg * (unitPuc + COALESCE(Costpkg, 0)) totalcost

FROM Vegetables;

NULLIF

You can compare two expressions using the NULLIF equation. The function returns null if they are equal. The function returns the first expression if they are not identical. For the first word, you can't use the literal NULL.

Code:

 SELECT len(Vname) "Vegetable",len(Shopid) "Shop" ,

   NULLIF(len(Vname),len(Shopid))

   Result FROM Vegetables;

 Table data

LNNVL

When either or both operands of a state are null, LNNVL evaluates it. You can only find the function in a query's WHERE clause. It takes a condition as an argument and returns TRUE if the condition is FALSE or UNKNOWN, and FALSE otherwise.

Code:

 create table Vegetables (VId int, Vname varchar(25), Costpkg float ,unitPuc int,Shopid varchar(30) );

insert into Vegetables values(103,'Carrot',20.0,30,'ER123');

insert into Vegetables values(101,'Onion',60.0,17,'ER123');

insert into Vegetables values(102,'lemon',5.0,400,'ER123');

insert into Vegetables values(104,'Cabbage',25.0,50,'ER123');

insert into Vegetables values(105,'Beans',10.0,35,'ER123');

select * from Vegetables;

SELECT COUNT(*) FROM Vegetables  WHERE LNNVL(Costpkg );

 Input:

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

NANVL

Only floating-point numbers of type BINARY FLOAT or BINARY DOUBLE can be used with the NANVL feature. If the input value n1 is NaN, it instructs the database to return an alternative value n2 (not a number). Database returns n1 if n1 is not NaN. This feature can be used to convert NaN values to NULL.

Code:

  SELECT VId, NANVL(Costpkg,0)

  FROM Vegetables;

Conclusion:

Only floating-point numbers of type BINARY FLOAT or BINARY DOUBLE are used with the NANVL feature. If the input value n1 is NaN, it instructs the Database to return an alternative value n2 (not a number). Database returns n1 if n1 is not NaN. This feature can be used to convert NaN values to NULL.

The word NULL is used to describe a missing value in SQL. In a table, a NULL value is a value in a field that appears to be empty. A field with a NULL value is the same as one that has no value. It's important to grasp the difference between a NULL value and a zero value or a field of spaces.

While SQL is an old language, it is still very important today as businesses all over the world gather vast quantities of data to expand. SQL is regularly among the most-requested tech skills and mastering it will enhance your skillset.

If you want to learn more about software development topics such as SQL or want to become a software developer, then check out Simplilearn's SQL Certification Course. This Bootcamp will help you master both front-end and back-end Java technologies, starting with the basics and progressing to the advanced aspects of Full Stack Web Development.

Angular, Spring Boot, web services, JSPs, and MongoDB are some of the many topics that are covered in this extensive program.

Have any questions for us? Leave them in the comments section of this article, and our experts will get back to you on them, as soon as possible!