Where (SQL)

This article needs additional citations for verification. Please help improve this article by adding citations to reliable sources. Unsourced material may be challenged and removed.
Find sources: "Where" SQL – news · newspapers · books · scholar · JSTOR (September 2014) (Learn how and when to remove this message)

|

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.[1]

## Overview

[edit]

`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) 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 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

[edit]

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

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

### IN

[edit]

`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

[edit]

`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

[edit]

`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%';

* Character classes[2]
* Find any string that starts with a letter or number or the symbol '_'

SELECT ename FROM emp WHERE ename LIKE '[a-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.

### SIMILAR TO

[edit]

This one is used in PostgresSQL that supports regular expressions with the following syntax:[3]

string [NOT] SIMILAR TO pattern [ESCAPE escape-character]

It works similarly to LIKE statement mentioned above.

## References

[edit]

1. ^ "SQL WHERE Clause – Things beginners must know".
2. ^ Microsoft TechnetRetrieved 21 November 2013.
3. ^ "9.7. Pattern Matching". PostgreSQL Documentation. 2023-05-11. Retrieved 2023-06-10.

## External links

[edit]

* PSOUG Home Puget Sound Oracle Users Group gives several examples of SELECT statements with WHERE clauses.

* v
* t
* e

SQL

Versions|

* SEQUEL
* SQL-86
* SQL-89
* SQL-92
* SQL:1999
* SQL:2003
* SQL:2006
* SQL:2008
* SQL:2011
* SQL:2016
* SQL:2023

Keywords|

* As
* Case
* Create
* Delete
* From
* Group by
* Having
* Insert
* Join
* Merge
* Null
* Order by
* Over
* Prepare
* Select
* Truncate
* Union
* Update
* Where
* With

Related|

* Edgar Codd
* Relational database

ISO/IEC SQL parts|

* Framework
* Foundation
* Call-Level Interface
* Persistent Stored Modules
* Management of External Data
* Object Language Bindings
* Information and Definition Schemas
* SQL Routines and Types for the Java Programming Language
* XML-Related Specifications

*[v]: View this template
*[t]: Discuss this template
*[e]: Edit this template