A
WHERE
clause in
SQL 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.
WHERE
clauses are not mandatory clauses of SQL DML statements, but can be used to limit the number of rows affected by a SQL DML statement or returned by a query. In brief SQL WHERE clause is used to extract only those results from a SQL statement, such as:
SELECT
,
INSERT
,
UPDATE
, or
DELETE
statement.
Overview
WHERE
is an
SQL 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
Null may refer to:
Science, technology, and mathematics Computing
* Null (SQL) (or NULL), a special marker and keyword in SQL indicating that something has no value
* Null character, the zero-valued ASCII character, also designated by , often use ...
) are unaffected by the DML statement or query.
The following query returns only those rows from table ''mytable'' where the value in
column
A column or pillar in architecture and structural engineering is a structural element that transmits, through compression, the weight of the structure above to other structural elements below. In other words, a column is a compression member. ...
''mycol'' is greater than 100.
SELECT *
FROM mytable
WHERE mycol > 100
The following
DELETE
statement removes only those rows from table ''mytable'' where the column ''mycol'' is either NULL or has a value that is equal to 100.
DELETE
FROM mytable
WHERE mycol IS NULL OR mycol = 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
A string literal (computer programming), literal or anonymous string is a String (computer science), string value in the source code of a computer program. Modern Computer programming, programming languages commonly use a quoted sequence of charact ...
'Hammer':
DELETE
FROM mytable
WHERE mycol > 100 AND item = 'Hammer'
IN
IN
will find any values existing in a set of candidates.
SELECT ename WHERE ename IN ('Montreal', 'Quebec')
All rows match the predicate if their value is one of the candidate set of values. This is the same behavior as
SELECT ename WHERE ename='value1' OR ename='value2'
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.
SELECT ename WHERE ename BETWEEN 'value1' AND 'value2'
SELECT salary from emp WHERE salary BETWEEN 5000 AND 10000
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
Wild card most commonly refers to:
* Wild card (cards), a playing card that substitutes for any other card in card games
* Wild card (sports), a tournament or playoff place awarded to an individual or team that has not qualified through normal pla ...
** 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%';
* Character classes
Microsoft Technet
'Retrieved 21 November 2013.
** Find any string that starts with a letter or number or the symbol '_'
SELECT ename FROM emp WHERE ename LIKE ' -zA-Z0-9_';
The LIKE predicate typically performs a search without the normal performance benefit of indexes. Using '=', '<>', etc.. instead will increase performance. Case sensitivity (e.g., 'S' versus 's') may be different based upon database product or configuration.
References
External links
PSOUG Home Puget Sound Oracle Users Groupgives several examples of SELECT statements with WHERE clauses.
{{SQL
SQL keywords