SQL | Conditional Expressions

In SQL, conditional expressions allow you to determine the output based on certain conditions. They can be used in SELECT, WHERE, and ORDER BY clauses, among others. Here's an overview of some commonly used conditional expressions in SQL:

1. CASE Expression:

The CASE expression is the most versatile conditional expression in SQL. It allows for conditional logic to be used in the output of a query.

Syntax:

CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

Example:

SELECT name, salary,
    CASE 
        WHEN salary < 50000 THEN 'Low'
        WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
        ELSE 'High'
    END AS salary_grade
FROM employees;

2. COALESCE:

The COALESCE function returns the first non-NULL value in a list of expressions.

Syntax:

COALESCE(expr1, expr2, ... , exprN)

Example:

-- Returns salary if it's not NULL, otherwise returns 0
SELECT COALESCE(salary, 0) FROM employees;

3. NULLIF:

The NULLIF function returns NULL if the two specified expressions are equal, otherwise, it returns the first expression.

Syntax:

NULLIF(expr1, expr2)

Example:

-- Returns NULL if salary is 0, otherwise returns the salary
SELECT NULLIF(salary, 0) FROM employees;

4. IIF:

The IIF function is available in some RDBMS like SQL Server. It returns one of two values, depending on whether the condition provided evaluates to true or false.

Syntax:

IIF(condition, true_result, false_result)

Example:

-- SQL Server specific
SELECT IIF(salary > 50000, 'High', 'Low') AS salary_grade FROM employees;

5. DECODE (Oracle-specific):

In Oracle, the DECODE function allows you to perform a simple form of conditional logic in your SQL queries.

Syntax:

DECODE(expression, search1, result1, ..., searchN, resultN, default_result)

Example:

-- Oracle specific
SELECT DECODE(salary, 50000, 'Medium', 100000, 'High', 'Low') AS salary_grade FROM employees;

Points to Consider:

While conditional expressions are powerful, using them extensively, especially in large datasets, can impact query performance. Always ensure that your queries are optimized.
Always be aware of NULL values, as they can impact the outcome of your conditional expressions. Depending on the RDBMS and the specific expression, NULLs can be treated differently.
The support for certain functions and expressions varies among different RDBMSs. Always refer to the official documentation of your database system to understand the specifics.

Using IF-ELSE Conditions in SQL Queries: SQL doesn't have a direct IF-ELSE statement, but you can use the CASE statement for conditional logic.
```
SELECT column1,
       CASE
          WHEN column1 > 10 THEN 'High'
          ELSE 'Low'
       END AS category
FROM your_table;
```

Conditional Operators in SQL for Filtering:

SELECT column1, column2
FROM your_table
WHERE column1 > 10 AND column2 = 'Condition';

Working with NULL Values in SQL Conditional Expressions:

SELECT column1,
       CASE
          WHEN column2 IS NULL THEN 'No Data'
          ELSE column2
       END AS result
FROM your_table;

Conditional Aggregation in SQL using GROUP BY:

SELECT category, AVG(column1) AS avg_value
FROM your_table
GROUP BY category;

Using CASE WHEN in SQL for Conditional Logic:

SELECT column1,
       CASE
          WHEN column2 = 'A' AND column3 > 10 THEN 'Condition 1'
          WHEN column2 = 'B' THEN 'Condition 2'
          ELSE 'Default'
       END AS result
FROM your_table;

Advanced Examples of Conditional Expressions in SQL:

SELECT column1,
       CASE
          WHEN column2 = 'A' AND column3 > 10 THEN 'Condition 1'
          WHEN column2 = 'B' THEN 'Condition 2'
          WHEN column4 IS NULL THEN 'Unknown'
          ELSE 'Default'
       END AS result
FROM your_table;

Handling Multiple Conditions with SQL CASE Statement:

SELECT column1,
       CASE
          WHEN column2 = 'A' AND column3 > 10 THEN 'Condition 1'
          WHEN column2 = 'B' THEN 'Condition 2'
          WHEN column3 < 5 THEN 'Condition 3'
          ELSE 'Default'
       END AS result
FROM your_table;