SQL Joins and replace NULL values with Predefined text

muralikrishnasurap-12704
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 17982 red flag

We all know use of joins in SQL Server where we can merge two(or) more table information into one set of data based some predefined conditions.

Introduction

In this article, we will learn how to joins two tables and replace null values with predefined text.


Objective

Working with inner join, Left Outer Join and Right Outer Join

Description:

We all know use of joins in SQL Server where we can merge two(or) more table(s) information into one set of data based on some predefined conditions.

Apart from this, we will learn how to replace null values with custom message when required.

Setp1: Create tables with data as shown below, where EmployeeID is P_K(Primary Key) in EmployeDetails and F_K(Foreign Key) in EmployeeSalary table.



Setp2:

                We want to extract complete information of  employee details from EmployeeDetaila and EmployeSalary tables  using  SQL joins.

QUERY:

Select   EmployeeSalary.EmployeId,EmployeDetails.FirstName,EmployeDetails.LastName,EmployeDetails.EmailID

from EmployeDetails RIGHT OUTER join EmployeeSalary on EmployeDetails.EmployeId=EmployeeSalary.EmployeId

Output is as shown below:



Setp3:

                If there is a requirement to replace NULL values with predefined text we can achieve this using below query.


select EmployeeSalary.EmployeId,ISNULL(EmployeDetails.FirstName,'NoDetails') as FirstName ,ISNULL(EmployeDetails.LastName,'NoDetails') as LastName,ISNULL(EmployeDetails.EmailID,'NoDetails') as EmailID

from EmployeDetails RIGHT OUTER join EmployeeSalary on EmployeDetails.EmployeId=EmployeeSalary.EmployeId


OutPut is as shown below:



Conclusion

SQL functions can be used for many tasks and in this article we looked into one of the function ISNULL



Page copy protected against web site content infringement by Copyscape

About the Author

muralikrishnasurap-12704
Full Name: Murali Krishna
Member Level: Starter
Member Status: Member
Member Since: 11/21/2011 10:12:45 AM
Country: India
Best Regards Murali Krishna.S


Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)