Where (SQL)

A WHERE clause in SQL specifies that a SQL Data Manipulation Language (DML) statement should only affect rows that meet a specified criteria. The criteria is expressed in form of predicates. WHERE clauses are not mandatory clauses of SQL DML statements, but should be used to limit the number of rows affected by a SQL DML statement or returned by a query.

WHERE is an reserved word.

The WHERE clause is used in conjunction with SQL DML statements, and takes the following general form:

"SQL-DML-Statement" FROM "table_name" WHERE "predicate"

All rows for which the predicate in the WHERE clause is True are affected (or returned) by the SQL DML statement (or query). Rows for which the predicate evaluates to False or Unknown (NULL) are unaffected by the DML statement or query.

The following query returns only those rows from table "mytable" where the value in column "mycol" is greater than 100.

SELECT * FROM mytable WHERE mycol > 100

The following DELETE statement remove only those rows from table "mytable" where the column "mycol" is either NULL or has a value than is equal to 100.

DELETE FROM mytable WHERE mycol IS NULL OR mycol = 100

Predicates

Predicates do not only have to be simple comparisons but can be a combination of multiple predicates. The keywords AND and OR can be used to combine two predicates into a new one. If multiple combinations are applied, parenthesis can be used to group combinations. Without parenthesis, the AND operator has a stronger binding than OR.

The following example deletes rows from "mytable" where the value of "mycol" is greater than 100, and the value of "item" is equal to the string literal 'Hammer':

DELETE FROM mytable WHERE mycol > 100 AND item = 'Hammer'

LIKE

LIKE will find a string fitting a certain description.

*Ending Wildcard
**Find any string that begins with the letter 'S' SELECT ename FROM emp WHERE ename LIKE 'S%';
*Leading Wildcard
**Find any string that ends with the letter 'S' SELECT ename FROM emp WHERE ename LIKE '%S';
*Multiple Wildcards
**Find any string that contains, anywhere, the letter 'S' SELECT ename FROM emp WHERE ename LIKE '%S%';
*Single Character Wildcard
**Find any string that contains the letter 'A' followed by any single character followed by the letter 'E' SELECT ename FROM emp WHERE ename LIKE '%A_E%';

External Links

# [http://www.psoug.org/reference/conditions.html PSOUG Home Puget Sound Oracle Users Group] gives several examples of SELECT statements with WHERE clauses.