SQL Day 15: Replacing NULL in SQL

LO1: Replacing NULL with ISNULL function

LO2: Replacing NULL with COALESCE function

LO3: Replacing NULL with Case Statement

Prerequisite: Self Joins

In the previous session, we have found the employee’s managers using the Left Outer Join and we get the NULL value for the employee who does not have the manager assign or we can say he is the BOSS!



So now we are trying to replace this NULL value with the specified text using different methods.

Replacing NULL with ISNULL function

The ISNULL function accepts two parameters. First is the expression and second are the replacement value i.e. the value which is replaced by NULL.



If the expression you have passed in ISNULL function returns the NULL then NULL will be replaced by the given value as per the second parameter.

Now applying the ISNULL function in the Left Join to replace the NULL value with the text saying “BOSS”.

select         e.Name as Employee, ISNULL(m.Name,'BOSS') as Manager
from           tblemployee e
Left Join      tblemployee m
ON             e.ManagerID=m.ID



As you can see in the screen above, the NULL value us replaced by the text “BOSS”.

Replacing NULL with COALESCE function

The COALESCE function is similar to the ISNULL function but the COALESCE  function is more powerful than the ISNULL function. In COALESCE we can pass the multiple expression which will be covered in next session in greater details.

To use the COALESCE function we need to replace the ISNULL with COALESCE in the query and we will get the same result.

select         e.Name as Employee, COALESCE(m.Name,'BOSS') as Manager
from           tblemployee e
Left Join      tblemployee m
ON             e.ManagerID=m.ID

Note: We will talk about the COALESCE function in next session.

Replacing NULL with Case Statement

The Case statement is also easy to use. Below is the syntax of Case Statement.

Syntax: Case When ‘Expression’ Then ‘Value1’ Else ‘Value2’ End

If the expression returns the true then the value1 will be used else value2 is used when expression returns false.

Let’s use the Case Statement to replace the NULL value.

select          e.Name as Employee, 
                CASE 
                WHEN m.Name IS NULL THEN 'BOSS' ELSE m.Name
                END
                as Manager
from            tblemployee e
Left Join       tblemployee m
ON              e.ManagerID=m.ID



That’s all for today. Thank You.

Comments

Post a Comment

Popular posts from this blog

SQL Day 11: Group By in SQL Server

Image
LO1: Grouping rows using Group By LO2: Filtering Groups LO3: Difference between Where and Having Clause Group By clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expression. It is always used in conjunction with one or more aggregate functions. Let’s say we have a table tblEmployee which contains all the salary of employees including the cities. So if we need to find the maximum salary of employee we can use the MAX(Column_Name) aggregate function to find the highest salary of employees. select MAX(salary) from tblemployee In the same way, we can find the minimum salary and total salary of all the employees using different aggregate functions. select MIN(salary) as [Minimum Salary] from tblemployee select SUM(salary) as [Total Salary] from tblemployee Let’s say we have to find out the employee salary as per the different cities, to achieve this we need to use the Group B...
Keep reading

JAVA Script Executor Code to Click on an Element

Click on an Element in Selenium WebDriver Sometimes click() method does not work because two views are loaded at the same time on a page. In this case, we can use the JavascriptExecutor to get the work done. Below is code to click on an element using Javascript Executor. 1 2 3 4 5 6 7 @FindBy(id = Checkbox. financialAssistanceAccountEmailId ) public WebElement financialAssistanceAccountEmail; //Click to element JavascriptExecutor executor = (JavascriptExecutor) driver; executor. executeScript ( "arguments[0].click()" , financialAssistanceAccountEmail);
Keep reading

SQL Day 2: Creating, Altering and Dropping a Database

Image
LO1: Creating, Altering and Dropping a Database Prerequisite:   Connecting to SQL Server using SSMS After connecting to the database through SSMS first we need to create the database on which different queries will run. Let’s start with creating a Database. A SQL Server Database can be Created, Altered or Dropped in two ways: Graphically using SQL Server Management Studio or Using SQL Query Graphically using SQL Server Management Studio: Within the Object Explorer, right-click on Database folder and select the New Database. When clicked on New Database a new dialog box will open. Here you need to provide the database name and when clicking on OK, SQL Server run the query internally and will create the new database for us. When you expand the database folder then you will see the Sample database here Creating Database Using Query: We can also create the database by executing the query in the query editor. For creating a...
Keep reading