Difference between revisions 953314383 and 956147597 on enwiki

{{Refimprove|date=September 2014}}
A <code>WHERE</code> clause in [[SQL]] specifies that a SQL [[Data manipulation language|Data Manipulation Language (DML)]] statement should only affect [[Row (database)|rows]] that meet specified criteria. The criteria are expressed in the form of predicates. <code>WHERE</code> 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.<ref>{{cite web|url=http://www.programmingunit.com/2014/01/30/sql-clause-things-beginners-must-know/|title=SQL WHERE Clause – Things beginners must know}}</ref>

==Overview==
<code>WHERE</code> is an [[SQL:2003|SQL]] reserved word.

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

<sourceyntaxhighlight lang="sql">
SQL-DML-Statement
FROM table_name 
WHERE predicate
</sourceyntaxhighlight>

all rows for which the predicate in the <code>WHERE</code> 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 (SQL)|NULL]]) are unaffected by the DML statement or query.

The following query returns only those rows from table ''mytable'' where the value in [[Column (database)|column]] ''mycol'' is greater than 100.

<sourceyntaxhighlight lang="sql">
SELECT *
FROM   mytable
WHERE  mycol > 100
</sourceyntaxhighlight>

The following [[Delete (SQL)|<code>DELETE</code> statement]] removes only those rows from table ''mytable'' where the column ''mycol'' is either NULL or has a value that is equal to 100.
<sourceyntaxhighlight lang="sql">
DELETE
FROM   mytable
WHERE  mycol IS NULL OR mycol = 100
</sourceyntaxhighlight>

== Predicates ==
Simple predicates use one of the operators <code>=</code>, <code><></code>, <code>></code>, <code>>=</code>, <code><</code>, <code><=</code>, <code>IN</code>, <code>BETWEEN</code>, <code>LIKE</code>, <code>IS NULL</code> or <code>IS NOT NULL</code>.

Predicates can be enclosed in parentheses if desired. The keywords <code>AND</code> and <code>OR</code> 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 <code>AND</code> operator has a stronger binding than <code>OR</code>.

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':

<sourceyntaxhighlight lang="sql">
DELETE
FROM   mytable
WHERE  mycol > 100 AND item = 'Hammer'
</sourceyntaxhighlight>

=== IN ===
<code>IN</code> will find any values existing in a set of candidates.
<sourceyntaxhighlight lang="sql" start="1">
SELECT ename WHERE ename IN ('Montreal', 'Quebec')
</sourceyntaxhighlight>
All rows match the predicate if their value is one of the candidate set of values. This is the same behavior as
<sourceyntaxhighlight lang="sql">
SELECT ename WHERE ename='value1' OR ename='value2'
</sourceyntaxhighlight>
except that the latter could allow comparison of several columns, which each <code>IN</code> clause does not. For a larger number of candidates, <code>IN</code> is less verbose.

=== BETWEEN ===
<code>BETWEEN</code> will find any values within a range.
<sourceyntaxhighlight lang="sql">
SELECT ename WHERE ename BETWEEN 'value1' AND 'value2'
</source>
<sourceyntaxhighlight>
<syntaxhighlight lang="sql">
SELECT salary from emp WHERE salary BETWEEN 5000 AND 10000
</sourceyntaxhighlight>

All rows match the predicate if their value is between 'value1' and 'value2', inclusive.

=== LIKE ===
<code>LIKE</code> will find a string fitting a certain description.

* Ending [[Wildcard character|Wildcard]]
** Find any string that begins with the letter 'S'<sourceyntaxhighlight lang="sql">
SELECT ename FROM emp WHERE ename LIKE 'S%';</sourceyntaxhighlight>
* Leading Wildcard 	
** Find any string that ends with the letter 'S'<sourceyntaxhighlight lang="sql">
SELECT ename FROM emp WHERE ename LIKE '%S';</sourceyntaxhighlight>
* Multiple Wildcards 	
** Find any string that contains, anywhere, the letter 'S'<sourceyntaxhighlight lang="sql">
SELECT ename FROM emp WHERE ename LIKE '%S%';</sourceyntaxhighlight>
* Single Character Wildcard 	
** Find any string that contains the letter 'A' followed by any single character followed by the letter 'E'<sourceyntaxhighlight lang="sql">
SELECT ename FROM emp WHERE ename LIKE '%A_E%';</sourceyntaxhighlight>
* Character Classes<ref>''[https://technet.microsoft.com/en-us/library/ms179859.aspx Microsoft Technet]''Retrieved 21 November 2013.</ref>
** Find any string that starts with a letter or number or the symbol '_'<sourceyntaxhighlight lang="sql">
SELECT ename FROM emp WHERE ename LIKE '[a-zA-Z0-9_]%';</sourceyntaxhighlight>
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.

== References ==
<references />

== 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.

{{SQL}}

[[Category:SQL keywords]]