NULLIF function in sql server

The NULLIF () function returns NULL if two expressions are equal, otherwise, it returns the first expression.

Syntax

NULLIF ("expression 1", "expressions 2")

We will get the same result using the following CASE statement

CASE   WHEN "expression 1 = expression 2 " THEN NULL   ELSE   "expression 1"   END

Let’s see the example.

 

If both the expression value is the same in this case it will return Null.

If both the expression is not the same in this case it will return the first expression value.

 

Use of NULLIF function

This function we are using generally in the mathematical calculation like dividing a number or find the percentage.

declare @a int=25,                  @b int=5; select @a/@b

It will return to 5.

Suppose we are getting the value of @b is zero then that will happen. We will get the error.

Suppose we are using this script in SP then SP will fail. To avoid such type of error we need to handle it in the script. If we get the value of @b is zero then we need to set the result as zero.

With the help of NULLIF function we will handle such type of issue.

Let’s see the below example.

As we discuss above if expression 1 and expression both value is same in this case it will return the null value and if we divide any number with null in this case we will get the result as null. Finally with the help of the ISNULL function we will convert null to zero value.

Read More about ISNULL function: Different ways to replace A NULL value in SQL server

https://bageshkumarbagi-msbi.blogspot.com/2015/12/different-ways-to-replace-null-in-sql.html

Remove Correlated subquery (Use Joins) from sql script

A correlated subquery is a subquery that uses the values of the outer query. In other words, it depends on the outer query for its values. Because of this dependency, a correlated subquery cannot be executed independently as a simple subquery. Moreover, a correlated subquery is executed repeatedly, once for each row evaluated by the outer query. The correlated subquery is also known as a repeating subquery.

Correlated queries are sometimes called synchronized queries.

See the below example

Database: AdventureWorks2017

select p.Name, p.ProductNumber, (select psc.Name from Production.ProductSubcategory psc where psc.ProductSubcategoryID=p.ProductSubcategoryID) as ProductSubcategory, (select pc.name from Production.ProductCategory pc where pc.ProductCategoryID in                 (select ProductCategoryID from Production.ProductSubcategory psc where pc.ProductCategoryID=psc.ProductCategoryID                                                                   and psc.ProductSubcategoryID in (select psc.ProductSubcategoryID from Production.ProductSubcategory psc                                                                   where psc.ProductSubcategoryID=p.ProductSubcategoryID ))) as Productcategory from Production.Product p

See the result  

The same result we will get using joins

Here I am using left join to get the result.

select p.Name, p.ProductNumber, psc.Name as productnubcategoryname, pc.name as productname from Production.Product p left join Production.ProductSubcategory psc on psc.ProductSubcategoryID=p.ProductSubcategoryID left join Production.ProductCategory pc on pc.ProductCategoryID=psc.ProductCategoryID

See the result

  

If we see the execution plan for both query we will get the idea which will be better to use.

Correlated query:

  

From a single table we are reading the data multiple times.

In sub-query row by row operation happen.

Join execution plan

In join set based execution happens.