• Source: Where (SQL)

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:

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.

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.




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




= IN

=
IN will find any values existing in a set of candidates.

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

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.

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
Find any string that begins with the letter 'S'
Leading wildcard
Find any string that ends with the letter 'S'
Multiple wildcards
Find any string that contains, anywhere, the letter 'S'
Single character wildcard
Find any string that contains the letter 'A' followed by any single character followed by the letter 'E'
Character classes
Find any string that starts with a letter or number or the symbol '_'
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 in PostgresSQL that supports regular expressions with the following syntax:It works similarly to LIKE statement mentioned above.




References






External links


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

Kata Kunci Pencarian:

• SQL
• MySQL
• Daftar kata yang dilindungi di SQL
• Injeksi SQL
• Pola active record
• X (media sosial)
• SQLAlchemy
• Oracle Corporation
• Microsoft
• Graph database
• Where (SQL)
• SQL
• SQL injection
• Null (SQL)
• Where
• Microsoft SQL Server
• Join (SQL)
• SQL syntax
• Data definition language
• Transact-SQL