My Web Training

📖 MySQL Conditionals

Conditional Statements are used to evaluate an expression and return a value to determine the program's flow.

CASE Statement

Returns a value based on conditions. Simple CASE statement: returns different values based on the value of a single field.

Format

CASE input_expression
	WHEN value-1 THEN result-1
	[WHEN value-2 THEN result-2] . . .
	[ELSE result-default]
END

• CASE and 1 WHEN clause are required.
• Can have multiple WHEN clauses with different values and results.
• ELSE is optional. If present, assigns a value if none of the WHEN clauses are matched.

Example

SELECT 
    customer_id,
    customer_state,
    CASE customer_state
        WHEN 'OH' THEN .0575
        WHEN 'CA' THEN .0725
        WHEN 'NY' THEN .04
        ELSE 0
    END AS tax_rate
FROM
    customers;
-- Returns tax rate for OH, CA and NY
-- All others are taxed at 0

Search CASE statement

Used for more complicated conditions including non-equal conditions (>, <, >=, <=) or multiple columns.

Format

CASE
	WHEN condition > 1 THEN result-1
	[WHEN condition <= 20 THEN result-2] . . .
	[ELSE result-default]
END

Example

SELECT 
    invoice_id,
    invoice_total,
    CASE
        WHEN invoice_total < 50.00 THEN 15.00
        WHEN credit_total > 100.00 THEN 35.00
        ELSE 'Free Shipping'
    END AS ship_amount
FROM
    invoices;
-- Orders under $50 get shipping for $15
-- Orders with credit over $100 get shipping for $35
-- Orders over $50 with credit under $100 get free shipping

You can also use lists of values or use keywords in your conditional.

SELECT 
    customer_id,
    customer_state,
    CASE
        WHEN customer_state IN ('OH', 'IN', 'IL') THEN 'Midwest'
        WHEN customer_state BETWEEN 'AK' AND 'GA' THEN 'Preferred'
        ELSE 'Retail'
    END AS Category
FROM
    customers;

Example Result Set

IF, IFNULL, COALESCE Statements

IF

Conditional statements return one value for true and one for false.

Format

IF(test_expression, true_result, false_result)

Example

SELECT 
    vendor_id,
    vendor_name,
    vendor_contact_first_name,
    IF(vendor_state = 'TX',
        'In state',
        'Out of state') AS 'Tax rate'
FROM
    vendors;
-- Value will be 'In state' if the value of state is 'TX', otherwise 'Out of state'

IFNULL

Same as if, but only tests if null.

Format

IFNULL(field_name, true_result, false_result)

Example

SELECT 
    vendor_id,
    vendor_name,
    IFNULL(vendor_phone, 'Not Provided') AS Phone
FROM
    vendors;
-- Value will be 'Not Provided' if vendor_phone has a null value. 
-- Otherwise the vendor_phone will be displayed.

COALESCE

Caolesce statement returns the first non-null value in a list.

Format

COALESCE(
first_choice, second_choice[, third_choice, . . .]
)

Example

SELECT 
    vendor_id,
    vendor_name,
    COALESCE(vendor_address1, vendor_address2, 'No Address Provided') AS 'Address'
FROM
    vendors;
-- Value will be address1 if it exists. If not, it will be address2 unless it is also null. 
-- If both are null, then the value will be 'No Address Provided'.