Join us in Las Vegas on June 13-16 for the Snowflake Summit

Learn More
datameer banner faq

How to use IF condition in SQL SELECT statement?

  • How-Tos FAQs
  • February 1, 2019

In SQL Server, we have below two options to implement IF condition in SQL SELECT statement.

  1. CASE WHEN… THEN
  2. IIF

Let us consider the following ‘student’ table.

CREATE TABLE student
(
student_name VARCHAR(50),
grade CHAR(1)
)

INSERT INTO student
SELECT 'Jack', 'A' UNION ALL
SELECT 'Rose', 'B' UNION ALL
SELECT 'John', 'C' UNION ALL
SELECT 'Dave', 'D' UNION ALL
SELECT 'Chris', 'F'

1. CASE WHEN… THEN

We can use this option when we have multiple cases to be handled.

SELECT student_name 'Student Name',
CASE grade
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Good'
WHEN 'C' THEN 'Average'
WHEN 'D' THEN 'Poor'
WHEN 'F' THEN 'Fail'
ELSE 'N/A'
END 'Grade'
FROM student

-- Output

Student Name Grade
--------------------------
Jack Excellent
Rose Good
John Average
Dave Poor
Chris Fail

A Faster Way To Build Data Models

Mix SQL and no code to create data models in a few clicks vs months.

Try Datameer Today

2. IIF

From SQL SERVER 2012, we have the option to use IIF statements. It is like a Shorthand form of CASE statement. We can conveniently use it when we need to decide between two options. There are three parts in IIF statement, first is a condition, second is a value if the condition is true and the last part is a value if the condition is false.

SELECT student_name 'Student Name',
IIF(grade = 'f', 'Fail', 'Pass') 'Pass/Fail'
FROM student

-- Output
Student Name Pass/Fail
---------------------------------
Jack Pass
Rose Pass
John Pass
Dave Pass
Chris Fail

Up Next:

Read How to create an AUTO_INCREMENT column in a table on Oracle DB using SQL?

More Resources We Think You Might Like

SQL FAQ Feat

Can we use stored procedures in a SELECT statem...

We can not directly use stored procedures in a SELECT statement. The database objects that can be...

  • How-Tos FAQs
  • December 17, 2018
SQL FAQ Feat

How to initialize an AUTO_INCREMENT column in M...

The value of AUTO_INCREMENT can be reinitialized using the below methods. ALTER TABLE… AUTO_INCRE...

  • How-Tos FAQs
  • February 1, 2019
SQL FAQ Feat

How to convert month number to month name in SQL

We do not have a built-in function to convert month number to month name in SQL. But we can use t...

  • How-Tos FAQs
  • December 17, 2018