What is IF ELSE Statement in SQL?
If-else is known as a conditional statement. Similarly in SQL, it is known as the conditional SQL statement. if & else control structure used mostly in the procedures & methods. When If the condition used in the SQL in that case execution takes place as shown in the following expression:
Syntax:
IF(condition, true, false)If condition expression evaluates to true then true case value is returned by the expression otherwise false case value will be returned.
IF…ELSE statement is also known as the control flow statement. It controls the flow of execution. Use of the IF… ELSE statements can skip SQL statements on a certain condition. The condition should be in boolean expression, which means condition expression results must be either true or false. Any T-SQL (Transact-SQL) can be placed in the conditional IF…ELSE statement.
Syntax Applying in IF…ELSE Statement
In the below-given statement IF conditional statement evaluates to true then SQL statement under IF block is executed otherwise ELSE block is executed.
Syntax #1:
IF <conditional_statement>
BEGIN
{ SQL Statement }
END
END IF;Syntax #2:
IF <conditional_statement>
BEGIN
{ SQL Statement }
END
ELSE
BEGIN
{ SQL Statement }
END
END IF;Flow Diagram
Below given diagram shows how the if-else statement plays a role in executing the statement based on the expression evaluation:
Above given flow diagram says the following things:
- If Condition Evaluates to true in that case true case statement 1 block gets executed.
- If Condition Evaluates to false in that case false case statement 2 block get executed.
After execution of the IF…ELSE statement then other unconditional statement execution remains to continue.
IF…ELSE Statement in SQL
In MySQL, IF…ELSE statement is a conditional statement. In the above-given flow diagram we can see how different statements executed & skipped conditionally:
- The condition should be in boolean expression & must be evaluated in true/false.
- IF…ELSE conditionally handle the single or multiple T-SQL statements in its block.
- IF…ELSE block of statement starts from BEGIN statement & ends with an END statement.
- ELSE statement may be used or not, It’s optional.
- BEGIN & END statement helps SQL Server to identify the start & end of the SQL statement that needs to be executed. Also, it separates the T-SQL statement conditionally.
Example #1
In this example, we will see how IF…ELSE condition can be used in the SQL statement. Here placement of the IF…ELSE statement will teach us how it can be placed effectively with the SQL.
The above given table having the record on the foreign key `category_id`. In this table, a procedure is created where IF…ELSE statement that will be called by passing an argument as a category_id.
Code:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `__searchItemsByCategoryId`(IN `categoryId` INT(11))
BEGIN
IF (categoryId <= 8) THEN
SELECT * FROM `oms_items` WHERE `category_id` <= categoryId;
ELSE
SELECT * FROM `oms_items` WHERE `category_id` > categoryId LIMIT 10;
END IF;
END$$
DELIMITER ;Above given SQL will create a procedure to extract the record based on the category_id. When this procedure will be called by the application by passing an input id as a category_id.
Calling of the procedure can be done in the following way:
Code:
CALL __searchItemsByCategoryId(8);Result of the procedure call is displayed below:
Example #2
In the below-given example, A procedure “__searchItemsByName” is created to find the product by the name as entered at the time of the procedure call. Also if the provided name to the procedure is “null” in that case procedure will fetch all the products.
Code:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `__searchItemsByName`(IN `itemName` TEXT)
BEGIN
IF (itemName != '') THEN
SELECT * FROM `oms_items` WHERE `item_description` LIKE itemName;
ELSE
SELECT * FROM `oms_items`;
END IF;
END$$
DELIMITERAbove given query can be executed through the following way
Code:
CALL __searchItemsByName(‘Capacitor’);In the above-given procedure call, the parameter passed to the procedure will display those records which will have the ‘Capacitor’ in the ‘item_description’ column as shown in the below screenshot.
Now if the calling of the above procedure is done without the passing of any values, in that case, all the records will be displayed.
Example #3
In the below-given table, If `is_featured` needs to be changed to inactive state then we can see how IF can work in the SQL statement.
Code:
UPDATE `oms_items` SET `is_featured` = IF(`is_featured` = 1, 0, 1) WHERE `item_no`= 10;After the execution of the above query, the status of the `is_featured` column switch to 0. If `if_featured` column value for the corresponding record is 0 then it will change to 1.
Example #4
In the given table, the following query efficiently displaying the category wise count of the items. In this statement IF queries work like as a ternary statement
Code:
SELECT COUNT(IF(category_id = 7,1,NULL)) motor_parts,
COUNT(IF(category_id = 8,1,NULL)) circuit_items,
COUNT(IF(category_id = 9,1,NULL)) fitting_goods,
COUNT(IF(category_id = 10,1,NULL)) board_parts
FROM oms_items;Output:
The above output can be achieved by using the “group_by” method in the SQL statement.
Conclusion
IF…ELSE is from one of the conditions in SQL which reduces query & makes DML operation faster & efficient. If the condition matches then the statement under the scope of IF block will be executed otherwise statement under ELSE block will be executed. Use of IF, IF…ELSE, IF- ELSEIF-ELSE these statements work differently according to the use cases. ELSE-IF & ELSE is optional it may be used or not It’s totally dependent on the situation.
Recommended Articles
This is a guide to IF-ELSE Statement in SQL. Here we discuss Syntax Applying in IF…ELSE Statement with a flow diagram and examples of how different statements executed & skipped condition. You can also go through our other related articles to learn more –
