1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL: BETWEEN, IN and LIKE Operator

Discussion in 'SQL PL/SQL' started by SBH, Oct 16, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Introduction

    WHERE clause in Oracle adds condition to the result set and restricts it. This implies that the final result by virtue of these condition majorly depend on Condition framing.
    Two terms take part in a condition on account of their relation, which is nothing but the operator they use for comparison. Equal to, Less than, Greater than, AND, OR and many others are the relationship attribtes between two terms of a condition.

    Here, we shall discuss on more operators like BETWEEN, IN ad LIKE.

    2. The BETWEEN Operator


    BETWEEN operator is very similar to the Inequality operator. A term/value in a condition has to be compared within a definite range using BETWEEN operators. The range must have a lower and upper limit, both inclusive.

    For example, WHERE hire_date between ’01-JAN-2010’ and ’01-MAR-2010’ specified a value use of BETWEEN operator. Check the below query to implement this condition

    Example 2.1. Below query lists all recruitments between January and March 2010.

    Code (SQL):
    SELECT*
    FROM EMPLOYEE
    WHERE HIRE_DATE BETWEEN ’01-JAN-2010AND ’01-MAR-2010’;

    3. The IN Operator

    A term of a condition can be tested by equating it with defined fixed values of other term. This can be done using IN operator. The condition defined using the IN operator is also known as the membership condition. It works with value of all data types. In case of characters or dates list items, they must be enclosed with single quotation marks ('').

    For example ….WHERE SALARY IN (1000,2000,3000)

    As per above conditional statement, it will restrict the rows where salary is either of 1000, 2000 or 3000. Similarly, below query finds the employee whose last name is included in the list of names.

    Example 3.1. Below SQL show the use of IN operator. The query select those employee whose last name is either of KAIF or SAIF

    Code (SQL):
    SELECT
    EMPLOYEE_ID,
    MANAGER_ID,
    DEPARTMENT_ID
    FROM EMPLOYEE
    WHERE LAST_NAME IN ('KAIF', 'SAIF');
    4. LIKE Operator

    A term of a condition can be tested by equating possible predictive values other term using LIKE operator. If actual value of a participating term is partially known, then LIKE operator is used in to match the complete string literals.

    It uses wildcard operators to build up the search string, thus search is known as Wildcard search. These two operators are Percentile (‘%’) and Underscore (‘_’). Percentile (‘%’) replaces group of characters while Underscore (‘_’) substitutes a single character. For example,

    Example 4.1. Below SQL displays the employees whose last name ends with letter 'K'

    Code (SQL):
    SELECT
    ENAME,
    SALARY
    FROM EMPLOYEE
    WHERE LAST_NAME LIKE ‘K%’
    The above query returns the name and salary of all the employees whose have ‘J’ and ‘N’ characters in their last name. It can be KING, KHAN, KITE or KROOV etc.

    LIKE operator can also be used with numeric data. In such cases, it type casts the numeric into character and compares with the search literal formed using wild cards.

    Example 4.2. Below SQL displays the employee whose four digit salary ends with 800

    Code (SQL):
    SELECT *
    FROM EMPLOYEE
    WHERE SALARY LIKE '_800'
    The above query returns the employee details for the employees whose 4 digit salary ends with 800. It can be 1800, 2800 or 5800.

    5. ESCAPE Identifier

    When string containing actual % and _ characters has to be matched in wildcard search, ESCAPE identifier is used to escape the special meaning of such characters.
    Syntax: ESCAPE '<escape_character>'

    Example 5.1. Below SQL shows the employee details whose JOB ID contains 'SA\' as partial string

    Code (SQL):
    SELECT
    EMPLOYEE_ID,
    LAST_NAME,
    JOB_ID
    FROM EMPLOYEE
    WHERE JOB_ID LIKE '%SA\_%' ESCAPE '\';
    The ESCAPE identifier identifies the backslash (\) as the escape character. In the above SQL statement, the escape character precedes the underscore (_).

    Therefore, it ignores the ‘_’ as wildcard operator and searched the strings as ‘SA_’. It may be SA_REP, SA_MGR.

    6. IS (NOT) NULL Conditions

    The IS NULL operator serves as equality operator to check NULL values of a column. As per the relational theory, NULL is never equal to NULL also.

    Therefore, instead of checking WHERE TERM = NULL, IS NULL and IS NOT NULL operators are widely used is SQL query writing. The latter condition can be written as TERM IS NULL.

    Example 6.1. Below query to list the employees whose JOB ID is null (not assigned any role)

    Code (SQL):
    SELECT
    ENAME,
    DEPARTMENT_ID,
    SALARY
    FROM EMPLOYEE
    WHERE JOB_ID IS NULL
    References
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    SQL> SELECT
    ENAME,
    SALARY
    FROM EMPLOYEE
    WHERE LAST_NAME LIKE ‘K%’ ;

    can be done with alternative, looks like this

    SQL> SELECT
    ENAME,
    SALARY
    FROM EMPLOYEE
    WHERE SUBSTR(LAST_NAME ,1,1) = 'K';
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    SELECT * FROM EMPLOYEE WHERE HIRE_DATE BETWEEN ’01-JAN-2010AND ’01-MAR-2010’;
    One should never rely upon default date formats to convert date strincgs; errors can be produced when local and remote date formats don't match the supplied string:
    Code (SQL):
    SQL> ALTER SESSION SET nls_date_Format = 'DD-MON-RR';SESSION altered.SQL>
    SQL> SET echo ON
    SQL>
    SQL> SELECT empno, ename, hiredate
      2  FROM emp
      3  WHERE hiredate <= '17-NOV-81'
      4  /     EMPNO ENAME      HIREDATE
    ---------- ---------- ---------
          7369 SMITH      17-DEC-80
          7499 ALLEN      20-FEB-81
          7521 WARD       22-FEB-81
          7566 JONES      02-APR-81
          7654 MARTIN     28-SEP-81
          7698 BLAKE      01-MAY-81
          7782 CLARK      09-JUN-81
          7839 KING       17-NOV-81
          7844 TURNER     08-SEP-81
          7996 GUAKMOM    07-DEC-41
          7999 PRINGNOB   17-DEC-8011 ROWS selected.SQL>
    SQL> ALTER SESSION SET nls_date_format = 'MON-DD-YYYY';SESSION altered.SQL>
    SQL> SET echo ON
    SQL>
    SQL> SELECT empno, ename, hiredate
      2  FROM emp
      3  WHERE hiredate <= '17-NOV-81'
      4  /
    WHERE hiredate <= '17-NOV-81'
                      *
    ERROR at line 3:
    ORA-01843: NOT a valid MONTH
    SQL>
    Using the supplied TO_DATE() function will prevent such errors from occurring.
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    After altering the date session, now the default session is 'MON-DD-YYYY'

    Code (SQL):
    SELECT * FROM EMP WHERE HIREDATE <= 'NOV-17-1981';
    How the new date format works with old date format? we need to change accordingly.
     
  5. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Thanks Zargon and Kiran for your comments on Date conditions. Its helpful and add on to the article.
    In the article, main purpose was to show the use of BETWEEN operator.

    Thanks