SQL IF THEN : Learn the Various Forms the IF-THEN Statement

SQL stands for Structured Query Language. It is a powerful database computer language which was introduced in 1974. SQL is specifically designed to work with relational databases. All relational database systems such as Oracle, MySQL, MS SQL Server and others employ this standard database language. SQL is used to create, store, retrieve, change and delete data in relational database management systems. PL/SQL is the procedural extension language for SQL and provides many data types and useful programming structures. Users can create functions and procedures using database objects thanks to PL/SQL. Other important features include exception handling and support for Object-Oriented Programming. Prerequisites for learning PL/SQL include understanding SQL and the basics of programming.

Here we walk you through the PL/SQL IF THEN conditional construct.  If you’re new to SQL, you can first take this introductory course on SQL.

Conditional Structure – IF THEN

The IF… THEN construct is a part of PL/SQL. It falls into the category of conditional statements. This means multiple actions can be taken based on defined or logic based decisions. There are three variants of this conditional construct. They are as follows

1. IF…THEN
2. IF…THEN…ELSE
3. IF…THEN…ELSIF…ENDIF

First variant of IF statement: Simple IF THEN

This is a single level conditional statement. This conditional structure is commonly used to manipulate the data in the tables from the command prompt.

The basic syntax of IF THEN statement is as follows

IF ( condition ) THEN
  statement
END IF;

Note that the conditionals, and other statements, are written in all caps, in SQL. With the IF statement, we first check a condition. If it evaluates to true, the statement associated with THEN is executed. Otherwise, program control goes to the next statement of the program.  Take a look at the following example.

Example 1: Program to check the value of a number

DECLARE
   num INTEGER(2) := 10;
BEGIN
   IF ( num = 10 ) THEN
      DBMS_OUTPUT.PUT_LINE('The condition is true');
   END IF;
END;
/

There are 2 sections of this program.

1. DECLARE SECTION – In this section variables are declared.
2. BEGIN and END  block- The executable statements are placed within the BEGIN and END keywords. This is a mandatory section.

Note the / at the end is needed to execute the program from the command prompt. Learn how to write your own SQL programs with this course.

Example 2: Using IF THEN statement with EXISTS condition to delete records from a table

Here we work with an existing table called customer. One of its fields is called ID.

IF EXISTS(SELECT 1
   FROM customer
   WHERE ID = 100 )
THEN
    DELETE FROM customer WHERE ID = 100
 End If;
End
/

The EXISTS condition used here is a part of SQL. It accepts as parameter a subquery. Only if the subquery returns at least one row does this condition be true. In this example, the IF …THEN statement evaluates whether a record exists in the customer table. If the condition is true, then the program proceeds to delete the relevant records from the table. You can learn more about using SQL databases with this course.

Example 3: Using IF THEN statement with NOT EXISTS condition

Let’s see an exampling using IF THEN statement with NOT EXISTS condition to avoid inserting a duplicate record in given table

IF NOT EXISTS(SELECT 1
 FROM customer
 WHERE ID = 100 )
THEN
  INSERT INTO customer (ID) VALUES (100)
END IF;
END;
/

Here, the NOT EXISTS SQL condition is true only if the parameter subquery returns no records from the specified table. If the condition is true, one record is inserted into the customer table.

Second Variant of IF statement: IF THEN ELSE Statement

The  syntax is as follows
IF ( condition ) THEN
  statement;
ELSE
  statement;
END IF;

Here, the IF condition is evaluated first. It if it’s true then the associated statement is executed. If false, control will pass to the ELSE clause and the statement associated with ELSE is executed. Take a look at the given program.

Example 4: To determine the bonus component of a particular employee’s salary

Here, we are working with an existing  table called employee  which has fields employee_id and salary.

DECLARE
  sales  NUMBER(8,2) := 15000;
  quota  NUMBER(8,2) := 10000;
  bonus  NUMBER(6,2);
  emp_id NUMBER(6) := 250;
BEGIN
  IF sales > (quota + 200) THEN
     bonus := (sales - quota)/4;
  ELSE
     bonus := 100;
  END IF;
  UPDATE employee SET salary = salary + bonus WHERE employee_id = emp_id;
END;
/

In this program, the IF condition is evaluated first and if true, the bonus of the employee is calculated. However, if the condition is false, the program control goes to the ELSE clause where a fixed value is assigned to the bonus variable. Next the SQL UPDATE command is executed, which updates the salary field of the record containing the given employee_id in the employee table by adding the bonus value to the existing salary value.

Third variant of IF statement: IF…THEN…ELSIF…ENDIF

At times you may want to choose between different options. For this you can use the IF THEN ELSIF Statement. The syntax will be as follows

IF ( condition-1 ) THEN
statement-1;
ELSIF ( condition-2 ) THEN
statement-2;
ELSIF ( condition-3 ) THEN
statement-3;
ELSE
statement;
END IF;

Note that the keyword here is ELSIF and not  ELSEIF or ELSE IF. There can be any number of ELSIF clauses. However the ELSE clause at the end is optional. Here the conditions are evaluated  individually at a time in sequential order. If a particular condition is true, its associated statements are executed. After this, control goes to the program statement following the END IF. In the case where all the conditions are false, the statements associated with the ELSE clause are executed.

EXAMPLE 5:  To determine employee bonus depending on different performance criteria

DECLARE
  sales  NUMBER(8,2) := 50000;
  bonus  NUMBER(6,2);
  emp_id NUMBER(6) := 120;
BEGIN
   IF sales > 60000 THEN
      bonus := 2000;
   ELSIF sales > 35000 THEN
      bonus := 1000;
   ELSE
      bonus := 500;
   END IF;
   UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id;
END;
/

Now that we’ve walked you through so many examples, try figuring out this one yourself. Leave you explanation in the comments!

Learning any new programming language is always fun. If you’re pressed for time, you can take our crash course to learn SQL in just 6 days. It’s definitely worth it!