Operators in SQL....

In this post I want to discuss about different operators which are available in SQL. These operators are very useful while writing the SQL statements depending on the requirement.

  The different types of operators which are available in SQL are,

        1. Concatenation Operator
        2. Arithmetic Operators
        3. Logical Operators
        4. Comparison Operators

  Before discussing about these operators we need to know about a table named 'DUAL' which is very
  important in writing SELECT statements.

  Dual ::


       Dual is a pseudo table which is having single row and single column. This is system table of database.
This table will not contain any data on its own. If we select the data using select statement from dual
table then we will see data as 'x'. This table is used to evaluate expression on the data which is not stored
in the database tables. So, this table is used for evaluation of general expressions.

      As, dual table will not contain any data, always it will display the output of the expression which we
are evaluating using dual.

EX : Let us say we want to check the sum of two numbers 234567 and 876954. If we want to add these
two numbers we can use + operator. But these two are independent numbers which are not stored in the
database. So, we need to evaluate arbitrary expression here. For this purpose dual table will be used.

   SELECT 234567 + 876954 FROM Dual;

So, this statement will give us the result of the sum of those 2 numbers. Like this, If we want to evaluate the
expressions involving all arbitrary values where data base tables not involved, we can use Dual table to evaluate them.

Now Let us discuss about the Operators in SQL.

Concatenation Operator ::


   Concatenation operator is used to combine two different strings into a single string. Using this operator we can combine multiple strings into single string. ' || ' will be used as concatenation operator in SQL.

 SYNTAX :  string1 || string2 || string3 ....

 EX : SELECT 'Ram ' || 'Babu' || ' Kandimalla' FROM dual;

  If we execute this statement then we will get output as , Ram Babu Kandimalla. So, here we have concatenated 3 strings into a single string using this operator. Whenever we have a requirement of combining two or more strings we can use concatenation operator.

 Ex : Let us say ,We have Emp table having many columns like empno , ename, sal, hiredate ..... . From this table I want to display the output in the format  like 'Ramu is earning sal of 5000'. This I can achieve with following query using concatenation operator.

  SELECT ename||' is earning sal of '|| sal FROM Emp;

This query will give us the required result. So, like this wherever we have the requirement of combining two strings or values into single string or value we can use concatenation operator.

Arithmetic Operators ::


    Arithmetic Operators are used to perform arithmetic operations on numeric columns or numeric values. We have 4 arithmetic operators available.

   1. Addition Operator ( + )
   2. Subtraction Operator ( - )
   3. Multiplication Operator ( * )
   4. Division Operator ( / )

All these operators are used to perform arithmetic operations.

 Ex : SELECT 2345667 + 543276 FROM dual;
        SELECT sal + bonus FROM Emp;
        SELECT 2358976 / 4567 FROM dual;
        SELECT sal * 5 FROM Emp;

Precedence of Arithmetic Operators ::


   Precedence means, which arithmetic operator will be executed first, if we have all the 4 arithmetic operators in an expression as shown below.

   SELECT 12897654 + 234567 * 45321 / 567 - 2345 FROM dual;

  In this case first division operator (/) will be executed first . So, here 45321/567 will be executed first. after that multiplication operator will be executed. 234567 * (result of division) . Then either + or - will be executed. Like this the expression will be evaluated and result will be displayed.

 So, Order of precedence of the arithmetic operators evaluation is,

    /     *   +   -

 If we want to override this precedence then we have to enclose the operators in brackets. Then precedence will be override accordingly.

Ex : SELECT (1298677+23456) * 45678 / (567-234) FROM dual;

     Now even though we have all the 4 operators in this expression, we gave  + and - operators in brackets. So, first they will be executed. and after that / followed by * will be executed. Like this we can override the precedence.


  Comparison Operators ::


     Comparison Operators are used to perform comparison operations in SQL.  While writing SQL statements, comparison operations are very useful depending on the requirement. To, perform these comparison operations we will use these operators. We have many comparison operators in SQL.

    1 . Equal to operator ( = ) :: used to match single value
    2.  Not equal to Operator ( != or <> )
    3.  Less than Operator ( < )
    4. Greater than Operator ( > )
    5. Less than or equal to Operator ( <= )
    6. Greater than or equal to Operator ( >= )
    7. In operator
    8. Like Operator
    9. Between And Operator
    10. Is Null Operator

 All these Operators are used to perform Comparison operations in SQL. Operators from 1 to 6 are general comparison operators which we all know about what they are used for. So, I will just give examples for these operators.

While performing comparison operation with character data, we must remember that this operation is case sensitive. It meane 'RAMU' is not equal to 'ramu' as first is containing upper case letters and second one is containing lower case letters. So, we need to be careful while performing comparison operation with character data. We must use correct case for the letters. Similarly we must be careful while comparing date data . We must use default format of dates for comparison.

    EX : SELECT * FROM Emp WHERE ename = 'RAMU';

            The above query will give the row corresponding to employee with name 'RAMU' from Emp table.

            SELECT * FROM Emp WHERE ename <> 'RAMU';
            SELECT * FROM Emp WHERE hiredate != '13-JAN-2011';

            The above two queries are performing not equal to operation.,

            SELECT * FROM Emp WHERE sal < 15000;
            SELECT * FROM Emp WHERE sal > 1500;
            SELECT * FROM Emp WHERE sal <= 15000;
            SELECT * FROM Emp WHERE sal >= 15000;

    Now let us discuss about remaining comparison operators.

  In Operator ::

      In Operator is used for matching multiple values to single column. Using '=' operator we can match single value only. If you want to compare multiple values in single comparison then we can use IN operator.

  EX : If we want to find out all the employees from departments 10, 20 and 30 . Here we have to match deptno column with values 10,20 and 30 at a time. In this case we can use In operator.

    SELECT * FROM Emp WHERE deptno IN ( 10, 20, 30 );

 This is the syntax for using the IN operator. Like this using IN operator we can match multiple values in single comparison.

 IS NULL Operator ::


     This operator is used to check the presence of null values in a column. If we want to check whether null values are present in any column we can use this operator.

  EX : If we want to check if null values are present comm column of emp table in any of the rows we can use following statement.

   SELECT * FROM Emp WHERE comm IS NULL;

 So, this will give us all the rows of Emp table where comm column contains null values.

 Between - And Operator ::


      This operator is used to match range of values to a column in single comparison. Unlike In condition where we will match selective multiple values, we will match range of values using this operator.

 EX : If we want to find out all the employees from emp table who are earning sal in the range of 1500 to 2000. We cannot do this using In as we have to list 500 values in In condition. So, here Between And operator will be useful. We can write the select statement for this requirement as shown below.

      SELECT * FROM Emp WHERE sal BETWEEN 1500 AND 2000;

   This operation is inclusive of comparison values. It means employees with sal's 1500 and 2000 also will come in the result.
     
  Like Operator ::


    Like operator will be used to match single or multiple characters with character or numeric columns. If we want to match single character or multiple characters to get the result without matching entire words we can use Like operator . Using this Like Operator we can match entire words also as shown below.

     SELECT * FROM Emp WHERE ename LIKE 'RAMU';

  But main use of like operator comes with its ability to match partial values . This can be achieved using two special characters called as Wild cards. So, While discussing about Like operator in detail , we will discuss about Wild cards also.

  We will use two wild cards in SQL to work with the Like Operator. They are,

       %     and   _ (under score)

   % will be used for matching the partial characters. If, we place % after set of characters like 'ab%' then it means that we want all the strings starting with ab. If we place % before the set of characters like '%ab' then it means that we want all the string starting with ab. If we place % on both sides like '%ab%' then it will give us all the strings containing string 'ab' in any position. Like this we can use % for matching partial characters depending on the requirement.

Let me explain this with an example.

 EX : Let us say we want to find out all the employees from Emp table , whose names starts with letter 'R' then we can write the query as,,

       SELECT * FROM Emp WHERE ename LIKE 'R%';

  If we want to find out employees whose name ends with 'H' then we can write the query as,

      SELECT * FROM Emp WHERE ename LIKE '%H';

 If we want to find out employees whose name contains 'BA' in any position of their name we can write the query as,

      SELECT * FROM Emp WHERE ename LIKE '%BA%';

 Depending on the requirement we can use this % operator accordingly.

If we want to find out employees whose name starts with 'A' and ends with 'K' then we can write this query as,

     SELECT * FROM Emp WHERE ename LIKE 'A%K';

If we want to find out out employees whose names starts with 'S' and ends with 'R' and containing 'H' in any position of their names we can write the query as,

     SELECT * FROM Emp WHERE ename LIKE 'S%H%R';

So, position of % in comparison condition will decide  the position of searching characters in comparison strings. So, we must place % in appropriate position to get desired result.

 _ (under score) wild card will be used to skip a position in the string from the comparison. Let me explain this with examples. Generally this will be used in conjunction with % wild card.

 Ex : Let us say we want to find out all the employees whose names contain letter 'A' in second position. In this case we dont bother about the letter in the first place. So, we need to skip that from comparison. In this case _ will be very useful. We can write the query for this as shown below.

  SELECT * FROM Emp WHERE ename LIKE '_A%';

 So, this will give us all the employees whose names contain letter 'A' in second position. So, like this _ wild card will be used to skip certain positions of string from the comparison.

   So, we can use _ and % wild cards in conjunction along with Like operator depending on the requirements to get the desired results.

Logical Operators ::


     Logical operators are used for performing logical operations in SQL . These are very useful while writing the SQL statements with comparison operators. We have 3 logical operators available in SQL.

    1. AND operator
    2. OR operator
    3. NOT operator

 AND and OR logical operators are used to combine or evaluate multiple conditions together. we can combine two conditions using AND or OR operators. If we want to combine more, we have to use brackets.

If we have combined two conditions using AND operator, then if both the conditions are satisfied then only we will get the result.

EX :  SELECT * FROM Emp WHERE empno = 123 AND deptno=20;

      This statement will produce the result only if the table contains a row having empno as 123 and deptno as 20. Other wise this statement will not produce any result.

If we have combined two conditions using OR operator then we will get the result if any of the  two or both the conditions satisfied.

EX : SELECT * FROM Emp where empno=123 OR deptno=20;

This statement will give us all the employees from deptno = 20 along with employee with empno as 123.

NOT logical operator will be used to perform negation operation on the conditions mentioned. Let me explain this with examples. we can use this operator in conjunction with certain comparison operators to get the negative comparison results.

EX :  SELECT * FROM Emp WHERE deptno NOT IN (10,20);
           
          The above statement will give details of all the employees from emp table except from depts 10 and 20.
         SELECT * FROM Emp WHERE ename NOT LIKE 'A%';
 
          The above statement will give us all the employees from emp table except those employees  whose name starts with A.

         SELECT * FROM Emp WHERE comm IS NOT NULL;  

          The above statement will give us all the employees who are having comm as not null value.

Order of Precedence of all the operators ::


     If we have all the above mentioned operators in single statement then order of execution of these operators will be as shown below.

    ---> Arithmetic Operators
    ---> Concatenation Operator
    ---> 1 to 6 comparison operators
    ---> IS (NOT) NULL, LIKE , (NOT) IN operators
    ---> (NOT) Between operator
    ---> NOT Logical operator
    ---> AND Logical Operator
    ---> OR Logical Operator

We can override precedence of any of these operators by enclosing  them in brackets.