1. Introduction
Structured Query Language (SQL) supports the IF statement like any other programming language. The IF statement tests a boolean expression. If the expression is true, then the block of statements associated with the IF statement is executed. It can also be used with an ELSE statement;
In this example, I will demonstrate how to:
- Use the IF function in a Select statement.
- Create a stored procedure with nested IF statements.
2. Technologies Used
The example code in this article was built and run using:
- MySQL
- SQL
3. IF Function
The IF() function takes three arguments:
- The first is a boolean expression which evaluates to either true or false.
- The second argument is the value to be returned when the expression is true.
- The third argument is the value returned when the expression is false.
IF Function Syntax
IF(expression, true_value, false_value);
In these two examples, the first example evaluates to true and returns the second value. The second example evaluates to false and returns the third value.
IF in Select
mysql> SELECT IF(500<1000, "500 is less than 1000", "NA") // +---------------------------------------------+ | IF(500<1000, "500 is less than 1000", "NA") | +---------------------------------------------+ | 500 is less than 1000 | +---------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT IF(500>1000, "", "500 is less than 1000") // +-------------------------------------------+ | IF(500>1000, "", "500 is less than 1000") | +-------------------------------------------+ | 500 is less than 1000 | +-------------------------------------------+ 1 row in set (0.00 sec) mysql>
4. SQL IF Statement
The SQL IF statement is very similar to the IF function, as it branches out the execution statements.
IF Statement Syntax
IF (Expression which evaluates to either true OR false )
BEGIN
-- If the condition is TRUE
END
ELSE
BEGIN
-- If the condition is False
END
END IFNote: the else branch is optional.
4.1 Set up Simple Data
In this step, I will create a table named with demo_table and insert three records.
Create a Table with Data
create table demo_table(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
direction INT NOT NULL,
PRIMARY KEY ( id )
);
insert into demo_table(name, direction) values('mary',1);
insert into demo_table(name, direction) values('shan',2);
insert into demo_table(name, direction) values('zheng',3);4.2 Create a Stored Procedure
In this step, I will create a stored procedure called demoIf which takes a single input argument type.
If the input type is “P”, then executes a Select statement which prints out the data from demo_table and translates the direction column. If the input type is “A”, then prints out “Hello World!“. Otherwise, it prints out “nothing to do!“.
Create demoIf Stored Procedure
DELIMITER //
DROP PROCEDURE IF EXISTS demoIf; //
SHOW PROCEDURE STATUS WHERE Db = 'mysqlDemo'; //
CREATE PROCEDURE demoIf(
IN type VARCHAR(
1) )
BEGIN
IF type = "P" THEN
SELECT direction,
IF(direction=1,"Sync Change From Remedy to Snow", IF(direction=2,"Sync Incident From Remedy to Snow", "Sync Change From Snow to Remedy")) as DirectionMsg
FROM demo_table;
ELSE
IF type = "A" THEN
SELECT "Hello World!" as DirectionMsg;
ELSE
SELECT "nothing to do!" as DirectionMsg;
END IF;
END IF;
END //
Line 12: I add an IF function in a Select statement.
4.2 Call the Stored Procedure
In this step, I will call the demoIf stored procedure with three different values: N, A, and P and capture the outputs.
Execute demoIf Stored Procedure
mysql> mysql> CALL demoIf('N');//
+----------------+
| DirectionMsg |
+----------------+
| nothing to do! |
+----------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL demoIf('A');//
+--------------+
| DirectionMsg |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL demoIf('P');//
+-----------+-----------------------------------+
| direction | DirectionMsg |
+-----------+-----------------------------------+
| 1 | Sync Change From Remedy to Snow |
| 2 | Sync Incident From Remedy to Snow |
| 3 | Sync Change From Snow to Remedy |
+-----------+-----------------------------------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>5. Summary
In this example, I demonstrated how to use the IF function and IF statement in the MySQL database. The Oracle PL/SQL IF statement has the same syntax too. Click here for more details.
6. Download the Source Code
You can download the full source code of this example here: SQL IF Statement Example
As far as I am aware, SQL is not a programming language and it does not support IF statement.