SQL | Conditional Expressions

Conditional expressions in SQL allow decisions to be made directly within a query. They help return different results based on specific conditions, making queries more flexible and powerful. With these expressions, SQL can handle logic such as comparisons, validations, and conditional outputs right inside SELECT statements or filtering clauses.

Create the Employee Table

First, we will create a demo SQL database and table, on which use the Conditional Expressions

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 logic in traditional programming languages.

Syntax:

CASE expr    
    WHEN comparison_expr1 THEN return_expr1    
    WHEN comparison_expr2 THEN return_expr2    IF-THEN-ELSE 
     ...    
    ELSE else_expr
END

Query:

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)

Query:

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)

Query:

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)

Query:

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)

Query:

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)

Query

SELECT NULLIF(9995463931, contact_:num) FROM Employee;

Output: