SQL Reference

Example

SELECT
    CASE
        WHEN price > 200 THEN 'Expensive'
        ELSE 'Cheap'
    END AS Price_Category
FROM
    Products

Output

+----------------+
| Price_Category |
+----------------+
| Expensive      |
| Cheap          |
| Expensive      |
| Cheap          |
+----------------+

Explanation

The CASE statement is used to perform conditions in SQL. The THEN keyword acts as the outcome when the condition in the WHEN clause is met. In the given code, the CASE statement checks the price column, if the price is greater than 200 it displays ‘Expensive’, otherwise it displays ‘Cheap’. The result is a new column named Price_Category with the price categories.

Example

DECLARE @Num INT;
SET @Num = 10;

IF (@Num > 5)
BEGIN
   PRINT 'The condition is met.';
END
ELSE
BEGIN
   PRINT 'The condition is not met.';
END

Output

The condition is met.

Explanation

In the example provided, an integer variable (@Num) is declared and assigned the value of 10. The IF statement checks if the value of @Num is greater than 5. If the condition is met (which it is, because 10 is greater than 5), then it executes the statement within the BEGIN and END block after the IF clause, printing the message “The condition is met.”. If the condition is not met, then it executes the block after ELSE, printing “The condition is not met.” (This block is not executed in our example). The THEN keyword is implicit in SQL Server and is not explicitly written in the statement.

Example

SELECT first_name, department_id,
CASE
    WHEN department_id IS NULL THEN 'No department'
    ELSE 'Has department'
END AS department_status
FROM employees;

Output

FIRST_NAME | DEPARTMENT_ID | DEPARTMENT_STATUS
--------------------------------------------------------
John       | 30            | Has department
Steve      | 60            | Has department
Sarah      | NULL          | No department
James      | 80            | Has department

Explanation

In the given SQL statement, a CASE function is being used to create a new column department_status. If the department_id for a particular employee is NULL, the department_status is set as No department. If the department_id is not NULL, the department_status is set as Has department.

Example

SELECT
    CASE
    WHEN type = 'dog' THEN 'Canine'
    WHEN type = 'cat' THEN 'Feline'
    ELSE 'Other'
    END AS Animal_Class
FROM pets;

Output

| Animal_Class |
|--------------|
| Canine       |
| Canine       |
| Feline       |
| Other        |

Explanation

The CASE function in SQL performs a conditional logic in a SQL query. The THEN keyword plays a crucial role in this function where the output is defined based on the conditions. In this example, the CASE statement checks each row to see if the ‘type’ column corresponds to ‘dog’ or ‘cat’. If it’s ‘dog’, then ‘Canine’ is returned, if it’s ‘cat’, then ‘Feline’ is returned. If the ‘type’ column neither corresponds to ‘dog’ nor ‘cat’, ‘Other’ is returned. The new value is returned in a column named ‘Animal_Class’.