Sql Query For If Else Condition

Conditional logic forms the backbone of dynamic data manipulation in relational databases. Now, whether you are transforming raw values for a report, categorizing rows for analysis, or implementing business rules directly within a query, understanding how to write a SQL query for if else condition scenarios is essential. While standard SQL does not use the exact keywords IF ... ELSE in the same way procedural languages like Python or C# do, every major database engine provides dependable mechanisms to achieve identical results. The two primary standards you will encounter are the CASE expression and the IIF (or IF) function, alongside vendor-specific procedural blocks for stored routines Worth keeping that in mind. Practical, not theoretical..

The Universal Standard: The CASE Expression

The ANSI SQL standard defines the CASE expression as the primary tool for conditional logic. Also, it works across PostgreSQL, MySQL, SQL Server, Oracle, SQLite, and virtually every other relational database. Because it is an expression rather than a statement, it returns a single value and can be used almost anywhere a column or literal value is valid—inside SELECT lists, WHERE clauses, ORDER BY clauses, GROUP BY clauses, and even SET clauses in UPDATE statements Most people skip this — try not to..

There are two distinct syntax flavors: the Simple CASE and the Searched CASE.

Simple CASE Expression

The simple variation compares a single input expression to a set of discrete values. It is ideal for mapping specific known values to new outputs, such as converting status codes into human-readable labels Simple, but easy to overlook..

SELECT 
    order_id,
    status_code,
    CASE status_code
        WHEN 'P' THEN 'Pending'
        WHEN 'S' THEN 'Shipped'
        WHEN 'C' THEN 'Cancelled'
        WHEN 'R' THEN 'Returned'
        ELSE 'Unknown Status'
    END AS status_description
FROM orders;

In this structure, the engine evaluates status_code once and checks it against each WHEN clause sequentially. The first match wins. That said, if no match is found, the ELSE block executes. Omitting ELSE results in a NULL return value, which is a common source of bugs in reporting queries.

Searched CASE Expression

The searched variation is significantly more powerful. Instead of comparing one column against static values, it evaluates a list of independent Boolean conditions. This allows for range checks, complex compound logic, and comparisons across multiple columns.

SELECT 
    employee_id,
    salary,
    CASE 
        WHEN salary < 30000 THEN 'Entry Level'
        WHEN salary >= 30000 AND salary < 60000 THEN 'Mid Level'
        WHEN salary >= 60000 AND salary < 100000 THEN 'Senior Level'
        ELSE 'Executive Level'
    END AS salary_band
FROM employees;

Notice that the input expression (salary) is not listed immediately after CASE. Each WHEN clause contains a full predicate. The engine evaluates these top-to-bottom. That said, **Order matters critically here. ** If you placed the salary < 100000 check before the salary < 60000 check, a salary of 50,000 would incorrectly match the first condition and be labeled 'Senior Level'.

Vendor-Specific Shorthand Functions

While CASE is portable, several major engines offer shorter syntax for simple true/false branching. These are syntactic sugar—they compile down to CASE internally—but they improve readability for binary decisions.

SQL Server and Azure SQL: IIF Function

Introduced in SQL Server 2012, IIF mimics the immediate IF function found in Visual Basic and Excel. It accepts three arguments: the boolean condition, the value if true, and the value if false That alone is useful..

SELECT 
    product_name,
    list_price,
    IIF(list_price > 500, 'Premium', 'Standard') AS pricing_tier
FROM products;

Nesting IIF functions is possible but quickly becomes unreadable. For more than two outcomes, revert to CASE.

-- Hard to read nested IIF
IIF(score >= 90, 'A', IIF(score >= 80, 'B', IIF(score >= 70, 'C', 'F')))

MySQL and MariaDB: IF Function

MySQL provides an IF() function (distinct from the procedural IF statement used inside stored programs). It behaves identically to SQL Server's IIF But it adds up..

SELECT 
    name,
    credits_earned,
    IF(credits_earned >= 120, 'Graduate', 'Undergraduate') AS student_status
FROM students;

MySQL also supports IFNULL(expr1, expr2) and COALESCE for null handling, which are specialized conditional shortcuts.

Oracle Database: DECODE and NVL

Oracle historically used the DECODE function before adopting standard CASE in later versions. DECODE performs value-by-value comparison similar to Simple CASE That alone is useful..

-- Oracle specific syntax
SELECT 
    department_id,
    DECODE(department_id, 10, 'Admin', 20, 'HR', 30, 'IT', 'Other') AS dept_name
FROM employees;

For null handling, Oracle uses NVL(expr1, expr2) (two arguments) whereas standard SQL uses COALESCE (unlimited arguments) Small thing, real impact. Worth knowing..

Handling NULLs in Conditional Logic

A frequent pitfall when writing a SQL query for if else condition logic involves NULL values. In SQL, NULL represents an unknown value, and any comparison with NULL (e.g.That's why , NULL = 'X' or NULL > 5) yields UNKNOWN, not TRUE or FALSE. As a result, a WHEN clause checking column = NULL will never match That alone is useful..

Incorrect:

CASE 
    WHEN middle_name = NULL THEN 'No Middle Name' -- Never triggers
    ELSE middle_name
END

Correct (Standard SQL):

CASE 
    WHEN middle_name IS NULL THEN 'No Middle Name'
    ELSE middle_name
END

Correct (Shorthand): Most engines support COALESCE or ISNULL/NVL/IFNULL for this specific scenario, which is cleaner than a full CASE block.

COALESCE(middle_name, 'No Middle Name') -- Standard ANSI
-- ISNULL(middle_name, 'No Middle Name') -- SQL Server
-- NVL(middle_name, 'No Middle Name')    -- Oracle
-- IFNULL(middle_name, 'No Middle Name') -- MySQL

Conditional Aggregation: Pivoting Data Without PIVOT

One of the most powerful applications of conditional logic is conditional aggregation. This technique allows you to calculate multiple aggregates over different subsets of data in a single pass of the table, effectively creating pivot tables or summary reports without proprietary PIVOT syntax.

It sounds simple, but the gap is usually here.

Imagine you need a report showing total sales, and the count of orders, broken down by year, with separate columns for 'Online' and 'Retail' channels.

SELECT 
    YEAR(order_date) AS sales_year,
    SUM(CASE WHEN channel = 'Online' THEN amount ELSE 0 END) AS online_revenue,
    COUNT(CASE WHEN channel = 'Online' THEN 1 END) AS online_orders,
    SUM(CASE WHEN channel = 'Retail' THEN amount ELSE 0 END) AS retail_revenue,
    COUNT(CASE WHEN channel = 'Retail' THEN 1 END) AS retail_orders
FROM sales
GROUP BY YEAR(order_date)
ORDER BY sales_year;

Inside the aggregate functions (SUM, COUNT), the CASE expression acts as a filter. For SUM, we return the amount for

the matching row; otherwise it returns 0, which does not alter the total. For COUNT, omitting the ELSE clause returns NULL for non-matching rows, and COUNT ignores NULL values, so only matching rows are tallied. This single-pass approach avoids multiple table scans or self-joins, making it significantly more efficient than running separate queries for each category.

When nesting conditions or handling more complex business rules, CASE expressions can be embedded inside one another, though readability should always be a priority. If the logic becomes too involved, consider using a derived table or CTE to stage the conditional flags before aggregating And it works..

Conclusion

Conditional logic is a cornerstone of practical SQL, but its behavior varies subtly across database platforms. For reporting, conditional aggregation transforms row-level data into multi-column summaries without proprietary pivot syntax, delivering both flexibility and performance. Regardless of dialect, the golden rule remains: never compare a column directly to NULL with =; use IS NULL, COALESCE, or the engine’s equivalent null-handling function instead. Now, standard CASE offers portability and clarity, while legacy constructs like Oracle’s DECODE or server-specific shortcuts such as IIF may appear in older codebases. By combining explicit NULL checks, consistent CASE usage, and set-based aggregation, you can write dependable queries that produce accurate results across any relational database That alone is useful..