Database Essentials - Predefined Functions - Using CASE and WHEN

Databases Postgres
1

At times we might have to select values from multiple columns conditionally.

  • We can use CASE and WHEN for that.

  • Let us implement this conditional logic to come up with derived order_status.

    • If order_status is COMPLETE or CLOSED, set COMPLETED

    • If order_status have PENDING in it, then we will say PENDING

    • If order_status have PROCESSING or PAYMENT_REVIEW in it, then we will say PENDING

    • We will set all others as OTHER

  • We can also have ELSE as part of CASE and WHEN.

Youtube Video

Key Concepts Explanation

Key Concept 1

In this key concept, we are using CASE and WHEN statement to apply conditional logic based on specified conditions. Here is an example:

SELECT *,

    CASE 

        WHEN condition1 THEN result1

        WHEN condition2 THEN result2

        ELSE result3

    END AS new_column

FROM table_name

Key Concept 2

Another key concept involves using LIKE and IN operators within CASE and WHEN statements for more complex conditions. Here is an example:

SELECT *,

    CASE 

        WHEN column_name IN ('value1', 'value2') THEN 'Result1'

        WHEN column_name LIKE '%pattern%' THEN 'Result2'

        ELSE 'Result3'

    END AS new_column

FROM table_name

Hands-On Tasks

Here are some hands-on tasks to practice using CASE and WHEN:

  1. Practice writing SQL queries using CASE and WHEN with different conditional scenarios.
  2. Apply the learned concepts to real-world datasets by creating derived columns based on specific conditions.

Conclusion

In conclusion, mastering the usage of CASE and WHEN statements in SQL can greatly enhance your ability to manipulate and transform data based on specific criteria. Practice implementing these concepts in your queries and explore the endless possibilities of conditional logic in SQL. Happy learning and keep practicing!

Watch the video tutorial here