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 AND Condition OR Condition Not Condition

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

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Objective

    The article explains the use of Logical operators (AND Condition, OR Condition and NOT Condition) in clubbing the conditions in a SELECT statement.

    1. Introduction to Logical operators

    To ensure proper and enhanced data refining of query result set, multiple filter conditions can be appended to WHERE clause in a single SELECT statement using Logical operators. Refer the truth table for each of the operator.

    • AND
    • OR
    • NOT

    [​IMG]

    The records which satisfy all the participating conditions qualify to appear in final result set.

    Code (SQL):
    WHERE col1 =VALUE----------//Condition 1
      AND col3 <> ‘CLUB-ORACLE’       ----------//Condition 2
      AND (col2 = 1000
            OR
           col4 > 5000)       ----------//Condition 3 & 4 (later covered in Precedence section)
    For instance, in the below example, a row appears from table in the result set only, if condition 1 and 2 are satisfied and either of conditions return true in 3 & 4.

    1.1. AND Operator

    With the use of AND Boolean operator, we make sure that all the participating terms are TRUE (satisfied).

    Example 1.1.1 Below SQL selects all HR's from department 20

    Code (SQL):
    SELECT EMPLOYEE_ID, ENAME, SALARY
    FROM EMPLOYEE
    WHERE DEARTMENT_ID = 20
    AND      JOB_ID = 'HR'

    EMPLOYEE_ID           ENAME             SALARY
    ---------------     ---------------    ---------------
    120                   KATE                        3400
    140                   NEO                        11000
    170                   RYAN                        5600
     
     
    1.2. OR Operator

    OR operator sets to TRUE if either (any one) of the participating terms is TRUE (satisfied).

    Example 1.2.1 Below SQL selects either Managers or those employee whose salary exceeds 10000.

    Code (SQL):
    SELECT EMPLOYEE_ID, ENAME, SALARY, JOB_ID
    FROM EMPLOYEE
    WHERE JOB_ID = 'MGR'
    OR SALARY > 10000

    EMPLOYEE_ID           ENAME             SALARY             JOB_ID
    ---------------     ---------------    ---------------     ---------------
    100                   KING                        7500              MGR
    110                   TIM                        12500              DEV
    150                   JOHN                        7800              MGR
    180                   PINNE                      10500              MGR

    1.3. NOT Operator

    The NOT operator just negates or reverses the logical stand of a condition.

    Note: The NOT operator can also be used with other SQL operators, such as BETWEEN, LIKE, and NULL. For example,
    Code (Text):
    a.  WHERE JOB_ID NOT IN ('AC_ACCOUNT', 'AD_VP')
    b.  WHERE SALARY NOT BETWEEN 10000 AND 15000
    c.  WHERE LAST_NAME NOT LIKE '%A%'
    2. Rules of Precedence of Logical operators

    Since multiple logical operators can be implemented in the same SQL query, it is equally important to set a protocol of their evaluation. Refer the below table for precedence rules.

    [​IMG]

    However, you can override the default order by using parentheses [()] around the expressions that you want to calculate first.

    Illustrations

    Example 2.1. Below example displays the employee details whose department id is 10 and last name is KING

    Code (SQL):
    SELECT
    ENAME,
    SALARY
    FROM EMPLOYEE
    WHERE DEPARTMENT_ID = 10
    AND LAST_NAME = ‘KING’
    Example 2.2. Below SQL displays the employee details whose department id is 10 and last name is KING, or whose JOB ID is Manager

    Code (SQL):
    SELECT
    ENAME,
    SALARY
    FROM EMPLOYEE
    WHERE DEPARTMENT_ID = 10
    AND LAST_NAME = ‘KING’
    OR    JOB_ID = ‘MGR’
    As per the precedence rules, above query contains two filters. Firstly, the employees which are working in department 10 and last name as KING and secondly, the employees which have job role as MANAGER irrespective of working department. Now, the final result would be the union of these two queries.


    Example 2.3. Below query will check only in department 10 for the employees whose last name is KING or job role is Manager.

    Code (SQL):
    SELECT
    ENAME,
    SALARY
    FROM EMPLOYEE
    WHERE DEPARTMENT_ID = 10
    AND (LAST_NAME = ‘KING’ OR JOB_ID = ‘MGR’)


    References

    Any External / Internal references for the article / tutorials /code snippets.
     

    Attached Files: