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

Free Course: Introduction to SQL

Learn MySQL, PostgreSQL and SQL ServerEnroll Now

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;

Free Course: Business Analysis Basics

Master the Fundamentals of Business AnalysisEnroll Now

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:

Post Graduate Program in Business Analysis

In partnership with Purdue UniversityVIEW COURSE

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;

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Certification Program. Enroll now!

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 gain expertise in SQL programming language, then Simplilearn's SQL Certification Training Course is for you. This SQL certification course provides you with everything you need to get started working with SQL databases and incorporating them into your applications. Learn how to organize your database correctly, write effective SQL statements and clauses, and maintain your SQL database for scalability. This course includes comprehensive coverage of SQL basics, comprehensive coverage of all relevant query tools and SQL commands, an industry-recognized course completion certificate, and lifetime access to self-paced learning.

Database and relationship management, Query tools and SQL commands, Aggregate functions, Group by clause, Tables and joins, Subqueries, Data manipulation, Transaction control, Views, and procedures are among the skills covered.

Start learning for free today's most in-demand skills. These courses emphasize the development of good core skills for future career advancement. Experts in the field will teach you. Get access to 300+ job-ready skills in today's most in-demand fields. Learn from anywhere, on any laptop, while working or studying. Explore free courses here. You can find free guides on various career paths, wages, interview tips, and more.

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!