SQL: If Else Statement & Else If Statement
SQL (Structured Query Language) is one way of medium to let user communicate and manipulate databases. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems which became part of it in 1986 and further expanded as the part of International Organization for Standardization (ISO) in 1987. SQL statements are used to perform tasks revolving around a database such as execute queries, update records, or retrieve data. There are some common relational database management systems that use SQL such as: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, and etc.
Although most database systems use SQL, majority of them also have their own inbuilt additional proprietary extensions on their system. However, the standard SQL commands such as “Select”, “Insert”, “Update”, “Delete”, “Create”, and “Drop” can be used to accomplish almost everything that one needs to do with a database. This article will provide an in-depth discussion of a different variety of command in the form of If Else statement and Else If statement.
If Else Statement
The SQL If Else statement is one of the most useful decision-making queries. The If statement will test the condition first, and depending upon the result, it will execute the statements.
When the test condition in the If statement is True, the query inside the If block will execute. Otherwise, the lines inside the Else block are executed. The following below is the syntax of the If Else statement in SQL server:
IF (Test Condition or Expression)
BEGIN
-- The condition is TRUE then these will be executed
True statements;
END
ELSE
BEGIN
-- The condition is FALSE then these will be executed
False statements;
ENDThis SQL If Else statement accepts any test condition as the argument. When the test condition or expression in the above structure is True, then True statements will execute. When the test condition is False, then the False statements will run instead. The following below is a flow chart of the If Else statement in SQL server for a better understanding:
When the test condition in the above structure is True, then Statement 1 will execute, followed by Statement N. When the test condition is False, then Statement 2 will run instead, followed by Statement N.
Else If Statement
The SQL Else If statement is useful to check multiple conditions at once. SQL Else If statement is an extension to the SQL If Else statement on the previous section.
If Else statement only executes the statements when the given condition is either True or False. However in real life practice, there is always a possibility of having more than two conditions. In these situations, SQL Else If statement may be used to deal with them. The following below is the syntax of the Else If statement in SQL server:
IF (Expression 1)
BEGIN
-- The condition is TRUE then Statement 1 will be executed
Statement 1;
END
ELSE IF (Expression 2)
BEGIN
-- The condition is FALSE for Statement 1 but TRUE for Statement 2 then Statement 2 will be executed
Statement 2;
END
ELSE
BEGIN
Default Statement;
ENDThe SQL Else If statement handles multiple statements effectively by executing them sequentially. It will check for the first condition. When the condition is True, then it will execute the statements present in that block. When the condition is False, then it will check the next Else If condition and so forth until a True condition is met.
In SQL Else If statement, there will be some scenarios where there are more than one statement that is True, for example:
x = 47, y = 15
Condition 1: x > 2y — TRUE
Condition 2: x+y != 2x — TRUEIn these scenarios, statements under the Condition 1 are executed. Because Else If statements only execute if their previous If or Else If statement fails. The following below is a flow chart of the Else If statement in SQL server for a better understanding:
When condition 1 is True, then Statement 1 will execute, followed by Statement N. When condition 1 is False, then it will fall into condition 2. When condition 2 is True, then Statement 2 will execute, followed by Statement N. When condition 2 is False, then it will fall into condition 3. When condition 3 is True, then Statement 3 will execute, followed by Statement N. When condition 3 is False, then it will fall into condition N and so forth until a True condition is met. If everything is False, then Default Statement will be presented instead, followed by Statement N.
Examples
Here are some examples on SQL If Else statement and Else If statement followed by their output.
First example shows a simple implementation on If Else statement and Else If statement. World database is used for this experiment. The objective is to show all available countries alongside their GNP value while grouping them into four different categories which are High GNP, Upper-Middle GNP, Lower-Middle GNP, and Low GNP. The output shows a table with name of all countries, their respective GNP, and GNP category as the column sections.
Second example shows a bit more complex scenario for If Else statement and Else If statement implementation. Similar world database is still used here. The objective is to show only top 10 countries with the highest GNP value alongside their respective GNP category and capital cities as well. From country, both Name and GNP are selected. GNP value will be grouped into four different categories which are High GNP, Upper-Middle GNP, Lower-Middle GNP, and Low GNP. The additional complexity is to apply Join command from Capital in country and ID in city in order to display capital city in the table. Order By command is executed which GNP acts as the filter in descending order. Limit 10 command tops it off to show only the first 10 countries after being sorted in descending order. The output shows a table with name of top 10 countries with the highest GNP, their respective GNP, GNP category, and each capital city as the column sections.
I hope you find this article beneficial and informative. Thank you for taking your time and have a great day!
