How to use SQL IF statement in SELECT? [SOLVED]

Different methods to use SQL IF statement in SELECT

In SQL IF.. THAN condition statement is used to perform different actions based on conditions defined within the statement. With SQL Server, We can perform real-time programmatic logic on the values within query, and generate values based on these logical evaluations using IF..THAN statement.

SQL Server do not support IF..THAN statement inside the SQL select statement to achieve this condition based result set with SQL select statement we can used any one of the following method.

There are two methods to perform an IF...THEN in an SQL SELECT

1. CASE .. WHEN
2. IIF function

 

Lab Environment

Consider student result management database to perform practical examples on CASE.. WHEN

student table

result table

 

Method-1: Using CASE..WHEN statement

SQL CASE..WHEN statement works similar to programming logic IF…THAN statement. CASE WHEN will check for condition like IF , returns a value when the condition is TRUE and statement written after THAN will executes .  If no conditions are true, it returns the value in the ELSE clause.

Syntax 

SELECT column1,column2,
CASE  column name, Expression
WHEN codition1 THEN true_statement1
WHEN condition2 THEN true_statement2
WHEN condition3 THEN true_statement3
 ELSE 
Else_statement
END 
AS alias name
FROM table_name

Column_name / expression:  Specified column name or expression to be compared with all when condition if matches the condition of the first WHEN clause, it skips all the further WHEN and THEN conditions and returns the statement_1 in the result.

Else : If no condition is matched with the expression, the control automatically goes to the ELSE part and returns its result. In the CASE syntax, the ELSE part is optional.

In Syntax, CASE and END are the most important keywords which show the beginning and closing of the CASE statement.

 

Example-1: Using CASE.. WHEN statement with single table

Write SQL query to retrieve  student result information with grade and assign remarks value based on grade value

SELECT  student_id AS 'Student ID', examname, 
CASE grade 
WHEN 'A+' THEN 'Excellent' 
WHEN 'A' THEN 'Good' 
WHEN 'B' THEN 'Average' 
WHEN 'C' THEN 'Poor' 
WHEN 'D' THEN 'Fail' 
ELSE 'N/A' 
END AS 'Grade'
FROM   tblresult

• In above SQL query , we have retrieve student result information with grade and also assign remark value based on condition
• Grade column name specified with case as expression to be compared with WHEN parameter.
• For Each record of result table grade column value will be compared and result remarks value .
• IF none of the grade value matched for any record it will be return with ‘N/A’ ELSE value.

OUTPUT:

 

Example-2: Using CASE.. WHEN statement with multiple tables

Write SQL query to retrieve student personal and result information also display admission year based on semester number using CASE .. WHEN condition

SELECT  tblresult.student_id AS 'Student ID', tblstudent.studentname, tblstudent.email, 
tblstudent.city, tblresult.examname, tblresult.pecentage, tblresult.grade, 
CASE examname 
WHEN 'sem1' THEN '2021' 
WHEN 'sem3' THEN '2020' 
WHEN 'sem5' THEN '2019' 
END AS 'Admission Year'
FROM   tblresult LEFT OUTER JOIN
tblstudent ON tblstudent.student_id = tblresult.student_id

• In above SQL query , we have retrieve student personal and result information using SQL LEFT OUTER JOIN and also display admission year based on semester value
• examname column specified with case as expression to be compared with WHEN parameter.
• For Each record of result table examname column value will be compared and display admission year based on value
• IF for any of the record in result set the semester name does not match then that record will not contain value in admission year column

OUTPUT:

 

Mehod-2: Using IIF Function

SQL IIF function supported in SQL server 2012 and higher versions , IIF function is used to check for condition and execute statement according to the same. SQL IIF function is t is a shorthand form of writing CASE statement logic or IF-ELSE logic.

IIF is a shorthand way of writing a CASE expression. By evaluating the Boolean expression passed as the first argument, it can return either of the two other arguments

Syntax

Select column_name1, clumn_name 2, 
IIF(column_name / expression, true_value, false_value) from table_name;

Column_name/expression: Specified column name or expression to be compared as condition if condition is TRUE than second argument value will be true else third argument value will be return.

 

Example-1: Using IIF function with single table

Write SQL query to retrieve student result information with grade and assign remarks value based on grade value

SELECT student_id, examname,pecentage, 
iif(grade='A+','Excellent',iif(grade='A','Good',iif(grade='B','Average',iif(grade='C','Poor' ,iif(grade='D','Fail','N/A'))))) AS 'Grade'
FROM  tblresult;

• In the above query, we have used nested IIF function to conditionally select grade remark .
• First outer IIF function will be evaluated if first condition that is if grade column value is ‘A+’ than assign remark ‘Excellent’ and if first condition is FALSE it will check in nested IIF function for next condition.

OUTPUT:

 

Example-2: Using IIF function with multiple tables

Write SQL query to retrieve student personal and result information also display admission year based on semester number using IIF function

SELECT  tblresult.student_id AS 'Student ID', tblstudent.studentname, tblstudent.email, 
tblstudent.city, tblresult.examname, tblresult.pecentage, tblresult.grade, 
IIF(examname='sem1','2021',IIF(examname='sem3','2020',IIF(examname='sem5','2019','2018'))) 
AS 'Admission Year'
FROM   tblresult LEFT OUTER JOIN
 tblstudent ON tblstudent.student_id = tblresult.student_id

• In the above query, we have used SQL IIF function to conditionally retrieve admission year based on semester.
• Nested IIF function is used to check for more than one condition.
• For each record of result record set of left join select query it will check for first condition is satisfied than it will print 2021 as the admission years otherwise it will check nested IIF function condition.
• SQL LEFT OUTER JOIN is used to retrieve data from more than one table

OUTPUT:

 

Summary

This article on select if statement in SQL discusses overviews of SQL select statements with If conditions, and lists two ways to perform IF..THAN in SQL select statements. The first is the CASE..WHEN statement, and the second is the IIF function, as well as practical examples using a single table and multiple tables using SQL LEFT JOIN.

 

References

SQL LEFT JOIN
SQL select

 

Read More

CASE (Transact-SQL)
How do I perform an IF...THEN in an SQL SELECT?