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

Advertisement

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

student_id studentname admissionno admisssiondate enrollmentno date_of_birth email city class_id
101 reema 10001 02-02-2000 e15200002 02-03-1990 reema@gmail.com   2
102 kriya 10002 04-05-2001 e16200003 04-08-1991 kriya@gmail.com   1
103 meena 10003 06-05-1999 e15200004 02-09-1989   Vadodara 3
104 carlin 2001 04-01-1998 e14200001 04-04-1989 carlin@gmail.com Vapi 1
105 dhiren 2002 02-02-1997 e13400002 02-03-1987 dhiru@gmail.com Surat 2
106 hiren 2003 01-01-1997 e13400001 03-03-1987 hiren@gmail.com   2
107 mahir 10004 06-09-2000 e15200003 07-09-1990   Vapi 3
108 nishi 2004 02-04-2001 e16200001 03-02-1991 nishi@gmail.com Vadodara 1

Result Table

Advertisement
result_id student_id examname examdate subjectid obtainmark totalmarks percentage grade status
3001 101 sem1 07-08-2001 1 80 100 80 A+ pass
3002 101 sem1 08-08-2001 2 76 100 76 A+ pass
3003 102 sem3 05-05-2000 3 67 100 67 A pass
3004 102 sem3 06-05-2000 4 89 100 89 A+ pass
3005 102 sem3 07-05-2000 5 90 100 90 A+ pass
3006 103 sem5 08-09-1998 6 55 100 55 B pass
3007 105 sem3 09-09-2001 2 78 100 78 A+ pass

 

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:

SQL If Statement Tutorial [Practical Examples]

 

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:

SQL If Statement Tutorial [Practical Examples]

 

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

Advertisement

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:

SQL If Statement Tutorial [Practical Examples]

 

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:

SQL If Statement Tutorial [Practical Examples]

 

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

Advertisement
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:

SQL If Statement Tutorial [Practical Examples]

 

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:

SQL If Statement Tutorial [Practical Examples]

 

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:

Advertisement

SQL If Statement Tutorial [Practical Examples]

 

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

 

Didn't find what you were looking for? Perform a quick search across GoLinuxCloud

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can either use the comments section or contact me form.

Thank You for your support!!

Leave a Comment

X