What is Conditional Logic in SQL? Explained With Code

Conditional Logic in SQL

Like “if-then-else” structures in other programming languages, conditional logic in SQL lets you do different things or return different answers depending on certain situations. Strong functionality for this are offered by Oracle SQL, mainly through the DECODE function and the CASE expression. These let you directly apply complex logic to your queries, which makes it possible for the database to effectively handle a variety of cases.

The DECODE Function

Value-by-value substitution is the basis of the DECODE function, a proprietary Oracle modification to the standard SQL language that is renowned for its succinct execution of “if-then-else” logic. It generates a result by comparing an expression to a set of predetermined values.

Format and Usage

The DECODE function’s fundamental format is: DECODE(value, if1, then1, if2, then2, ..., else).

1. value: This is the expression (e.g., a column or the result of a computation) that DECODE will evaluate for each row.
2. ifN: Each ifN is a value that value is compared against. Oracle checks value against if1, then if2, and so on, in sequence.
3. thenN: If value matches ifN, the corresponding thenN is returned.
4. else: This is an optional argument. If value does not match any of the ifN values, else is returned. If the else argument is omitted and no match is found, DECODE returns NULL.

DECODE allows for a maximum of 255 elements within its parentheses, which includes the initial value, all if and then pairs, and the else part. Oracle automatically converts value and each ifN to the datatype of the first ifN before comparison. Similarly, the return value is converted to the datatype of the first thenN or else. Importantly, DECODE treats two NULL values as equivalent during comparison.

Examples with DECODE

Let’s use a simplified NEWSPAPER table and a BOOKSHELF structure that draws inspiration from the sources to illustrate DECODE.

Basic Value Substitution

First, we’ll create a simple NEWSPAPER table and insert some data:

CREATE TABLE NEWSPAPER (
    Feature VARCHAR2(15) NOT NULL,
    Section CHAR(1),
    Page NUMBER
);

INSERT INTO NEWSPAPER (Feature, Section, Page) VALUES ('National News', 'A', 1);
INSERT INTO NEWSPAPER (Feature, Section, Page) VALUES ('Sports', 'D', 1);
INSERT INTO NEWSPAPER (Feature, Section, Page) VALUES ('Editorials', 'A', 12);
INSERT INTO NEWSPAPER (Feature, Section, Page) VALUES ('Business', 'E', 1);
INSERT INTO NEWSPAPER (Feature, Section, Page) VALUES ('Weather', 'C', 2);
INSERT INTO NEWSPAPER (Feature, Section, Page) VALUES ('Obituaries', 'F', 6);
COMMIT;

Using DECODE, we can now substitute more illustrative language for the numeric page numbers:

SELECT
    Feature,
    Section,
    DECODE(Page,
        1, 'Front Page',
        'Turn to ' || Page
    ) AS DisplayPage
FROM NEWSPAPER;

Output:

FEATURE         S DisplayPage
--------------- - -----------
National News   A Front Page
Sports          D Front Page
Editorials      A Turn to 12
Business        E Front Page
Weather         C Turn to 2
Obituaries      F Turn to 6

In this example, if the Page is 1, DECODE returns ‘Front Page’. For any other Page value, it concatenates ‘Turn to ‘ with the actual page number.

Replacing List Values

More understandable descriptions can be used in place of category codes when using DECODE. Now let’s arrange some tables:

CREATE TABLE CATEGORY (
    CategoryName VARCHAR2(20) PRIMARY KEY
);

INSERT INTO CATEGORY (CategoryName) VALUES ('ADULTFIC');
INSERT INTO CATEGORY (CategoryName) VALUES ('ADULTNF');
INSERT INTO CATEGORY (CategoryName) VALUES ('CHILDRENFIC');
COMMIT;

CREATE TABLE BOOKSHELF (
    Title VARCHAR2(100) PRIMARY KEY,
    Publisher VARCHAR2(20),
    CategoryName VARCHAR2(20),
    Rating VARCHAR2(2),
    CONSTRAINT CATFK FOREIGN KEY (CategoryName) REFERENCES CATEGORY(CategoryName)
);

INSERT INTO BOOKSHELF (Title, Publisher, CategoryName, Rating) VALUES ('The Shipping News', 'Picador', 'ADULTFIC', '5');
INSERT INTO BOOKSHELF (Title, Publisher, CategoryName, Rating) VALUES ('West With The Night', 'Vintage', 'ADULTNF', '4');
INSERT INTO BOOKSHELF (Title, Publisher, CategoryName, Rating) VALUES ('Charlotte''s Web', 'Harper', 'CHILDRENFIC', '5');
COMMIT;

DECODE can now be used to show whole category names:

SELECT DISTINCT
    DECODE(CategoryName,
        'ADULTFIC', 'Adult Fiction',
        'ADULTNF', 'Adult Nonfiction',
        'CHILDRENFIC', 'Children Fiction',
        CategoryName -- else condition: returns original if no match
    ) AS FullCategoryName
FROM BOOKSHELF;

Output:

FULLCATEGORYNAME
--------------------
Adult Fiction
Adult Nonfiction
Children Fiction

This is a clear method for mapping codes to descriptions, and it works particularly well with static lists.

Nested DECODE for Complex Logic

DECODE functions can be nested within one another to create more complex conditional logic, such as late fees. Frequently used in conjunction with the SIGN function, this is especially helpful for situations requiring numerous levels of “if-then-else” evaluation or for performing numerical comparisons.

Let’s compute late fees using a BOOKSHELF_CHECKOUT table:

CREATE TABLE BOOKSHELF_CHECKOUT (
    Name VARCHAR2(50),
    Title VARCHAR2(100),
    CheckoutDate DATE,
    ReturnedDate DATE
);

INSERT INTO BOOKSHELF_CHECKOUT (Name, Title, CheckoutDate, ReturnedDate) VALUES ('MAN ROLAND BRANDT', 'The Discoverers', DATE '2002-02-02', DATE '2002-03-01'); -- 27 days, non-ADULT
INSERT INTO BOOKSHELF_CHECKOUT (Name, Title, CheckoutDate, ReturnedDate) VALUES ('MAN ROLAND BRANDT', 'The Shipping News', DATE '2002-01-12', DATE '2002-03-12'); -- 59 days, ADULTFIC
INSERT INTO BOOKSHELF_CHECKOUT (Name, Title, CheckoutDate, ReturnedDate) VALUES ('EMILY TALBOT', 'West With The Night', DATE '2002-01-05', DATE '2002-03-01'); -- 55 days, ADULTNF
COMMIT;

Proceed to compute late fines using the nested DECODE:

• The typical late fee is $0.20 each day beyond 14 days.
• Adult novels were permitted for 21 days before being charged $0.30 per day.
• Books of adult fiction: 20 days, after which they cost $0.60 per day.
• Late fees of $4.00 or less are not collected (return 0).

SELECT
    BC.Name,
    BC.Title,
    BC.ReturnedDate,
    (BC.ReturnedDate - BC.CheckoutDate) AS DaysOut,
    DECODE(SUBSTR(B.CategoryName, 1, 5), 'ADULT',
        (BC.ReturnedDate - BC.CheckoutDate - 21),
        (BC.ReturnedDate - BC.CheckoutDate - 14)
    ) AS DaysLate,
    DECODE(SIGN(
        DECODE(SUBSTR(B.CategoryName, 1, 5), 'ADULT',
            DECODE(SUBSTR(B.CategoryName, 6, 3), 'FIC',
                (BC.ReturnedDate - BC.CheckoutDate - 21) * 0.60, -- Adult Fiction
                (BC.ReturnedDate - BC.CheckoutDate - 21) * 0.30  -- Other Adult
            ),
            (BC.ReturnedDate - BC.CheckoutDate - 14) * 0.20     -- Non-Adult
        ) - 4 -- Subtract 4 to check if fee is > $4
    ),
    1, -- If result of subtraction is positive (i.e., fee > $4)
        DECODE(SUBSTR(B.CategoryName, 1, 5), 'ADULT',
            DECODE(SUBSTR(B.CategoryName, 6, 3), 'FIC',
                (BC.ReturnedDate - BC.CheckoutDate - 21) * 0.60,
                (BC.ReturnedDate - BC.CheckoutDate - 21) * 0.30
            ),
            (BC.ReturnedDate - BC.CheckoutDate - 14) * 0.20
        ),
    0  -- If result of subtraction is 0 or negative (i.e., fee <= $4)
    ) AS LateFee
FROM BOOKSHELF_CHECKOUT BC
JOIN BOOKSHELF B ON BC.Title = B.Title
WHERE
    (BC.ReturnedDate - BC.CheckoutDate) >
    DECODE(SUBSTR(B.CategoryName, 1, 5), 'ADULT', 21, 14)
ORDER BY BC.Name, BC.CheckoutDate;

Output:

NAME              TITLE                 RETURNEDD    DAYSOUT   DAYSLATE    LATEFEE
----------------- -------------------- --------- ---------- ---------- ----------
EMILY TALBOT      West With The Night  01-MAR-02         55         34       10.2
MAN ROLAND BRANDT The Discoverers      01-MAR-02         27         13          0
MAN ROLAND BRANDT The Shipping News    12-MAR-02         59         38       22.8

This illustration demonstrates the intricacy that can result from using nested DECODE functions as well as the necessity of using SIGN for range comparisons, which are required because DECODE is primarily used for equality checks.

CASE Expressions

Implementing “if-then-else” logic directly into SQL statements is made more structured and frequently readable with the use of the CASE expression, which is a component of the ANSI/ISO standard SQL syntax. CASE expressions have been supported by Oracle Database since Oracle8i for SQL and Oracle9i Release 1 for PL/SQL. CASE expressions evaluate conditions and return a single value, as opposed to CASE statements that run PL/SQL statement sequences.

Two primary types of CASE expressions exist:

Expression in Simple CASE

This form compares a single expression to a series of specific values. CASE expression WHEN result1 THEN result_expression1 WHEN result2 THEN result_expression2 ... ELSE result_expression_else END;

1. The expression is evaluated once.
2. Its result is then compared to result1, result2, and so on.
3. The result_expression associated with the first matching result is returned.
4. If no match is found and an ELSE clause is present, result_expression_else is returned.
5. If no match is found and there is no ELSE clause, the CASE expression returns NULL.

CASE Expression Search

This form evaluates a series of independent Boolean conditions. CASE WHEN condition1 THEN result_expression1 WHEN condition2 THEN result_expression2 ... ELSE result_expression_else END;

• Each condition (e.g., condition1, condition2) is a Boolean expression that evaluates to TRUE, FALSE, or UNKNOWN.
• The conditions are evaluated in the order they appear, from left to right.
• The result_expression associated with the first condition that evaluates to TRUE is returned. This is known as short-circuit evaluation.
• If no condition is TRUE and an ELSE clause is present, result_expression_else is returned.
• If no condition is TRUE and there is no ELSE clause, the CASE expression returns NULL.

When it comes to sophisticated logic, CASE expressions are typically easier to read and maintain than DECODE, especially when working with range-based conditions or multiple Boolean checks. The END keyword is used to end them.

Examples with CASE Expressions

The identical BOOKSHELF and BOOKSHELF_CHECKOUT tables will be used for the demonstration.

Basic Value Substitution (equivalent to DECODE)

Basic Value Substitution, which is the same as DECODE, can be accomplished using a straightforward CASE expression and the BOOKSHELF table.

SELECT DISTINCT
    CASE CategoryName
        WHEN 'ADULTFIC' THEN 'Adult Fiction'
        WHEN 'ADULTNF' THEN 'Adult Nonfiction'
        WHEN 'CHILDRENFIC' THEN 'Children Fiction'
        ELSE CategoryName -- if no match, return original CategoryName
    END AS FullCategoryName
FROM BOOKSHELF;

Output:

FULLCATEGORYNAME
--------------------
Adult Fiction
Adult Nonfiction
Children Fiction

This shows that CASE can be applied similarly to DECODE for basic equality checks.

Nested CASE for Complex Logic

Nested CASE expressions can be used to implement the late charge computation with all of its rules. Compared to deeply nested DECODE calls, this frequently produces more verbose but more understandable logic, particularly when conditions get complex.

Using CASE expressions, the late fee logic can be implemented as follows:

• Standard late fee: $0.20/day after 14 days.
• Adult category books: Allowed 21 days, then $0.30/day.
• Adult Fiction books: Allowed 21 days, then $0.60/day.
• Late fees less than or equal to $4.00 are not collected (return 0).

SELECT
    BC.Name,
    BC.Title,
    BC.ReturnedDate,
    (BC.ReturnedDate - BC.CheckoutDate) AS DaysOut,
    CASE SUBSTR(B.CategoryName, 1, 5)
        WHEN 'ADULT' THEN (BC.ReturnedDate - BC.CheckoutDate - 21)
        ELSE (BC.ReturnedDate - BC.CheckoutDate - 14)
    END AS DaysLate,
    CASE
        WHEN (CASE SUBSTR(B.CategoryName, 1, 5)
                WHEN 'ADULT' THEN
                    CASE SUBSTR(B.CategoryName, 6, 3)
                        WHEN 'FIC' THEN (BC.ReturnedDate - BC.CheckoutDate - 21) * 0.60
                        ELSE (BC.ReturnedDate - BC.CheckoutDate - 21) * 0.30
                    END
                ELSE (BC.ReturnedDate - BC.CheckoutDate - 14) * 0.20
              END) < 4
        THEN 0
        ELSE
            CASE SUBSTR(B.CategoryName, 1, 5)
                WHEN 'ADULT' THEN
                    CASE SUBSTR(B.CategoryName, 6, 3)
                        WHEN 'FIC' THEN (BC.ReturnedDate - BC.CheckoutDate - 21) * 0.60
                        ELSE (BC.ReturnedDate - BC.CheckoutDate - 21) * 0.30
                    END
                ELSE (BC.ReturnedDate - BC.CheckoutDate - 14) * 0.20
            END
    END AS LateFee
FROM BOOKSHELF_CHECKOUT BC
JOIN BOOKSHELF B ON BC.Title = B.Title
WHERE
    (BC.ReturnedDate - BC.CheckoutDate) >
    CASE SUBSTR(B.CategoryName, 1, 5)
        WHEN 'ADULT' THEN 21
        ELSE 14
    END
ORDER BY BC.Name, BC.CheckoutDate;

Output:

NAME              TITLE                 RETURNEDD    DAYSOUT   DAYSLATE    LATEFEE
----------------- -------------------- --------- ---------- ---------- ----------
EMILY TALBOT      West With The Night  01-MAR-02         55         34      10.20
MAN ROLAND BRANDT The Discoverers      01-MAR-02         27         13       0.00
MAN ROLAND BRANDT The Shipping News    12-MAR-02         59         38      22.80

Although this CASE version is longer than its DECODE counterpart, its structure which includes distinct WHEN and THEN clauses makes the logic flow more obvious and possibly easier to maintain. Additionally, it enables comparison operators (<, >) directly in its conditions, so it does not require functions like SIGN for numeric ranges.

Analogy

Consider yourself at a delivery service, having to make a decision about how to handle parcels.

With its pre-programmed labels, DECODE functions similarly to a basic sorting machine. The machine reads the value on the address label when you feed it a package. The label changes to “Local Delivery” (then1) if it reads “Sydney” (if1). “Interstate Delivery” (then2) is displayed if “Melbourne” (if2) is displayed. In the event that no pre-specified city is found, it is sent to “International Shipment” (otherwise).

It’s quick and effective for precise matches, but if you need to determine “if the package is going to any city in Queensland and weighs more than 5kg,” the basic machine may not be able to handle the situation or may need a sophisticated set of nested checks with extra processes to handle logical or range criteria.

CASE expressions, on the other hand, are like a human logistics manager with a checklist of rules. They look at a package and can apply more complex conditions. “Is the destination within a 500km radius AND is the weight over 10kg?” (WHEN condition1 THEN result_expression1). “Or, is it an express package AND requires refrigeration?” (WHEN condition2 THEN result_expression2). If none of the specific rules apply, there’s a default “general handling” instruction (ELSE result_expression_else). This manager is more flexible, can handle complex scenarios directly, and their thought process (the logic) is easier to follow because the rules are clearly laid out.