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!

WHERE Clause in SQL SELECT

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 demonstrates the use of WHERE clause and various operators to restrict the use of SELECT result set.

    1. Introduction of WHERE clause

    The SELECT query result can be restricted or filtered by passing the result set through logical conditions. A restricting condition qualifies to be a logical condition based on the WHERE clause with appropriate use of comparison and logical operators.

    Filtering of the result set can be achieved using WHERE clause. It can be based on one condition or set of conditions. The two participating terms of the condition are compared on virtue of various comparison operators and logical operators.

    Syntax:

    Code (Text):
    SELECT *|{[DISTINCT] column| expression [alias],…}
    FROM table
    [WHERE condition(s)]
    Please note in the syntax that, WHERE clause appears after the FROM clause in a SELECT query. This hierarchy must be maintained, violating which Oracle server throws error.

    Example 1.1. Below SQL gets Employee details in department 20

    [​IMG]

    Code (SQL):
    SELECT
    EMPLOYEE_ID,
    ENAME,
    DEPARTMENT_ID
    FROM EMPLOYEE
    WHERE DEPARTMENT_ID = 20
    The participating terms in a condition must be of similar or compatible data type. For example, in the below query, value for SALARY column must be equated to a number value only.


    Example 1.2. Below SQL shows the use of compatible data tye value in the WHERE clause conditions

    Code (SQL):
    SELECT
    ENAME,
    LAST_NAME,
    SALARY
    FROM    EMPLOYEE
    WHERE SALARY = 5000
    Character literals must be enclosed within quotes. For example,

    Example 1.3. Below SQL shows the use of string literals in WHERE clause.

    Code (SQL):
    SELECT <Col List>
    FROM EMP
    WHERE ENAME = ‘KING’
    Using (WHERE LAST_NAME = KING) in the above SQL would throw an error as KING would be interpreted as an identifier.

    Date values and date columns can be used in the WHERE clause predicates to build up the filter condition. Please note that a date literal is interpreted equivalent to character literal by Oracle, which can be used as one of the term against date column value.For example

    2. Comparison Operators

    Since conditions play vital role in data fetch and presentation, the conditions must be meaningful, syntactically correct and implement the requirement in a logical way.

    These conditional operators compare term values and expressions. They can be equality, inequality, BETWEEN, IN, LIKE and IS (NOT) NULL. To be noted, an alias cannot be used in the WHERE clause.

    Syntax
    Code (Text):
    ... WHERE expr1 operator expr2
    Equality and Inequality operators

    Two terms of a condition are tested for equality using Equality operator (‘=’). For example,
    ….WHERE JOB_ID = ‘MGR’ shows an equality condition.

    A term of a condition can be tested for range bound, defined by the other terms, using Inequality operator. ‘<’,’>’ are known as inequality operators while ’< =’,’> =’ are the composite inequality operators.
    Limit or ranges can be numeric, date, or character. For example,

    Code (Text):
    a.  WHERE SALARY <= 5000
    b.  WHERE ENAME <= ‘MYNAME’ and ENAME >= ‘YOURNAME’
    Example 2.1. Below SQL displays the employee records whose salary ranges between 2000 to 3000

    Code (SQL):
    SELECT
    EMPNO,
    ENAME,
    SALARY,
    FROM EMPLOYEE
    WHERE SALARY >=2000 AND SALARY <= 3000

    EMPNO      ENAME      SALARY
    ---------- ---------- -----------
           100 JOHN              2500
           120 MILLS             3000
           130 KATES            2000

     
    Limiting rows with the multiple conditions
    Multiple conditons can be appended together using logical operators to provide enhanced filteration of the result set. For example, below SELECT statement has two conditions to be satisfied before displaying the final result. So, only those records appear in the final result set which satisify both the conditions.

    Example 2.2. Below SQL displays the employees working in department 10 and last name is ‘KING’

    Code (SQL):
    SELECT
    ENAME,
    SALARY
    FROM EMP LOYEE
    WHERE DEPARTMENT_ID = 10
    AND LAST_NAME = ‘KING’
    Refer the article 'Logical Operators in SQL' for more details.


    References
     

    Attached Files: