Where (SQL)

AWHEREclause inSQLspecifies that a SQLData Manipulation Language (DML)statement should only affectrowsthat meet specified criteria. The criteria are expressed in the form of predicates.WHEREclauses 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,orDELETEstatement.

Overview

WHEREis anSQLreserved word.

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

SQL-DML-Statement
FROMtable_name
WHEREpredicate

all rows for which the predicate in theWHEREclause 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 tablemytablewhere the value incolumnmycolis greater than 100.

SELECT*
FROMmytable
WHEREmycol>100

The followingDELETEstatementremoves only those rows from tablemytablewhere the columnmycolis either NULL or has a value that is equal to 100.

DELETE
FROMmytable
WHEREmycolISNULLORmycol=100

Predicates

Simple predicates use one of the operators=,<>,>,>=,<,<=,IN,BETWEEN,LIKE,IS NULLorIS NOT NULL.

Predicates can be enclosed in parentheses if desired. The keywordsANDandORcan 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, theANDoperator has a stronger binding thanOR.

The following example deletes rows frommytablewhere the value ofmycolis greater than 100,andthe value ofitemis equal to thestring literal'Hammer':

DELETE
FROMmytable
WHEREmycol>100ANDitem='Hammer'

IN

INwill find any values existing in a set of candidates.

SELECTenameWHEREenameIN('Montreal','Quebec')

All rows match the predicate if their value is one of the candidate set of values. This is the same behavior as

SELECTenameWHEREename='value1'ORename='value2'

except that the latter could allow comparison of several columns, which eachINclause does not. For a larger number of candidates,INis less verbose.

BETWEEN

BETWEENwill find any values within a range.

SELECTenameWHEREenameBETWEEN'value1'AND'value2'

SELECTsalaryfromempWHEREsalaryBETWEEN5000AND10000

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

LIKE

LIKEwill find a string fitting a certain description.

• Endingwildcard
  • Find any string that begins with the letter 'S'

    SELECTenameFROMempWHEREenameLIKE'S%';
    

• Leading wildcard
  • Find any string that ends with the letter 'S'

    SELECTenameFROMempWHEREenameLIKE'%S';
    

• Multiple wildcards
  • Find any string that contains, anywhere, the letter 'S'

    SELECTenameFROMempWHEREenameLIKE'%S%';
    

• Single character wildcard
  • Find any string that contains the letter 'A' followed by any single character followed by the letter 'E'

    SELECTenameFROMempWHEREenameLIKE'%A_E%';
    

• Character classes
  • Find any string that starts with a letter or number or the symbol '_'

    SELECTenameFROMempWHEREenameLIKE'[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

This one is used inPostgresSQLthat supportsregular expressionswith the following syntax:

string[NOT]SIMILARTOpattern[ESCAPEescape-character]

It works similarly to LIKE statement mentioned above.

External links

• PSOUG Home Puget Sound Oracle Users Groupgives several examples of SELECT statements with WHERE clauses.