SQL query with if: Conditional Selection

Imagine you’re a detective. But instead of solving crimes, you’re solving data mysteries in your company’s database. That’s what writing SQL queries can feel like—searching through a sea of info to find exactly what you need. Today, let’s unlock a special tool in your SQL kit: the mighty IF statement for conditional selection.

TL;DR

SQL allows you to use IF-style conditional logic inside queries using functions like CASE, IF, and IFNULL. These give you the power to control how data is selected or displayed. Want different output for different conditions? You got it. It’s like telling SQL: “If this happens, do that!”

Why Conditional Logic?

Let’s say you have a list of customer orders. Some are from VIPs; some, regular folks. Wouldn’t it be cool to show “High Priority” next to VIP orders and “Standard” next to others? That’s where conditional selection kicks in.

You’re not just selecting rows—you’re deciding what to show based on conditions. Like a choose-your-own-adventure book, but for data!

The Hero of Our Story: CASE Statement

Meet CASE. It’s the SQL way to say “IF” without actually saying IF (sometimes).

SELECT
  customer_name,
  order_amount,
  CASE 
    WHEN order_amount > 1000 THEN 'Big Spender'
    ELSE 'Regular'
  END AS customer_type
FROM orders;

What’s happening here?

If the order is more than $1000, label it as “Big Spender”.
Otherwise, call them “Regular”.

Simple, right? You can do multiple conditions too!

CASE 
  WHEN order_amount > 5000 THEN 'Mega Spender'
  WHEN order_amount > 1000 THEN 'Big Spender'
  ELSE 'Regular'
END

You can use CASE in SELECT, UPDATE, ORDER BY, even WHERE clauses. It’s that flexible.

But Wait… There’s Also IF()

Some SQL versions (looking at you, MySQL) offer a direct IF() function. It works like the one in Excel.

SELECT 
  customer_name,
  IF(order_amount > 1000, 'Big Spender', 'Regular') AS spender_type
FROM orders;

This one-liner is neat and quick. But note: IF() is not part of standard ANSI SQL. That means databases like PostgreSQL won’t use it (they prefer CASE).

The Magic of IFNULL and COALESCE

Sometimes the “condition” is simply: “Is there a value here or not?”

Enter IFNULL() (in MySQL) or COALESCE() (in most databases):

SELECT 
  product_name,
  IFNULL(description, 'No description available') AS description
FROM products;

Or with COALESCE:

SELECT 
  product_name,
  COALESCE(description, 'No description available') AS description
FROM products;

So if the description is NULL, it shows our custom message instead. Goodbye, mystery NULLs!

Real World Example: Employee Bonuses

Let’s mix it all up! Assume you have a table called employees. You want to award bonuses:

10% for salespeople with over $50,000 in sales
5% if their sales are between $10,000 and $50,000
0% for anything less

SELECT 
  name,
  sales,
  CASE 
    WHEN sales > 50000 THEN sales * 0.10
    WHEN sales >= 10000 THEN sales * 0.05
    ELSE 0
  END AS bonus
FROM employees;

Now your query is a business brain! It knows context. It judges fairly!

Ordering With Conditions

You can also use conditional logic in ORDER BY to control sorting.

SELECT 
  name,
  last_login_date
FROM users
ORDER BY 
  CASE 
    WHEN last_login_date IS NULL THEN 1
    ELSE 0
  END,
  last_login_date DESC;

This makes sure users who have logged in (non-NULLs) appear first, sorted by date. Everyone else? At the bottom.

Filter With CASE? Not Quite.

Important note: You can’t directly use CASE in a WHERE clause like this:

-- This won't work:
SELECT * FROM orders
WHERE CASE 
        WHEN status = 'Shipped' THEN TRUE 
        ELSE FALSE 
      END;

Why? Because WHERE expects a simple TRUE or FALSE condition. But we can flip the style like this:

SELECT * FROM orders
WHERE 
  (status = 'Shipped' OR status = 'Delivered');

Or rewrite complex filters with subqueries or join logic instead of messing with CASE in WHERE.

Nested CASE: It Gets Real

You can also put one CASE inside another. It’s like Russian dolls, but more nerdy.

SELECT 
  name,
  sales,
  CASE 
    WHEN department = 'Sales' THEN
      CASE 
        WHEN sales > 50000 THEN 'Star'
        WHEN sales > 20000 THEN 'Strong Performer'
        ELSE 'Needs Improvement'
      END
    ELSE 'Not in Sales'
  END AS performance
FROM employees;

Was that too much? Don’t worry—usefulness will make it stick. Think of it like: “If they’re in Sales, then judge their performance; otherwise, say ‘Not in Sales’.”

Quick Recap!

Your SQL toolbox now includes:

CASE: The flexible and powerful IF-style tool for all databases
IF(): A MySQL shortcut for simple if-else logic
IFNULL() / COALESCE(): Say goodbye to NULLs
Nested CASE: When one condition isn’t enough

Fun Challenge!

Try creating a leaderboard using conditional logic. Use a table of students and their scores. Label them as “Pass”, “Fail”, or “Top Performer”. Bonus points if you sort by those labels!

Remember: SQL isn’t just for selecting data. It’s for understanding it. For storytelling. For judging who gets dessert.

Dive in. Break stuff. Make it better. Because when you master conditional logic in SQL, you’re not just querying—you’re speaking SQL with style.

WP Sticky