CHAPTER 13: WHERE Clause

Example

1. The following WHERE clause restricts the records from personnel to those having the name of Jones. Since there is one record for each name in the personnel table, this restriction will have the effect of requesting a single record.

      SELECT * FROM personnel
         WHERE name = 'Jones';
   
   

   It produces the following output:

      number     name     phone        credit_limit
   
       3         Jones    667-2951     $500.00
   
   

2. The following WHERE clause restricts the records to those having a credit limit greater than or equal to $200.00. Records with credit limit less than $200.00 will not be requested. Note that it is not necessary to include the dollar sign for dollar values when using the Query Language. If the dollar sign is included, it is then necessary to precede it with another dollar sign, so that it will not be mistaken for an Empress variable.

      SELECT * FROM personnel
         WHERE credit_limit >= 200.00;
   
   

   This produces:

      number     name          phone        credit_limit
      
      10         Kilroy        426-9681     $500.00
      5          Mosca         544-2243     $750.00
      17         Wladislaw     723-6073     $200.00
      3          Jones         667-2951     $500.00
      8          Peterson      978-6060     $250.00
   
   

3. The following WHERE clause restricts the records to those with a name which is not Kilroy and a credit limit of $500.00. Since an AND is used to connect the two conditions, both conditions in parentheses must be true for the record to be requested. <> may be substituted for !=.

      SELECT * FROM personnel
         WHERE name <> 'Kilroy'
         AND credit_limit = '$$500';
   
   

   It produces:

      number     name      phone        credit_limit
   
       3         Jones     667-2951     $500.00
   
   

   Note the double $ to prevent interpretation of the credit limit as a variable.

4. The following WHERE clause restricts the records from the loans table to those having a date of 16 February 1990 or the name Wladislaw. Since an OR is used to connect the two conditions, either one or the other or both conditions must be true for the record to be requested.

      SELECT * FROM loans
         WHERE date = '16 February 1990'
         OR name = 'Wladislaw';
   
   

   and produces:

      name            date                 amount
   
      Kilroy          16 February 1990     $250.00
      Wladislaw       12 May 1990           $25.00
      Wladislaw       27 February 1990      $55.00
      Wladislaw       25 June 1990          $75.00
   
   

5. When it is necessary to select records where an attribute may have a number of different values, one way to accomplish this is to construct a complex WHERE clause using OR. For example, to find all loans by Jones, Kilroy, and Mosca, you could use the following to achieve the desired result:

      SELECT * FROM loans
         WHERE name = 'Jones' OR
         name = 'Kilroy' OR name = 'Mosca';
   
   

   There is, however, an alternative way to do this, using a constant list. In this case, the command above becomes:

      SELECT * FROM loans
         WHERE name IN
         ('Jones', 'Kilroy', 'Mosca');
   
   

   which saves considerable typing for a long list. If there is an index on the attribute name, the format should be used to utilize the indexed attribute.

   Similarly, all cases not in a list may be found. To find loans by everyone except Jones, Kilroy, and Mosca, use:

      SELECT * FROM loans
         WHERE name NOT IN
         ('Jones', 'Kilroy', 'Mosca');
   
   

6. The following would be used to find all employees who do not have a phone number entered. The keyword NULL indicates data not entered in the record.

      SELECT FROM personnel
         WHERE phone = NULL;
   
   

   For this table, this command produces the following output:

      number     name     phone          credit_limit
   
   

   There are no records with null values for phone, so only the attribute headings are printed. If preferred, it is possible to use the keywords IS and IS NOT instead of = and != when finding null or not-null values. Thus, the command:

      SELECT FROM personnel
         WHERE phone IS NULL;
   
   

   has exactly the same effect as the command above, while:

      SELECT FROM personnel
         WHERE phone IS NOT NULL;
   
   

   finds all the employees who do have phone numbers recorded.

7. The following would be used to find loans made before May 1990 by Jones or after April 1990 by Wladislaw. Conditions in parentheses are evaluated first. The parentheses are used to group conditions so that each group of name and date is requested as a unit and evaluated first.

   Since an OR is used to connect two conditions in parentheses, either the first or the second or both conditions must be true for the record to be requested. Note that for this particular case it is impossible for both conditions in parentheses to be true at the same time for the same record.

      SELECT FROM loans
         WHERE (name = 'Jones' AND date < '1 May 1990')
         OR (name = 'Wladislaw' AND date > '30 April 1990');
   
   

   It produces:

      name               date                 amount
   
      Jones               7 February 1990     $33.95
      Jones               3 April 1990        $25.00
      Wladislaw          12 May 1990          $25.00
      Wladislaw          25 June 1990         $75.00
   
   

   Parentheses may be included in a WHERE clause simply to emphasize the logic, if so desired.

8. The following WHERE clause restricts the records to those with loans larger than half the employee's credit limit.

      SELECT name, date, amount
         FROM loans, personnel
         WHERE amount > 0.5 * credit_limit
         AND loans.name = personnel.name;
   
   

   This produces:

      name               date                  amount
   
      Jones              12 August 1990        $300.00
      Scarlatti           9 September 1990     $150.00
      Scarlatti          14 October 1990       $125.00
   
   

9. You could print the name, phone, and credit limit of all employees who currently have loans with the command:

      SELECT name, phone, credit_limit
         FROM personnel WHERE exists
         (SELECT name FROM loans
          WHERE loans.name = personnel.name);
   
   

   which produces:

      name          phone           credit_limit
      
      Jones         667-2951        $500.00
      Kilroy        426-9681        $500.00
      Mosca         544-2243        $750.00
      Peterson      978-6060        $250.00
      Scarlatti     961-7363        $100.00
      Wladislaw     723-6073        $200.00
   
   

   The following WHERE clause finds all loans less than the average loan amount over all loans, by using a nested select statement to find the average.

      SELECT FROM loans
         WHERE amount < (SELECT AVG amount FROM loans);
   
   

   It produces:

      name            date                amount
      
      Jones            7 February 1990    $33.95
      Wladislaw       27 February 1990    $55.00
      Jones            3 April 1990       $25.00
      Wladislaw       12 May 1990         $25.00
      Peterson         6 June 1990        $50.00
      Wladislaw       25 June 1990        $75.00
   
   

   This example prints the names and credit limits of all employees with single loans exceeding $200 outstanding.

      SELECT name, credit_limit FROM personnel
         WHERE name= ANY
         (SELECT name FROM loans WHERE amount > 200);
   
   

      name         credit_limit
   
      Kilroy       $500.00
      Jones        $500.00
   
   

10. The following WHERE clause restricts the records to those having a pattern of characters in phone that matches the pattern of the number 9, followed by zero or more of any other characters. This could be used to find all employees at a given local phone exchange. In this case, it will find the records with the phone numbers 978-6060 and 961-7363.

       SELECT * FROM personnel
          WHERE phone MATCH '9*';
    
    

    It produces:

       number    name        phone       credit_limit
    
       8        Peterson     978-6060    $250.004
                Scarlatti    961-7363    $100.00
    
    

    This command may also be expressed as:

       SELECT * FROM personnel
          WHERE phone LIKE '9%';
    
    

    The following WHERE clause restricts records to those having a pattern of characters in date that matches the pattern of zero or more characters, a blank, the letters May followed by a blank, followed by zero or more characters. This would find all the loans which were made during May in any year.

       SELECT * FROM loans
          WHERE date MATCH '* May *';
    
    

    It produces:

       name            date           amount
    
       Mosca            4 May 1990    $200.00
       Wladislaw       12 May 1990     $25.00
    
    

    Since the only letters in a date are in the month, a more terse way to look for all May entries is to look for the letters May preceded and followed by anything:

       SELECT * FROM loans
          WHERE date MATCH '*May*';
    
    

    To find all loans not made in May, the appropriate command is:

       SELECT * FROM loans WHERE date !MATCH '*May*';
    
    

    or

       SELECT * FROM loans WHERE date NOT LIKE '%May%';
    
    

    which produces:

       name               date                 amount
       
       Mosca               2 February 1990     $150.00
       Jones               7 February 1990      $33.95
       Kilroy             16 February 1990     $250.00
       Wladislaw          27 February 1990      $55.00
       Jones               3 April 1990         $25.00
       Peterson            6 June 1990          $50.00
       Wladislaw          25 June 1990          $75.00
       Jones              12 August 1990       $300.00
       Scarlatti           9 September 1990    $150.00
       Scarlatti          14 October 1990      $125.00
    
    

    The effect of SMATCH may be shown by a selection from the auto parts table. The command:

       SELECT supplier, phone, ATTR 'part name', price
          FROM 'auto parts'
          WHERE ATTR 'part name' SMATCH '*chrome*';
    
    

    produces:

       supplier           phone        part name              price
    
       AAA Automotive     922-8624     Fender--chrome          $67.95
    
    

    with just a single record, whereas the command:

       SELECT supplier, phone, ATTR 'part name', price
          FROM 'auto parts'
          WHERE ATTR 'part name' MATCH '*chrome*';
    
    

    produces the following with two records:

       supplier             phone        part name              price
    
       AAA Automotive       922-8624     Fender--chrome        $67.95
       Auto Wholesalers     922-6219     Chrome paint           $1.99
    
    

    Note that the complex attribute name part name must be preceded by the keyword ATTR to indicate that it is an attribute, not an expression.

11. To find values lying within a given range, the keyword RANGE should be used in preference to a complex WHERE clause such as:

       WHERE ATTRIBUTE first_constant AND ATTRIBUTE second_constant
    
    

    The range must be given from a lower to a higher value for any record to meet the condition. Selecting a range from a higher to a lower value does not produce an error, but no records will be selected. The records with values equal to the upper and lower range limits are included by default. These values can be excluded from the WHERE clause by specifying EXCLUSIVE in the WHERE condition.

    Note that BETWEEN ... AND may be used instead of RANGE ... TO.

    The following would be used to find all loans between $100 and $200, inclusive:

       SELECT name, date, amount FROM loans
          WHERE amount RANGE 100 to 200;
    
    

    It produces:

       name               date                 amount
    
       Mosca               2 February 1990     $150.00
       Mosca               4 May 1990          $200.00
       Scarlatti           9 September 1990    $150.00
       Scarlatti          14 October 1990      $125.00
    
    

    By contrast, the command:

       SELECT name, date, amount FROM loans
          WHERE amount RANGE 100 EXCLUSIVE TO 200 EXCLUSIVE;
    
    

    produces only:

       name               date                amount
    
       Mosca              2 February 1990      $150.00
       Scarlatti          9 September 1990     $150.00
       Scarlatti          9 September 1990     $125.00
    
    

    The following finds all loans over $200.00 but less than or equal to $300.00:

       SELECT name, date, amount FROM loans
          WHERE amount RANGE 200 EXCLUSIVE TO 300;
    
    

    It produces:

       name            date                 amount
    
       Kilroy          16 February 1990     $250.00
       Jones           12 August 1990       $300.00
    
    

12. Use BETWEEN ... AND to specify a range of acceptable values. BETWEEN ... AND may be used as a synonym for RANGE ... TO. A BETWEEN ... AND can also involve a complex boolean expression, but is then evaluated more slowly.

       SELECT * FROM table WHERE
          attr BETWEEN value AND value;
    
    

    is equivalent to:

       SELECT * FROM table WHERE
          attr RANGE value TO value;
    
    

    Thus, the example in the previous section which finds credit limits between $100 and $200 can also be expressed as:

       SELECT name, date, amount FROM loans
          WHERE amount BETWEEN 100 EXCLUSIVE AND 200 EXCLUSIVE;
    
    

    and produces exactly the same output:

       name                date                 amount
    
       Mosca               2 February 1990      $150.00
       Scarlatti           9 September 1990     $150.00
       Scarlatti           9 September 1990     $125.00
    
    

    As with RANGE ... TO, the values must be given in increasing order for any record to meet the condition.