SQL If Statement Tutorial [Practical Examples]

Overview of SQL If Statement

SQL If Statement is a conditional statement used to execution flow of control, SQL If statement allows us to perform different actions based on conditions defined within the statement, if the condition is satisfied then the block of statements within will be executed, otherwise optimal Else block will be executed,

The IF statement is logically equivalent to a CASE statement with a searched-case-statement-when clause

The IF statement supports the use of optional ELSE IF clauses and a default ELSE clause. An END IF clause is required to indicate the end of the statement

 

SQL If Statement Syntax

IF Boolean_expression   
   BEGIN
{ sql_statement | statement_block }
  END 
[ ELSE   
    BEGIN
{ sql_statement | statement_block }
  END ]

Here,

• Boolean_expression|condition : It is a condition expression that returns TRUE or FALSE. If the Boolean expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses
• { sql_statement | statement_block }: It is any Transact-SQL statement or statement grouping as defined by using a statement block. Unless a statement block is used, the IF or ELSE condition can affect the performance of only one Transact-SQL statement

To define a statement block, use the control-of-flow keywords BEGIN and END

 

Key Points of SQL If Statement

• If the Boolean_expressionevaluates to TRUE then the statement_block in the ..END block is executed. Otherwise, the statement_block is skipped and the control of the program is passed to the statement after the END keyword
• An IF...ELSE construct can be used in batches, in stored procedures, and in ad hoc queries. When this construct is used in a stored procedure, it is frequently used to test for the existence of some parameter.
• IF tests can be nested after another IF or following an ELSE. The limit to the number of nested levels depends on available memory

 

SQL If Statement Examples

Consider student result management system database with two tables student and result for practical examples of SQL If Statement

Student Table

Result Table

 

Example-1: SQL If Statement with Numeric constant value in the condition

Write SQL query to compare constant value 5 with numeric value 10 and print the message of equal or not equal value using if statement

IF (5=10)  
  BEGIN
     select 'values is not equal' As 'Output'
  END 
ELSE   
  BEGIN
     select ' values is equal' as 'Output'
  END

In this query, SQL If the conditional statement is used to compare two constant values, if both values are not equal then the condition will be satisfied and if block statement ‘ values are not equal will be the result of this query

OUTPUT:

 

Example-2: SQL If Statement with Variable in condition

Write SQL Query to check enrolment number stored in a variable and print message of student name with year of admission

DECLARE @Enrollmentno INT = 2015001
IF (@Enrollmentno = 2015001)
select 'Student name is Reema and she got admission in year 2015' As Output

• In the above query, DECLARE keyword and @ is used to declare variable Enrollmentno
• If Statement is used to compare and check variable value and print output according

OUTPUT:

 

Example-3: Multiple SQL If Statement with Variable in condition

We can execute multiple if conditions and perform an action accordingly if the condition is TRUE, also we can specify else block  to print a message if the condition is FALSE

Example 3 : Write SQL Query to get student percentage and print the message according

DECLARE @Percentage INT= 75;
IF @Percentage >= 75
BEGIN
 SELECT 'Distinction Class' as 'RESULT';
END
IF @Percentage >=65 AND @Percentage <75
BEGIN
  SELECT 'First Class' as 'RESULT';
END

• In this query, SQL if the conditional statement is used to check variable @percentage value
• If the percentage variable value is greater than and equal to 75 it first if condition will be true and the output will be ‘Distinction Class’
• If the percentage variable value is greater than 65 and less than 75 then second if condition will be true and the output will be ‘First class’

OUTPUT:

 

Example-4: Multiple SQL If and Else Statements

Write SQL query to check student obtain marks and print message of grade according to if the student obtains marks is not matched with any condition the message ‘ Marks Grade is not decided please Try Again!!’

DECLARE @ObtainMark INT= 51;
IF @ObtainMark >= 90
BEGIN
 SELECT 'A+' as 'Grade';
END
IF @ObtainMark >=80 AND @ObtainMark <90
BEGIN
  SELECT 'A' as 'Grade';
END
IF @ObtainMark >=70 AND @ObtainMark <80
BEGIN
  SELECT 'B' as 'Grade';
END
ElSE
BEGIN
  SELECT 'Marks Grade is not decided please Try Again!!' as 'Grade'
END

• In the previous query, SQL If the statement is used with Else to print the grade of obtained marks
• In the first if condition variable obtain marks value is compared to greater than or equal to 90 then grade will be A+
• The second condition variable obtain marks value is checked if it is greater than 80 and less than 90 then grade will be A
• The third condition variable obtain marks value will be check if it is greater than 70 and less than 70 then grade will be B
• If all condition is FALSE then the message ‘'Marks Grade is not decided please Try Again!!' as 'Grade' will be displayed

OUTPUT:

 

Example-5: SQL If Statement with SELECT Query

In the above examples, we print a message if a condition is either TRUE or FALSE. We might want to execute Select Query if the condition is satisfied, we can specify Select Script in the SQL If block or in the Else part also

Write SQL Query to execute SQL Select query to retrieve student data if student percentage is between 80 to 90

DECLARE @Percentage INT= 89;
IF @Percentage >= 80 and @Percentage <90
BEGIN
 SELECT studentname,enrollmentno,email,city,examname,pecentage from tblstudent left join tblresult on tblstudent.student_id=tblresult.student_id where pecentage=@Percentage;
END

• In the above query, SQL If the statement is used to check the variable percentage value and execute the select query if the condition is true
• the SQL select query will return the student data whose percentage is equal to the variable @Percentage

OUTPUT:

 

Nested SQL If Statement Examples

Like other programming languages, we cannot add an ELSE IF statement within an IF ELSE condition in SQL but we can nest IF ELSE in SQL query statements to check condition within a condition

 

Example-6: Nested If Statement with a variable value

Write SQL Query to check semester wise student percentage value and print message accordingly

DECLARE @Percentage INT= 89,@Sem VARCHAR(10)='sem2'
IF @Sem='sem2'
BEGIN
IF @Percentage >= 80 and @Percentage <90
BEGIN
  SELECT 'SEM2 student percentage is  ' + CAST(@Percentage as Varchar(10)) as 'OUTPUT'; 
END
END

• In this Query, nested if is used to check two condition, if variable@sem value is equal to sem2  then the inner condition will be executed
• If Inner if condition @Percentage is true then the inner statement “SEM2 student percentage is 89” will be printed on the output screen
• as @Percentage variable type is INT to concet its value with string we need to convert it to varchar type using the CAST function

OUTPUT:

 

Example-7: Nested If Statement with SELECT Query

Write SQL query to retrieve student data with the result if the student is in sem3 and have achieved more than 70% marks

DECLARE @Percentage INT= 70,@Sem VARCHAR(10)='sem3'
IF @Sem='sem3'
BEGIN
IF @Percentage >= 70
BEGIN
  SELECT studentname,class_id,result_id,examdate,subjectid,obtainmark,totalmarks from tblstudent left join tblresult on tblstudent.student_id=tblresult.student_id where pecentage >@Percentage and examname=@Sem ; 
END
END

• In the above query, SQL nested If the statement is used to check two conditions, if outer condition @Sem=’sem3’ is TRUE then inner condition @Percentage>=70 will be check
• IF nested if the condition is satisfied then SQL Select Query will be executed and student data will be displayed

OUTPUT:

 

Summary

In this Article of SQL If Statement, first we covered an overview and key point of SQL if statement, Syntax and syntax argument has been explained, Practical examples of SQL If Statement has been discussed with variable value in condition, constant value has been compared, SQL If with Select query to executed and at the end of the article Nested If Statement is explained with examples

 

References

Getting started with SQL

 

Further Reading

SQL IF...ELSE