You can use these simple conditions or comparisons in the WHERE clause:
You also can use a SELECT statement within the WHERE clause; this is called a subquery. The following WHERE clause operators are valid in a subquery:
For more information, see Condition .
In the WHERE clause, an aggregate function is not valid unless it is part of a subquery or is on a correlated column originating from a parent query, and the WHERE clause is in a subquery within a HAVING clause.
A relational-operator condition is satisfied if the expressions on each side of the operator fulfill the relation that the operator specifies. The following SELECT statements use the greater than ( > ) and equal ( = ) relational operators:
SELECT order_num FROM orders WHERE order_date > '6/04/98' SELECT fname, lname, company FROM customer WHERE city[1,3] = 'San'
Quotes are required around 'San' because the substring is from a character column. See the Relational-Operator Condition.
The IN condition is satisfied when the expression to the left of the IN keyword is included in the list of values to the right of the keyword.
The following examples show the IN condition:
SELECT lname, fname, company FROM customer
WHERE state IN ('CA','WA', 'NJ')
SELECT * FROM cust_calls
WHERE user_id NOT IN (USER )
For more information, see the IN Subquery.
The BETWEEN condition is satisfied when the value to the left of BETWEEN is in the inclusive range of the two values on the right of BETWEEN. The first two queries in the following example use literal values after the BETWEEN keyword. The third query uses the built-in CURRENT function and a literal interval to search for dates between the current day and seven days earlier.
SELECT stock_num, manu_code FROM stock WHERE unit_price BETWEEN 125.00 AND 200.00 SELECT DISTINCT customer_num, stock_num, manu_code FROM orders, items WHERE order_date BETWEEN '6/1/97' AND '9/1/97' SELECT * FROM cust_calls WHERE call_dtime BETWEEN (CURRENT - INTERVAL(7) DAY TO DAY) AND CURRENT
For more information, see the BETWEEN Condition.
The IS NULL condition is satisfied if the column contains a NULL value. If you use the NOT option, the condition is satisfied when the column contains a value that is not NULL. The following example selects the order numbers and customer numbers for which the order has not been paid:
SELECT order_num, customer_num FROM orders WHERE paid_date IS NULL
For a complete description, see the IS NULL Condition.
The LIKE or MATCHES condition is satisfied if either of the following is true:
The following SELECT statement returns all rows in the customer table in which the lname column begins with the literal string 'Baxter'. Because the string is a literal string, the condition is case sensitive.
SELECT * FROM customer WHERE lname LIKE 'Baxter%'
The next SELECT statement returns all rows in the customer table in which the value of the lname column matches the value of the fname column:
SELECT * FROM customer WHERE lname LIKE fname
The following examples use the LIKE condition with a wildcard. The first SELECT statement finds all stock items that are some kind of ball. The second SELECT statement finds all company names that contain a percent ( % ) sign. Backslash ( \ ) is used as the default escape character for the percent ( % ) sign wildcard. The third SELECT statement uses the ESCAPE option with the LIKE condition to retrieve rows from the customer table in which the company column includes a percent ( % ) sign. The z is used as an escape character for the percent ( % ) sign:
SELECT stock_num, manu_code FROM stock WHERE description LIKE '%ball' SELECT * FROM customer WHERE company LIKE '%\%%' SELECT * FROM customer WHERE company LIKE '%z%%' ESCAPE 'z'
The following examples use MATCHES with a wildcard in SELECT statements. The first SELECT statement finds all stock items that are some kind of ball. The second SELECT statement finds all company names that contain an asterisk ( * ). The backslash ( \ ) is used as the default escape character for a literal asterisk ( * ) character. The third statement uses the ESCAPE option with the MATCHES condition to retrieve rows from the customer table where the company column includes an asterisk ( * ). The z character is specified as an escape character for the asterisk ( * ) character:
SELECT stock_num, manu_code FROM stock WHERE description MATCHES '*ball' SELECT * FROM customer WHERE company MATCHES '*\**' SELECT * FROM customer WHERE company MATCHES '*z**' ESCAPE 'z'
See also the LIKE and MATCHES Condition.
With the IN subquery, more than one row can be returned, but only one column can be returned.
This example shows the use of an IN subquery in a SELECT statement:
SELECT DISTINCT customer_num FROM orders
WHERE order_num NOT IN
(SELECT order_num FROM items
WHERE stock_num = 1)
For additional information, see the IN Condition.
With the EXISTS subquery, one or more columns can be returned.
The following example of a SELECT statement with an EXISTS subquery returns the stock number and manufacturer code for every item that has never been ordered (and is therefore not listed in the items table).
It is appropriate to use an EXISTS subquery in this SELECT statement because you need the correlated subquery to test both stock_num and manu_code in the items table.
SELECT stock_num, manu_code FROM stock
WHERE NOT EXISTS
(SELECT stock_num, manu_code FROM items
WHERE stock.stock_num = items.stock_num AND
stock.manu_code = items.manu_code)
The preceding example would work equally well if you use a SELECT * in the subquery in place of the column names, because you are testing for the existence of a row or rows.
For additional information, see the EXISTS Subquery.
The following examples return the order number of all orders that contain an item whose total price is greater than the total price of every item in order number 1023. The first SELECT uses the ALL subquery, and the second SELECT produces the same result by using the MAX aggregate function.
SELECT DISTINCT order_num FROM items
WHERE total_price > ALL (SELECT total_price FROM items
WHERE order_num = 1023)
SELECT DISTINCT order_num FROM items
WHERE total_price > SELECT MAX(total_price) FROM items
WHERE order_num = 1023)
The following SELECT statements return the order number of all orders that contain an item whose total price is greater than the total price of at least one of the items in order number 1023. The first SELECT statement uses the ANY keyword, and the second SELECT statement uses the MIN aggregate function:
SELECT DISTINCT order_num FROM items
WHERE total_price > ANY (SELECT total_price FROM items
WHERE order_num = 1023)
SELECT DISTINCT order_num FROM items
WHERE total_price > (SELECT MIN(total_price) FROM items
WHERE order_num = 1023)
You can omit the keywords ANY, ALL, or SOME in a subquery if the subquery returns exactly one value. If you omit ANY, ALL, or SOME, and the subquery returns more than one value, you receive an error. The subquery in the next example returns only one row, because it uses an aggregate function:
SELECT order_num FROM items
WHERE stock_num = 9 AND quantity =
(SELECT MAX(quantity) FROM items WHERE stock_num = 9)
See also ALL, ANY, and SOME Subqueries.
Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]