SQL If Statement

In this tutorial, you will learn how to write Conditional statements – IF and IF-ELSE.

In real-world situations, we used to do something according to the result of something we expect. Say for example, “If tomorrow is a holiday, I’ll plan for a vacation”, “If the train fare is not more than 500, I’ll go home. In the above examples, activity depends on the outcome of something. So, there is a condition that has to be tested; if the condition gets satisfied, do something. Else, execute the rest of the statements available or come out of the loop.

Basic Syntax for IF ELSE statement in SQL:

IF CONDITION
{ ......execute the statements if the test condition is true.....}
[ ELSE
{.......execute the statements if the test condition is false....}]

The usage of ‘ELSE’ is actually optional. If you want to execute something specifically if the condition being tested is FALSE, you can use ELSE. Otherwise, by default, the loop will execute the rest of the statements present in the loop if the condition being tested is not satisfied (FALSE).

Therefore, optionally you can use the ELSE condition within the IF…ELSE statement. You should not use an ELSE IF condition in the IF ELSE statement. But, you can use nested (multiple) IF ELSE statements to obtain your results. Let’s have a quick example to learn the IF ELSE statements better…

DECLARE @student_marks INT;
SET @student_marks= 95;
IF student_marks>= 40
PRINT ‘You passed the exam’;
ELSE
PRINT ‘You failed’;
GO

From the above IF ELSE statement, we are testing a condition product_price < 150 or not. If yes, it will print ,‘Hi’. If the above condition is false, it will print, ‘Hello’.

As we discussed earlier, we can use IF condition alone to write a conditional statement. The usage of ELSE is optional. Let’s see an example for a conditional statement using IF and without the use of ELSE.

DECLARE @student_marks INT;
SET @student_marks= 95;
IF student_marks>= 40
PRINT ‘You passed the exam’;
GO

In the above code, we avoided the use of ELSE since we don’t need to execute something else, if the test condition is False. Therefore, you will get printed ‘Hi’ if the test condition is TRUE.

Now, we shall learn to use Nested IF ELSE statements. As we mentioned earlier that, we could not use an ELSE IF condition inside a IF ELSE statement. Hence, we shall use Nested IF ELSE statements.

DECLARE @student_marks INT;
SET @student_marks= 95;
IF student_marks>= 90
PRINT ‘You passed the exam in Merit’;
ELSE
BEGIN
IF student_marks>=70
PRINT ‘You passed the exam in First class’;
ELSE
PRINT ‘You failed in the exam, Try again’;
END
GO

From the above code for using Nested IF-ELSE, we are testing three conditions namely:

IF student_marks>= 90
IF student_marks>= 70 and
ELSE

If the first condition becomes TRUE, the output will say that the student has passed the exam in Merit. The same will be printed as ‘‘you passed the exam in Merit’. If the second condition becomes TRUE, the output will say that the student has passed the exam in first class and the same will be printed as ‘You passed the exam in First class’. If both of these conditions were not satisfied, that will be printed as ‘You failed in the exam, try again’.

You could also notice BEGIN and END blocks in the above program. After the first test condition, BEGIN is introduced to start another conditional statement. Once the second and third test conditions were met, the END is used to end the execution of the program.

We can also use BEGIN and END, twice for the same code which is shown above. Both of these codes produce the same output.

DECLARE @student_marks INT; SET @student_marks= 95; BEGIN IF student_marks>= 90 PRINT ‘You passed the exam in Merit’; END ELSE BEGIN IF student_marks>=70 PRINT ‘You passed the exam in First class’; ELSE PRINT ‘You failed in the exam, Try again’; END

We can access the SQL tables in the conditional statements. Let’s see an example of how to access SQL tables in the IF ELSE.

DECLARE @sales_market INT;
SELECT @ sales_market = SUM(TotalOrders*PricePerUnit);
FROM [AdventureWorks2019] . [sales_market] . [SalesDetails];
IF sales_market >= 1500000
SELECT *
FROM [AdventureWorks2019] . [sales_market] . [SalesDetails];
ELSE
SELECT *
FROM [AdventureWorks2019] . [sales_market] . [SalesHeader];
GO

From the above code, we can notice two conditions:

• IF sales_market >= 1500000 and
• ELSE

If the first test condition is satisfied, i.e., IF sales_market >= 1500000, that will select records from the SQL table ‘SalesDetails’.

If the condition is not satisfied, then, it will select the records from the SQL table, ‘SalesHeader’.

We can also use BEGIN and END for the same program as per the previous example.

Till this, we have completed the tutorial. I hope you have learned about the way of using Conditional Statements in SQL.

Related Blogs

1. SQL Joins
2. Schema in SQL
3. Decode in SQL