Default Value of SELECT When NO Records Found

Introduction

We are using SELECT statement to retrieve data from table. But here we are trying to construct such kind of SELECT, if no records found it returns a Default Value.
Hope it would be informative.

How We DO That  ?

Step – 1 [ Create Base Table and Insert Records ]

CREATE TABLE tbl_Employee
  (
     EmpID          INT NOT NULL,
     EmpName     VARCHAR(50) NOT NULL
  );


GO

INSERT INTO tbl_Employee
VALUES(1,  'Joydeep Das'),
                (2,  'Deepaasree Das'),
        (3,  'Santi Nath');


Step – 2  [ Now Try with EmpID that we have in the Table ]

SELECT COALESCE(A.EmpName, B.EmpName) AS EmpName
FROM (
SELECT EmpName AS EmpName FROM tbl_Employee WHERE EmpID = 1) A
       RIGHT OUTER JOIN (SELECT 'Not Found' AS EmpName) B ON 1 = 1;

Output:

EmpName
Joydeep Das

Step – 3 [ Now Try with EmpID that we Don’t have in Table ]

SELECT COALESCE(A.EmpName, B.EmpName) AS EmpName
FROM (
SELECT EmpName AS EmpName FROM tbl_Employee WHERE EmpID = 10) A
       RIGHT OUTER JOIN (SELECT 'Not Found' AS EmpName) B ON 1 = 1;

Output:

EmpName
Not Found




Hope you understand it.




Posted By: MR. JOYDEEP DAS