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%'; |
Comparison operators in the WHERE clause
Ranges in the WHERE clause
Lists in the WHERE clause
Pattern matching character strings in the WHERE clause
Character strings and quotation marks
Unknown values: NULL
Testing a column for NULL
Properties of NULL
Logical operators that provide connecting conditions
Search conditions that compare dates
Row matching by sound
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |