The WHERE clause: Specifying rows

The WHERE clause in a SELECT statement specifies the search conditions for exactly which rows are retrieved. Search conditions are also referred to as predicates. The general format is: 

SELECT select-list
FROM table-list
WHERE search-condition

Search conditions in the WHERE clause include the following:

  • Comparison operators   (=, <, >, and so on) For example, you can list all employees earning more than $50,000: 
    SELECT Surname
   FROM Employees
   WHERE Salary > 50000;

  • Ranges   (BETWEEN and NOT BETWEEN) For example, you can list all employees earning between $40,000 and $60,000: 
    SELECT Surname
   FROM Employees
   WHERE Salary BETWEEN 40000 AND 60000;

  • Lists   (IN, NOT IN) For example, you can list all customers in Ontario, Quebec, or Manitoba: 
    SELECT CompanyName, State
   FROM Customers
   WHERE State IN( 'ON', 'PQ', 'MB');

  • Character matches   (LIKE and NOT LIKE) For example, you can list all customers whose phone numbers start with 415. (The phone number is stored as a string in the database): 
    SELECT CompanyName, Phone
   FROM Customers
   WHERE Phone LIKE '415%';

  • Unknown values   (IS NULL and IS NOT NULL) For example, you can list all departments with managers: 
    SELECT DepartmentName
   FROM Departments
   WHERE DepartmentHeadID IS NOT NULL;

  • Combinations   (AND, OR) For example, you can list all employees earning over $50,000 whose first name begins with the letter A. 
    SELECT GivenName, Surname
   FROM Employees
   WHERE Salary > 50000
   AND GivenName like 'A%';

For the full syntax of search conditions, see Search conditions.

Using comparison operators in the WHERE clause
Using ranges in the WHERE clause
Using lists in the WHERE clause
Matching character strings in the WHERE clause
Character strings and quotation marks
Unknown Values: NULL
Testing a column for NULL
Properties of NULL
Connecting conditions with logical operators
Comparing dates in search conditions
Matching rows by sound

Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0