IF()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
Returns one of two values based on a truth expression.
USAGE
IF(check, true_value, false_value)
Argument Name | Description |
|---|---|
| The boolean expression to evaluate |
| The value returned for true |
| The value returned for false |
DETAILS
IF() is a control flow function that returns one of two values based on a truth expression.
If the truth expression evaluates to true, the return value is the true_value, otherwise it is the false_value.
The truth expression is evaluated in numerical context. Thus, a string that does not begin with a number is evaluated as zero.
The truth expression can be anything that evaluates to 0 or NULL for false, and all other numerical values for true.
The return type of the result is based on a combination of the true & false values:
If either of the return values is a string or a value that evaluates to a string (such as a date), a string value is returned.
If both values are numbers and one is a floating point number, a floating point value is returned.
EXAMPLES
With Literal Values
In the following examples, IF() is called with literal values:
SELECT IF(10 > 20, 'YES', 'no') AS result;
+--------+
| result |
+--------+
| no |
+--------+
SELECT IF(5, 42, 'hmm') AS result;
+--------+
| result |
+--------+
| 42 |
+--------+
SELECT IF(NULL, 5.0, 3) AS result;
+--------+
| result |
+--------+
| 3 |
+--------+
Example Schema and Data
Some of the examples are based on the example table contacts:
CREATE TABLE contacts (
first_name VARCHAR(25),
last_name VARCHAR(25),
email VARCHAR(25),
new_contact boolean
);
INSERT INTO contacts VALUES
('John', 'Smith', 'john.smith@example.com', true),
('Jane', 'Smith', 'jane.smith@example.com', true),
('Jeff', 'Jones', 'jane.jones@example.com', false),
('Mary', 'Jones', 'mary.jones@example.com', false),
('Joe', 'Schmoe', 'joe.schmoe@example.com', NULL),
(NULL, NULL, NULL, NULL);
Per-row evaluation
In the following example, IF() returns the email column if the new_contact evaluates true, otherwise it returns the first_name column.
SELECT IF(new_contact, email, first_name)
FROM contacts;
+------------------------------------+
| IF(new_contact, email, first_name) |
+------------------------------------+
| john.smith@example.com |
| jane.smith@example.com |
| Jeff |
| Mary |
| Joe |
| NULL |
+------------------------------------+
With GROUP BY
Using IF() with GROUP BY creates a separate result for each group. In this example, GROUP BY last_name creates a separate result for each unique name:
SELECT last_name,
IF(COUNT(*) > 1, 'group', 'solo') AS the_if
FROM contacts
GROUP BY last_name
ORDER BY last_name;
+-----------+--------+
| last_name | the_if |
+-----------+--------+
| NULL | solo |
| Jones | group |
| Schmoe | solo |
| Smith | group |
+-----------+--------+