Flash cards
Review the key moves
What is the main idea behind SQL WHERE Clause?
Lesson checks
Practice each idea before moving on
Short Mimo-style checks built from this lesson's code, terms, and sequence.
Which statement best captures the main point of this lesson?
Complete the missing token from the example code.
___ * FROM CustomersPut the learning moves in the order that makes the concept easiest to apply.
The SQL WHERE Clause
The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfill a specific condition.
Example
SELECT * FROM Customers
WHERE Country = 'Mexico';WHERE Syntax
SELECT column1 , column2, ... FROM table_name WHERE condition ;
Note
The WHERE clause is not only used in SELECT statements, it is also used in UPDATE , DELETE , etc.
Demo Database
Below is a selection from the Customers table used in the examples:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno TaquerÃa | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
Text Fields vs. Numeric Fields
SQL requires single quotes around text values (most database systems will also allow double quotes).
However, numeric fields should not be enclosed in quotes:
Example
SELECT * FROM Customers
WHERE CustomerID = 1;Operators in The WHERE Clause
You can use other operators than the = operator to filter the search.
Example
SELECT * FROM Customers
WHERE CustomerID > 80;The following operators can be used in the WHERE clause:
| Operator | Description | Example |
|---|---|---|
| = | 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 | |
| IN | To specify multiple possible values for a column |