Updated March 8, 2023
Introduction to SQL if then else
When dealing with conditional statements and executing code or statements in SQL based on the fulfillment of certain conditions, we can use conditional statements that are available in SQL. One such basic and crucial functionality is that of if then else statement in which we check for the expression or condition that includes variable comparison, retrieved data from the query comparison, or any literal value or parameter variable value checking that is retrieved from functions or stored procedure parameters.
If the condition or expression evaluates to true or value that can be deduced to a true value, then the statements mentioned after if that is between BEGIN and END clauses or after THEN clause is executed and if condition or expression evaluates to false or false-like value then the statements that are mentioned after ELSE clause are executed. In this article, we will learn about some of the implementations of the if then else statements in SQL that help us to execute conditional behavior in SQL with the help of some examples.
Syntax
The syntax of if then else statements are as shown below –
IF condition or expression evaluating to a boolean value
BEGIN
{ statements to be executed if the condition evaluates to true }
END
ELSE
BEGIN
{ statements to be executed if the condition evaluates to false }
ENDThe syntax consists of keywords like IF, BEGIN, END, and ELSE. Other than this, there is a necessity to mention the condition or expression after the IF keyword that ultimately evaluates to a boolean value. Further, whichever statements are to be executed if the boolean expression or condition evaluates to true, then they are to be mentioned between BEGIN and END keywords that are specified after IF keyword and expression and also mention the statements are to be executed if the boolean expression or condition evaluates to false then they are to be mentioned between BEGIN and END keywords that are specified after THEN keyword.
Examples
Let us consider an existing table named educba_articles that exists in our educba named database. We will check the contents and structure of the table by executing the following query statement –
SELECT * FROM educba_articles;The output of the execution of the above query statement is as follows –
Now, we have to check the sum of the total cost of the articles that have been submitted. For this, we will calculate the cost of a single article by calculating the product of rate and pages. Further, we will use the SUM() function to calculate the total cost. Following will be our query statement –
SELECT
SUM(` rate` * ` pages`)
FROM
educba_articles
WHERE
` status` = "Submitted";The output of the execution of the above query statement is as follows –
Now, we will use the IF and ELSE keywords as mentioned in the above syntax that is specified in the following manner to display the appropriate outputs –
BEGIN
DECLARE @totalCost INT;
SELECT
@totalCost = SUM(` rate` * ` pages`)
FROM
educba_articles
WHERE
` status` = "Submitted";
SELECT @totalCost;
IF @totalCost > 10000
BEGIN
PRINT 'The submitted articles seem to be great in terms of quality and quantity!';
END
ELSE
BEGIN
PRINT 'The submission rate needs to be increased!';
END
ENDThe output of the execution of the above query statement follows as the total cost does not exceed 10000, the flow executes the statement mentioned in else block printing “The submission rate needs to be increased!” message –
Now, suppose that we execute the following statements to print the messages depending on the pending articles that are not yet submitted –
BEGIN
DECLARE @totalCost INT;
SELECT
@totalCost = SUM(` rate` * ` pages`)
FROM
educba_articles
WHERE
` status` = "Pending";
SELECT @totalCost;
IF @totalCost > 10000
BEGIN
PRINT 'The writers need to speed up with their work!';
END
ELSE
BEGIN
PRINT 'More topics and work should be allocated to the writers!';
END
ENDThe output of the execution of the above query statement is as follows –
The expression mentioned in the IF keyword evaluates to true as the total cost of pending articles exceeds the 10000 amount, and then the message mentioned in the if block gets printed in the output. Let us execute the following query statement to confirm the total amount for pending articles and cross-check our working of IF then ELSE statements.
SELECT
SUM(` rate` * ` pages`)
FROM
educba_articles
WHERE
` status` = "Pending";The output of the execution of the above query statement is as follows –
Using CASE statement
We can alternatively use the CASE statement to execute the same functionality as that of IF-ELSE keywords shown above in a single query statement compacting the implementation in a single query. Consider the same example to print the message ‘The writers need to speed up with their work!’ for pending costs exceeding 10000 amount. We will use the following query statement to implement case statement to implement if else functionality in SQL –
SELECT
CASE
WHEN SUM(` rate` * ` pages`) > 10000
THEN 'The writers need to speed up with their work!'
ELSE 'More topics and work should be allocated to the writers!'
END AS output
FROM
educba_articles
WHERE ` status` = "Pending" ;The output of the execution of the above query statement follows that is the same as that of if-else keyword usage –
Let us also execute the case statement to implement our first example for submitted articles –
SELECT
CASE
WHEN SUM(` rate` * ` pages`) > 10000
THEN 'The submitted articles seem to be great in terms of quality and quantity!'
ELSE 'The submission rate needs to be increased!'
END AS output
FROM
educba_articles
WHERE ` status` = "Submitted" ;The output of the execution of the above query statement is as follows –
Conclusion – SQL if then else
We can use IF and ELSE keywords in SQL to perform conditional execution of the statements depending on the condition evaluating to a boolean value. Alternatively, we can use the CASE statement to implement if then else functionality in SQL.
Recommended Articles
We hope that this EDUCBA information on “SQL if then else” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
