Where (SQL)

From Wikipedia, the free encyclopedia

This article does not cite any references or sources. (April 2007) Please help improve this article by adding citations to reliable sources. Unverifiable material may be challenged and removed.

A WHERE clause in SQL specifies that a SQL Data Manipulation Language (DML) statement should only affect rows that meet a specified criteria. The criteria is expressed in 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.

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 remove only those rows from table mytable where the column mycol is either NULL or has a value than is equal to 100.

 DELETE
 FROM   mytable
 WHERE  mycol IS NULL OR mycol = 100

[edit] Predicates

Predicates do not only have to be simple comparisons but can be a combination of multiple predicates. The keywords AND and OR can be used to combine two predicates into a new one. If multiple combinations are applied, parenthesis can be used to group combinations. Without parenthesis, 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'

v • d • e Database management systems Database models · Database normalization · Database storage · Distributed DBMS · Referential integrity · Relational algebra · Relational calculus · Relational database · Relational DBMS · Relational model · Object-relational database · Transaction processing Concepts Database · ACID · Null · Candidate key · Foreign key · Primary key · Superkey · Surrogate key Objects Trigger · View · Table · Cursor · Log · Transaction · Index · Stored procedure · Partition SQL Select · Insert · Update · Merge · Delete · Join · Union · Create · Drop · Begin work · Commit · Rollback · Truncate · Alter Implementations Relational · Flat file · Deductive · Dimensional · Hierarchical · Network · Document-oriented · Object-oriented · Object-relational · Temporal · XML data stores · Triple stores Components Concurrency control · Data dictionary · JDBC · ODBC · Query language · Query optimizer · Query plan Database products: Object-oriented (comparison) · Relational (comparison) · Document-oriented