sql_where

SQL WHERE Clause

The WHERE clause is used to filter records from a result set. It specifies a condition that rows must satisfy to be included in the output of a SELECT, UPDATE, or DELETE statement.

The SQL WHERE Clause Explained

When you query a table, you often don’t want all the rows. The WHERE clause allows you to extract only those records that fulfill a specified criterion or set of criteria.

SQL WHERE Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition;

The condition is an expression that evaluates to true, false, or unknown for each row. Only rows for which the condition is true are returned.

Demo Database

For these examples, we’ll use a standard “Customers” table.

Below is a selection from the “Customers” table:

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

WHERE Clause Example

The following SQL statement selects all customers from the country “Mexico” in the “Customers” table:

SELECT *
FROM Customers
WHERE Country = 'Mexico';

This query will return rows 2 and 3 from the sample data.

Text Fields vs. Numeric Fields in Conditions

When specifying conditions in the WHERE clause:

• Text values (strings) must be enclosed in single quotes (e.g., 'Mexico'). Most database systems also allow double quotes, but single quotes are standard SQL practice.
• Numeric values should not be enclosed in quotes (e.g., CustomerID = 1).

Example with a numeric field:

SELECT CustomerName, City
FROM Customers
WHERE CustomerID = 1;

This query will return Alfreds Futterkiste from Berlin.

Operators in The WHERE Clause

You can use various operators in the WHERE clause to define conditions:

Operator	Description
=	Equal to
<>	Not equal to. (Some SQL versions also support !=)
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
BETWEEN	Checks if a value is within an inclusive range (e.g., Price BETWEEN 10 AND 20).
LIKE	Searches for a pattern (often used with wildcards like ’%’ or ’_’).
IN	Checks if a value matches any value in a list (e.g., Country IN ('Germany', 'France', 'UK')).
IS NULL	Checks if a value is NULL.
IS NOT NULL	Checks if a value is not NULL.

You can also combine multiple conditions using logical operators AND, OR, and NOT. These are covered in a separate chapter.

Practical Application:

The WHERE clause is essential for retrieving specific subsets of data, updating targeted rows, or deleting particular records. For example, finding all overdue invoices, updating the status of specific orders, or deleting inactive user accounts.