The SQL WHERE clause is used to filter rows and return only those that meet the specified criteria. It is used in conjunction with the SELECT, UPDATE, DELETE, etc. statements and takes the following general form:
SELECT column_name [, column_name ...] FROM table_name WHERE condition;
The WHERE clause can be used along with AND, OR, and NOT operators:
- AND and OR – filter records based on more than one condition.
- AND – shows a record if all the conditions separated by AND are TRUE.
- OR – shows a record if any of the conditions separated by OR is TRUE.
- NOT – shows a record if the condition is NOT TRUE.
If multiple operator 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.
In the WHERE clause, the following comparison operators can be used:
|>=||Greater than or equal|
|<=||Less than or equal|
|<>||Not equal. Note: In some versions of SQL this operator may be written as !=|
|BETWEEN||Between a certain range|
|LIKE||Search for a pattern using wildcards|
|IN||To specify multiple possible values for a column|
SQL requires single quotes around text values. However, numeric fields should not be enclosed in quotes.
Consider the Products table having the following records:
|3||Gloss Coffee Table||34||240.10|
SELECT * FROM Products WHERE product LIKE ‘%Table’ AND quantity < 20;
Will produce the following table, displaying all columns with products that contain the keyword ‘Table’ and have quantity less than 20: