WikiGalaxy

Personalize

SQL CASE Statement

Introduction to SQL CASE Statement

What is SQL CASE?

The SQL CASE statement is a conditional expression that allows you to add if-then-else logic to your queries. It can be used in SELECT, INSERT, UPDATE, and DELETE statements.


      SELECT employee_id, 
             CASE 
                 WHEN salary > 50000 THEN 'High'
                 WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium'
                 ELSE 'Low'
             END AS salary_level
      FROM employees;

Why Use SQL CASE?

The SQL CASE statement is useful for transforming data output based on specific conditions, making it easier to read and understand the results.

Sample Output:

1 | High
2 | Medium
3 | Low

Using CASE in SELECT Statements

Basic Usage

You can use the CASE statement within a SELECT query to return different values based on specified conditions.


      SELECT product_name,
             CASE 
                 WHEN stock > 100 THEN 'In Stock'
                 ELSE 'Out of Stock'
             END AS availability
      FROM products;

Advantages

This approach enables dynamic data categorization directly within the query result set.

Sample Output:

Widget | In Stock
Gadget | Out of Stock

Using CASE in WHERE Clauses

Conditional Filtering

The CASE statement can also be employed in WHERE clauses to filter records based on dynamic conditions.


      SELECT order_id, customer_id
      FROM orders
      WHERE 
          CASE 
              WHEN customer_id IS NOT NULL THEN customer_id > 100
              ELSE FALSE
          END;

Benefits

This feature allows for more complex filtering logic to be implemented directly within the SQL query.

Sample Output:

101 | 150

CASE with Aggregate Functions

Combining with Aggregates

CASE can be combined with aggregate functions like SUM or COUNT for conditional aggregation.


      SELECT 
          department,
          SUM(CASE WHEN salary > 50000 THEN 1 ELSE 0 END) AS high_earners
      FROM employees
      GROUP BY department;

Use Cases

This technique is useful for calculating conditional totals or counts within grouped data.

Sample Output:

Sales | 5
Engineering | 3

Nested CASE Statements

Using Nested CASE

You can nest CASE statements within each other to handle more complex conditional logic.


      SELECT employee_id,
             CASE 
                 WHEN department = 'Sales' THEN
                     CASE 
                         WHEN salary > 60000 THEN 'Senior Sales'
                         ELSE 'Junior Sales'
                     END
                 ELSE 'Other'
             END AS role
      FROM employees;

Complex Logic Handling

Nested CASE statements allow for detailed and multi-layered decision-making within SQL queries.

Sample Output:

1 | Senior Sales
2 | Other

CASE in ORDER BY Clauses

Dynamic Ordering

The CASE statement can be used in ORDER BY clauses to sort query results dynamically based on conditions.


      SELECT product_name, price
      FROM products
      ORDER BY 
          CASE 
              WHEN price > 100 THEN 1
              ELSE 2
          END;

Benefits of Dynamic Sorting

This allows you to prioritize certain records over others based on custom logic.

Sample Output:

Luxury Watch | 150
Basic Watch | 50

CASE in UPDATE Statements

Conditional Updates

Use the CASE statement in UPDATE queries to update records conditionally.


      UPDATE employees
      SET bonus = 
          CASE 
              WHEN performance = 'Excellent' THEN 1000
              WHEN performance = 'Good' THEN 500
              ELSE 100
          END;

Advantages

This allows for targeted updates based on specific conditions, improving data accuracy.

Sample Output:

Employee bonuses updated based on performance ratings.

CASE in DELETE Statements

Conditional Deletions

While not directly used in DELETE statements, CASE can be employed in subqueries to determine which records to delete.


      DELETE FROM employees
      WHERE employee_id IN (
          SELECT employee_id
          FROM employees
          WHERE 
              CASE 
                  WHEN department = 'HR' THEN TRUE
                  ELSE FALSE
              END
      );

Benefits

This method allows for selective deletion based on complex conditions.

Sample Output:

Records from HR department deleted.