How the Hell I use SQL IF Statement in SELECT [5 Methods]

Introduction

Navigating through SQL queries might seem complex, but it’s all about using the right tools and methods to get the information you need. In this tutorial, we will explore the magic of using SQL if statement in SELECT within your database queries. This technique is like a superpower that allows us to adjust and tailor the information we get from a database based on specific conditions or rules we set. We will guide you through five effective methods such as the CASE statement, IIF function, WHERE clause with OR/AND, CHOOSE function, and COALESCE function. These methods are like different paths to reach the same destination - making your SQL queries smart and responsive to conditions.

 

Different methods to use SQL IF Statement in SELECT

1. CASE Statement: The CASE statement directly introduces conditional logic (if-else) into SELECT statements, allowing different values to be returned based on evaluated conditions.
2. IIF Function: The IIF function is a concise way to implement if-else logic directly within a SELECT statement, evaluating a condition and returning specific values based on whether the condition is true or false.
3. Using WHERE Clause with OR/AND: While it might not be a direct if-else statement, the WHERE clause combined with OR/AND operators performs a similar role, allowing the crafting of queries that conditionally retrieve data based on logical combinations of multiple conditions.
4. CHOOSE Function: CHOOSE function, when combined with other expressions, can mimic if-else behavior within a SELECT statement by returning values based on a specified index or condition.
5. COALESCE Function (when combined with other functions like NULLIF): COALESCE, when paired with functions like NULLIF, can work as an if-else mechanism within SELECT statements, dealing with the replacement or handling of NULL or specific values based on evaluation.

 

Create Sample Table for Examples

Let us create a sample table which we will use to demonstrate all the examples:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Age INT,
    Department NVARCHAR(50),
    Salary INT
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Age, Department, Salary) VALUES
(1, 'Aarav', 'Patel', 28, 'HR', 50000),
(2, 'Pari', 'Gupta', 35, 'IT', 75000),
(3, 'Vihaan', 'Kumar', 40, 'Marketing', 65000),
(4, 'Saisha', 'Singh', 30, 'Finance', 70000),
(5, 'Reyansh', 'Sharma', 45, 'IT', 80000),
(6, 'Isha', 'Deshmukh', 25, 'HR', 52000),
(7, 'Yash', 'Agarwal', 33, 'Marketing', 67000),
(8, 'Anaya', 'Sethi', 29, 'Finance', 72000),
(9, 'Arjun', 'Menon', 37, 'IT', 78000),
(10, 'Siya', 'Yadav', 32, 'HR', 55000);

 

1. CASE Statement in SELECT

The CASE statement in SQL serves as a robust tool to implement conditional logic directly within a query. By using the CASE statement, an SQL if statement in SELECT can be easily incorporated to manage various conditions and return specific values based on the evaluation of these conditions. It’s versatile and can be utilized in SELECT, UPDATE, or DELETE statements, allowing for the customization of the data being retrieved or modified based on specific criteria.

For instance, if you want to categorize employees as 'Junior' or 'Senior' based on their ages:

SELECT 
    FirstName, 
    LastName, 
    Age,
    (CASE 
        WHEN Age < 35 THEN 'Junior'
        ELSE 'Senior'
     END) as EmployeeCategory
FROM Employees;

Categorize employees based on their salaries into different salary grades.

SELECT 
    FirstName, 
    LastName,
    Salary,
    (CASE 
        WHEN Salary < 60000 THEN 'Grade C'
        WHEN Salary BETWEEN 60000 AND 75000 THEN 'Grade B'
        ELSE 'Grade A'
     END) as SalaryGrade
FROM Employees;

This query categorizes employees into different salary grades using the CASE statement. The salary grade is determined within the SELECT statement itself, applying an SQL if statement in SELECT logic, categorizing the salaries into 'Grade C', 'Grade B', and 'Grade A' based on the specified salary ranges.

 

2. CHOOSE Function

The CHOOSE function simplifies conditional logic by acting as a lookup. It can be combined with other functions to mimic an SQL if statement in select, enabling you to specify various outcomes and choose among them based on a specified condition or index, thus making it possible to render results based on conditional logic directly within a SELECT statement.

SELECT 
    FirstName, 
    LastName,
    CHOOSE((CASE WHEN Age < 35 THEN 1 ELSE 2 END), 'Junior', 'Senior') as EmployeeCategory
FROM Employees;

This SQL query selects the first and last names of employees from the "Employees" table and categorizes them based on their age. If an employee is younger than 35, they are labeled as 'Junior'; otherwise, they are labeled as 'Senior'. This categorization appears in a new column named "EmployeeCategory.

SELECT 
    FirstName, 
    LastName,
    Department,
    CHOOSE(INDEX = (CASE WHEN Department = 'IT' THEN 1 ELSE 2 END), 'Technical', 'Non-Technical') as DepartmentType
FROM Employees;

This query uses the CHOOSE function along with the CASE statement to categorize the employees’ departments into 'Technical' and 'Non-Technical'. It’s a way to apply SQL if statement in SELECT, where it evaluates the Department and assigns an index, which is used by the CHOOSE function to select a corresponding value.

 

3. IIF Function

The IIF function provides a straightforward way to implement SQL if statement in SELECT. It is concise and allows for immediate evaluation of a condition, returning values based on whether the condition is true or false, thus embedding simple conditional logic directly within queries for immediate evaluation and results.

SELECT 
    FirstName, 
    LastName,
    IIF(Age < 35, 'Junior', 'Senior') as EmployeeCategory
FROM Employees;

In this example, each method applies an if-else logic to categorize employees based on their age, directly within a SELECT query or within a procedural script.

SELECT 
    FirstName, 
    LastName,
    Department,
    IIF(Department = 'IT' OR Department = 'HR', 'Core', 'Support') as DepartmentCategory
FROM Employees;

In this query, the IIF function is used as an SQL if statement in select to categorize departments into 'Core' and 'Support'. If an employee is from the 'IT' or 'HR' department, they are categorized as 'Core'; otherwise, they are categorized as 'Support'.

 

4. COALESCE Function

COALESCE is primarily used for handling NULL values but can also be used as an SQL if statement in SELECT when combined with other functions like NULLIF. It enables you to return the first non-null expression from a list, allowing for the creation of conditional logic to determine which values to display or manipulate within a query.

SELECT 
    FirstName, 
    LastName,
    COALESCE(NULLIF(Department, 'IT'), 'Not IT') as Department
FROM Employees;

This query uses the COALESCE function with NULLIF as a form of SQL if statement in SELECT. If the Department is 'IT', NULLIF returns null, and then COALESCE replaces it with 'Not IT'.

SELECT 
    FirstName, 
    LastName,
    COALESCE(NULLIF(Salary, 75000), 60000) as AdjustedSalary
FROM Employees;

Here, the COALESCE function serves as an sql if statement in select, adjusting the Salary value. If the Salary is 75000, it is replaced with 60000.

 

5. Using WHERE Clause with OR/AND

The WHERE clause, combined with OR/AND operators, acts as a powerful conduit for expressing SQL if statement in SELECT. It enables the crafting of rich and varied conditions within a SELECT statement, allowing for the retrieval of data that meets multiple criteria through logical combinations of conditions, enhancing the flexibility and specificity of data queries.

SELECT * 
FROM Employees 
WHERE (Department = 'IT' AND Salary > 60000) OR (Department = 'HR' AND Salary <= 60000);

This query utilizes the WHERE clause with AND/OR operators as an SQL if statement in SELECT to combine multiple conditions, allowing the retrieval of specific records based on complex conditional logic.

SELECT * 
FROM Employees 
WHERE Age < 35 OR (Age >= 35 AND Department = 'IT');

In this example, the WHERE clause is structured with OR/AND operators as an SQL if statement in SELECT to define conditions either based solely on age or a combination of age and department, demonstrating the versatility of conditional logic in SQL queries.

 

Frequently Asked Questions (FAQs)

 

Summary

In wrapping up, learning to use sql if statement in select is like learning to paint with different brushes - it helps to make your database queries more vibrant and detailed. The CASE statement is like your versatile brush, letting you create detailed scenarios in getting data. The IIF function is a simpler tool, making your queries clean with straightforward conditions. Using the WHERE clause with OR/AND is like mixing colors, where you can combine conditions to find the perfect match. The CHOOSE function simplifies things, acting like a shortcut to pick out specific pieces of data. Lastly, the COALESCE function helps in cleaning up, making sure you handle empty or missing data gracefully. Learning these methods will make your journey in SQL querying rich and enjoyable.

Further Reading:

• CASE statement
• IIF function
• WHERE clause with OR/AND
• CHOOSE function
• COALESCE function