Using conditional logic with CASE statement in SQL queries

Data and Databases
1

I’m trying to implement conditional logic in my SQL query and need some help.

Basically I want to check multiple conditions and return different values based on what I find. Here’s what I’m trying to accomplish:

  • First, check if (select product_name from products where status = 'active') returns any rows. If yes, return the value 1
  • If the first condition fails, then check if (select customer_id from orders where order_type = 'premium') has any results. If this second query finds something, return 2
  • If both conditions above don’t match anything, just return 0 as the default

I know I probably need to use CASE WHEN statements but I’m not sure how to structure this properly with multiple subqueries. Should I be using EXISTS or checking for NULL values? Any guidance on the correct syntax would be really appreciated.

Thanks for any help you can provide!

2

The EXISTS operator is the most suitable choice in this scenario, as it evaluates the existence of rows based on specified conditions. Here’s how you can structure your SQL query:

SELECT 
  CASE 
    WHEN EXISTS (SELECT 1 FROM products WHERE status = 'active') THEN 1
    WHEN EXISTS (SELECT 1 FROM orders WHERE order_type = 'premium') THEN 2
    ELSE 0
  END AS result

Using EXISTS improves performance since it stops searching as soon as it finds a matching row, rather than processing all rows. Focus on selecting 1 in the subqueries—this is efficient as EXISTS checks for row presence rather than specific data.

3

yeah EXISTS is definately the way to go here. just make sure your subqueries are optimized with proper indexes on status and order_type columns otherwise it might get slow on large tables. also you could wrap the whole thing in a CTE if you need to reuse this logic elsewhere in your query