Where (SQL)
Encyclopedia
A
specifies that a SQL Data Manipulation Language (DML)
statement should only affect rows that meet specified criteria. The criteria are expressed in the form of predicates.
reserved word.
The
all rows for which the predicate in the
) 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.
The following
removes only those rows from table mytable where the column mycol is either NULL or has a value that is equal to 100.
Predicates can be enclosed in parentheses if desired. The keywords
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':
All rows match the predicate if their value is one of the candidate set of values. This is the same behavior as
except that the latter could allow comparison of several columns, which each
All rows match the predicate if their value is between 'value1' and 'value2', inclusive.
SQL programmers need to be aware that the LIKE predicate typically performs a search without the normal performance benefit of indexes. Using '=', '<>', etc.. instead will increase performance. Users of the LIKE predicate should be aware that case sensitivity (e.g., 'S' versus 's') may be different based upon database product or configuration.
WHERE
clause in SQLSQL
SQL is a programming language designed for managing data in relational database management systems ....
specifies that a SQL Data Manipulation Language (DML)
Data Manipulation Language
A data manipulation language is a family of syntax elements similar to a computer programming language used for inserting, deleting and updating data in a database...
statement should only affect rows that meet specified criteria. The criteria are expressed in the 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.Overview
WHERE
is an SQLSQL:2003
SQL:2003 is the fifth revision of the SQL database query language. The latest revision of the standard is SQL:2008.-Summary:The SQL:2003 standard makes minor modifications to all parts of SQL:1999 , and officially introduces a few new features such as:* XML-related features * Window functions* the...
reserved word.
The
WHERE
clause is used in conjunction with SQL DML statements, and takes the following general form: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 (NULLNull (SQL)
Null is a special marker used in Structured Query Language to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfill the requirement that all true relational database management systems support...
) 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.
The following
DELETE
statementDelete (SQL)
In the database structured query language , the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.-Usage:The DELETE statement follows the syntax:...
removes only those rows from table mytable where the column mycol is either NULL or has a value that is equal to 100.
Predicates
Simple predicates use one of the operators=
, <>
, >
, >=
, <
, <=
, IN
, BETWEEN
, LIKE
, IS NULL
or IS NOT NULL
.Predicates can be enclosed in parentheses if desired. The keywords
AND
and OR
can be used to combine two predicates into a new one. If multiple combinations are applied, parentheses can be used to group combinations to indicate the order of evaluation. Without parentheses, 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':
IN
IN
will find any values existing in a set of candidates.All rows match the predicate if their value is one of the candidate set of values. This is the same behavior as
except that the latter could allow comparison of several columns, which each
IN
clause does not. For a larger number of candidates, IN
is less verbose.BETWEEN
BETWEEN
will find any values within a range.All rows match the predicate if their value is between 'value1' and 'value2', inclusive.
LIKE
LIKE
will find a string fitting a certain description.- Ending Wildcard
- Find any string that begins with the letter 'S'
- Leading Wildcard
- Find any string that ends with the letter 'S'
- Multiple Wildcards
- Find any string that contains, anywhere, the letter 'S'
- Single Character Wildcard
- Find any string that contains the letter 'A' followed by any single character followed by the letter 'E'
SQL programmers need to be aware that the LIKE predicate typically performs a search without the normal performance benefit of indexes. Using '=', '<>', etc.. instead will increase performance. Users of the LIKE predicate should be aware that case sensitivity (e.g., 'S' versus 's') may be different based upon database product or configuration.
External links
- PSOUG Home Puget Sound Oracle Users Group gives several examples of SELECT statements with WHERE clauses.