The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Conditional Expressions

Next ►
►

Conditional expressions in SQL allow you to perform different actions based on certain conditions. They are essential for writing complex queries that require decision-making logic. In this section, we will cover the following key concepts:

  1. CASE Statement
  2. IF Statement
  3. COALESCE Function
  4. NULLIF Function

  1. CASE Statement

The CASE statement is a powerful tool for adding conditional logic to your SQL queries. It allows you to return different values based on specified conditions.

Syntax

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

Example

Let's consider a table employees with the following structure:

employee_id name salary department
1 Alice 50000 HR
2 Bob 60000 IT
3 Charlie 55000 HR
4 David 70000 IT
5 Eve 65000 Finance

We want to categorize employees based on their salary:

SELECT name,
       salary,
       CASE
           WHEN salary < 55000 THEN 'Low'
           WHEN salary BETWEEN 55000 AND 65000 THEN 'Medium'
           ELSE 'High'
       END AS salary_category
FROM employees;

Explanation

  • WHEN salary < 55000 THEN 'Low': If the salary is less than 55000, the category is 'Low'.
  • WHEN salary BETWEEN 55000 AND 65000 THEN 'Medium': If the salary is between 55000 and 65000, the category is 'Medium'.
  • ELSE 'High': For all other cases, the category is 'High'.

Result

name salary salary_category
Alice 50000 Low
Bob 60000 Medium
Charlie 55000 Medium
David 70000 High
Eve 65000 Medium

  1. IF Statement

The IF statement is another way to add conditional logic, but it is less commonly used in standard SQL and more often found in specific SQL dialects like MySQL.

Syntax

IF(condition, true_result, false_result)

Example

Using the same employees table, we can categorize employees based on their department:

SELECT name,
       department,
       IF(department = 'IT', 'Tech', 'Non-Tech') AS department_category
FROM employees;

Explanation

  • IF(department = 'IT', 'Tech', 'Non-Tech'): If the department is 'IT', the category is 'Tech'. Otherwise, it is 'Non-Tech'.

Result

name department department_category
Alice HR Non-Tech
Bob IT Tech
Charlie HR Non-Tech
David IT Tech
Eve Finance Non-Tech

  1. COALESCE Function

The COALESCE function returns the first non-null value in a list of expressions. It is useful for handling null values.

Syntax

COALESCE(expression1, expression2, ..., expressionN)

Example

Consider a table orders with the following structure:

order_id customer_id discount
1 101 NULL
2 102 10
3 103 NULL
4 104 5

We want to ensure that all null discounts are treated as zero:

SELECT order_id,
       customer_id,
       COALESCE(discount, 0) AS discount
FROM orders;

Explanation

  • COALESCE(discount, 0): If the discount is null, it returns 0. Otherwise, it returns the discount value.

Result

order_id customer_id discount
1 101 0
2 102 10
3 103 0
4 104 5

  1. NULLIF Function

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

Syntax

NULLIF(expression1, expression2)

Example

Using the same orders table, we want to set the discount to null if it is zero:

SELECT order_id,
       customer_id,
       NULLIF(discount, 0) AS discount
FROM orders;

Explanation

  • NULLIF(discount, 0): If the discount is 0, it returns null. Otherwise, it returns the discount value.

Result

order_id customer_id discount
1 101 NULL
2 102 10
3 103 NULL
4 104 5

Practical Exercises

Exercise 1

Using the employees table, write a query to categorize employees based on their department and salary. If the department is 'IT' and the salary is greater than 60000, the category should be 'Senior Tech'. Otherwise, it should be 'Other'.

Solution

SELECT name,
       department,
       salary,
       CASE
           WHEN department = 'IT' AND salary > 60000 THEN 'Senior Tech'
           ELSE 'Other'
       END AS category
FROM employees;

Exercise 2

Using the orders table, write a query to ensure that all null discounts are treated as zero and all zero discounts are treated as null.

Solution

SELECT order_id,
       customer_id,
       NULLIF(COALESCE(discount, 0), 0) AS discount
FROM orders;

Summary

In this section, we covered the following conditional expressions in SQL:

  • CASE Statement: Allows for complex conditional logic.
  • IF Statement: Provides a simple conditional check (specific to certain SQL dialects).
  • COALESCE Function: Returns the first non-null value in a list.
  • NULLIF Function: Returns null if two expressions are equal.

These tools are essential for writing flexible and powerful SQL queries. In the next section, we will explore subqueries and nested queries, which allow for even more complex data retrieval.

◄ Previous
◄
Next ►
►

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL Queries

Module 3: Working with Multiple Tables

Module 4: Advanced Data Filtering

Module 5: Data Manipulation

Module 6: Advanced SQL Functions

Module 7: Subqueries and Nested Queries

Module 8: Indexes and Performance Optimization

Module 9: Transactions and Concurrency

Module 10: Advanced Topics

Module 11: SQL in Practice

Module 12: Final Project

The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved