SQL | Conditional Expressions

In SQL, conditional expressions are essential for making decisions based on certain conditions directly within queries. These expressions allow us to apply business logic, to return values based on conditions, and transform data without using complex procedures.

The CASE, DECODE, COALESCE, GREATEST, IFNULL, and LEAST functions are some of the most useful conditional expressions in SQL, enabling us to handle multiple scenarios. In this article, we’ll explain how to use these SQL conditional expressions effectively with detailed explanations and examples.

Create the Employee Table

Before diving into SQL conditional expressions, let's start by creating the Employee table and inserting some sample data into it. This will help us understand how these expressions work with real-world data.

Step 1: Create the Employee Table

CREATE TABLE Employee (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

Step 2: Insert Data into the Employee Table

INSERT INTO Employee (employee_id, first_name, last_name, department_id, salary)
VALUES 
(1, 'John', 'Doe', 50, 10000),
(2, 'Jane', 'Smith', 12, 12000),
(3, 'Bob', 'Johnson', 10, 15000),
(4, 'Alice', 'Williams', 50, 18000),
(5, 'Charlie', 'Brown', 12, 16000),
(6, 'David', 'Jones', 20, 20000),
(7, 'Eve', 'Davis', 10, 11000),
(8, 'Frank', 'Garcia', 12, 14000);

Step 3: View the Employee Table

SELECT * from Employee;

Output:

SQL Conditional Expressions

Now that the Employee table is created and populated, let’s dive into some commonly used SQL conditional expressions that you can use to perform operations on the data.

1. The CASE Expression in SQL

The CASE expression is the most common and flexible conditional expression in SQL. It allows us to perform conditional logic directly within our SELECT statement, much like IF-THEN-ELSE logic in traditional programming languages.

Syntax

CASE expr

WHEN comparison_expr1 THEN return_expr1

WHEN comparison_expr2 THEN return_expr2

...

ELSE else_expr

END

Example

SELECT first_name, department_id, salary,
       CASE department_id 
           WHEN 50 THEN 1.5 * salary
           WHEN 12 THEN 2.0 * salary
           ELSE salary
       END AS "REVISED SALARY"
FROM Employee;

Output:

2. The DECODE Function in SQL

The DECODE function is another way to implement conditional logic. It performs similarly to a CASE expression but with a simpler syntax. The DECODE function compares an expression with a set of values and returns a result based on the first matching value.

Syntax

DECODE(col/expression, search1, result1 [, search2, result2, ...], [, default])

Example

SELECT first_name, department_id, salary,
       DECODE(department_id, 50, 1.5 * salary,
                             12, 2.0 * salary,
                             salary) AS "REVISED SALARY"
FROM Employee;

Output:

3. The COALESCE Function in SQL

The COALESCE function returns the first non-null argument. It is particularly useful for handling NULL values and substituting them with a default value.

Syntax

COALESCE(value1, value2, ..., valueN)

Example

SELECT COALESCE(last_name, '- NA -')
FROM Employee;

Output:

4. The GREATEST Function in SQL

The GREATEST function returns the largest value from a list of expressions. It compares each expression and returns the one with the highest value, while considering case sensitivity.

Syntax

GREATEST(expr1, expr2, ..., exprN)

Example

SELECT GREATEST('XYZ', 'xyz') FROM dual;

Output:

5. The IFNULL Function in SQL

The IFNULL function is used to check whether an expression is NULL. If the expression is NULL, it returns the second argument; otherwise, it returns the first argument.

Syntax

IFNULL(expr1, expr2)

Example

SELECT IFNULL(1, 0) FROM dual;
SELECT IFNULL(NULL, 10) FROM dual;

Output:

6. The LEAST Function in SQL

The LEAST function returns the smallest value from a list of expressions. It works similarly to the GREATEST function but returns the smallest value.

Syntax

LEAST(expr1, expr2, ..., exprN)

Example

SELECT LEAST('XYZ', 'xyz') FROM dual;

Output:

7. The NULLIF Function in SQL

The NULLIF function compares two expressions. If the expressions are equal, it returns NULL; otherwise, it returns the first expression.

Syntax

NULLIF(expr1, expr2)

Example

SELECT NULLIF(9995463931, contact_num) FROM Employee;

Output:

Conclusion

SQL conditional expressions, such as CASE, DECODE, COALESCE, GREATEST, IFNULL, and LEAST, offer powerful ways to manipulate and transform data directly within queries. By using these expressions, we can make our SQL queries more flexible and efficient, allowing us to handle a wide range of business logic and conditions.