Conditional

In SQL, Conditional expressions can be used in the

1. SELECT statement,
2. WHERE clause, and
3. ORDER BY clause to evaluate multiple conditions.

CASE expression

The CASE expression is a flow-control statement that allows you to add if-else logic to a query. It comes in two forms: simple and searched.

SELECT OrderID, Quantity,
    CASE
        WHEN Quantity > 30 THEN 'Over 30'
        ELSE 'Under 30'
    END AS QuantityText
FROM OrderDetails;

COALESCE expression

The COALESCE function returns the first non-null value in a list. It takes a comma-separated list of values and returns the first value that is not null.
The COALESCE function allows handling the case where you have possible NULL values in your data and you want to replace it with some other value.

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

SELECT ProductName,
    COALESCE(UnitsOnOrder, 0) As UnitsOnOrder,
    COALESCE(UnitsInStock, 0) As UnitsInStock,
FROM Products;

NULLIF expression

NULLIF is a built-in conditional function in SQL Server. The NULLIF function compares two expressions and returns NULL if they are equal or the first expression if they are not

//syntax
NULLIF(expression1, expression2);

SELECT NULLIF(5,5) AS Same,
       NULLIF(5,7) AS Different;

IIF expression

IIF function returns value_true if the condition is TRUE, or value_false if the condition is FALSE.

SELECT IIF (1>0, 'One is greater than zero', 'One is not greater than zero');