CASE in SQL Server

Sometimes, you required to fetch or modify the records based on some conditions. In this case, you may use cursor or loop for modify your records. In this situation Case expression is best alternative for Cursor/looping and also provides better performance.

You can use CASE expressions anywhere in the SQL Query like CASE expressions can be used with in SELECT statement, WHERE clauses, Order by clause, HAVING clauses,Insert, UPDATE and DLETE statements.

Format of CASE expression

The CASE expression has following two formats:

1. Simple CASE expression

   This compares an expression to a set of simple expressions to find the result. This expression compares an expression to the expression in each WHEN clause for equivalency. If the expression with in the WHEN clause is matched, the expression in the THEN clause will be returned.

 Syntax

1. CASE expression
2. WHEN expression1 THEN Result1
3. WHEN expression2 THEN Result2
4. ELSE ResultN
5. END

Searched CASE expressions

This expression evaluates a set of Boolean expressions to find the result. This expression allows comparison operators, and logical operators AND/OR with in each Boolean expression.

Syntax

1. CASE
2. WHEN Boolean_expression1 THEN Result1
3. WHEN Boolean_expression2 THEN Result2
4. ELSE ResultN
5. END

CASE Expression Example

1. CREATE TABLE dbo.Customer
2. (
3. CustID INT IDENTITY PRIMARY KEY,
4. FirstName VARCHAR(40) NOT NULL,
5. LastName VARCHAR(40) NOT NULL,
6. StateCode VARCHAR(20) NOT NULL,
7. PayRate money NOT NULL DEFAULT 0.00,
8. Gender VARCHAR(1) NOT NULL,
9. )
10. GO
11.  
12. INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)
13. VALUES('Anand', 'Kumar', 'UP', 150.00,'M')
14.  
15. INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)
16. VALUES('Bhati', 'Kapoor', 'MP', 50.00 ,'F')
17.  
18. INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)
19. VALUES('Pavan', 'Kumar', 'MP', 200.00 ,'M')
20.  
21. INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)
22. VALUES('Shrama', 'Sharma', 'DL', 180.00 ,'F')
23.  
24. INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)
25. VALUES('Pankaj', 'Khan', 'DL', 210.00 ,'M')
26. GO
27.  
28. SELECT * from Customer

Select Clause with CASE expression

1. --Simple CASE expression:
2. SELECT FirstName, State=(CASE StateCode
3. WHEN 'MP' THEN 'Madhya Pradesh'
4. WHEN 'UP' THEN 'Uttar Pradesh'
5. WHEN 'DL' THEN 'Delhi'
6. ELSE NULL
7. END), PayRate
8. FROM dbo.Customer
9.  
10. -- Searched CASE expression:
11. SELECT FirstName,State=(CASE
12. WHEN StateCode = 'MP' THEN 'Madhya Pradesh'
13. WHEN StateCode = 'UP' THEN 'Uttar Pradesh'
14. WHEN StateCode = 'DL' THEN 'Delhi'
15. ELSE NULL
16. END), PayRate
17. FROM dbo.Customer

Update Clause with CASE expression

1. -- Simple CASE expression:
2. UPDATE Customer
3. SET StateCode = CASE StateCode
4. WHEN 'MP' THEN 'Madhya Pradesh'
5. WHEN 'UP' THEN 'Uttar Pradesh'
6. WHEN 'DL' THEN 'Delhi'
7. ELSE NULL
8. END
9.  
10. -- Simple CASE expression:
11. UPDATE Customer
12. SET StateCode = CASE
13. WHEN StateCode = 'MP' THEN 'Madhya Pradesh'
14. WHEN StateCode = 'UP' THEN 'Uttar Pradesh'
15. WHEN StateCode = 'DL' THEN 'Delhi'
16. ELSE NULL
17. END

Having Clause with CASE expression

1. -- Simple CASE expression:
2. SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)
3. FROM dbo.Customer
4. GROUP BY StateCode,Gender,FirstName
5. HAVING (MAX(CASE Gender WHEN 'M'
6. THEN PayRate
7. ELSE NULL END) > 180.00
8. OR MAX(CASE Gender WHEN 'F'
9. THEN PayRate
10. ELSE NULL END) > 170.00)
11.  
12. -- Searched CASE expression:
13. SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)
14. FROM dbo.Customer
15. GROUP BY StateCode,Gender,FirstName
16. HAVING (MAX(CASE WHEN Gender = 'M'
17. THEN PayRate
18. ELSE NULL END) > 180.00
19. OR MAX(CASE WHEN Gender = 'F'
20. THEN PayRate
21. ELSE NULL END) > 170.00)

Self join in sql server

Self Joins in SQL Server- Find the Manager Name for each employee in the employee table

A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle combines and returns rows of the table that satisfy the join condition.

For example the following query returns employee names and their manager names for whom they are working.

Create table Emp

(

empid int primary key,name varchar(50),mgrid int)
Insert into Emp(empid,name,mgrid)values (1001,'Anand Upadhyay',1001); 

Insert into Emp(empid,name,mgrid)values (1002,'Shiv',1001);

Insert into Emp(empid,name,mgrid)values (1003,'Pawan Bhati',1001);

Insert into Emp(empid,name,mgrid)values (1004,'Pankaj Sharma',1002);

Insert into Emp(empid,name,mgrid)values (1005,'Harish Yadav',1003);

Insert into Emp(empid,name,mgrid)values (1006,'Yatesh Chauhan',1002);

Insert into Emp(empid,name)values (1007,'Chitranjan Upadhyay');

Insert into Emp(empid,name)values (1008,'Munish Upadhyay');

Self join query 


SELECT e.empid, e.name, m.name  "Manager" FROM Emp e, Emp m WHERE e.mgrid=m.empid;