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:

Operator Description
= Equal
>  Greater than
<  Less than
>= 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.

Example:

Consider the Products table having the following records:

id product quantity price
1 Glass Table 10 899.99
2 Oak Desk 28 530.50
3 Gloss Coffee Table 34 240.10
4 Oak Table 10 450.90
5 Birch Chair 8 60.80
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:

id product quantity price
1 Glass Table 10 899.99
4 Oak Table 10 450.90

Was this article helpful?