Table of Contents
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
- CASE .. WHEN
- 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
Read More
CASE (Transact-SQL)
How do I perform an IF...THEN in an SQL SELECT?