Menu

MySQL WHERE clause

Filter MySQL rows with WHERE; combine conditions using AND, OR, and NOT, and compare values with IN, LIKE, BETWEEN, and IS NULL.

By default, a SELECT statement retrieves rows from a table. Use WHERE to return only rows that meet a condition.

WHERE clauses syntax

The WHERE clause allows you to specify a search condition for SELECT statement. The following is the syntax of the WHERE clause:

SELECT columns_list
FROM table_name
WHERE query_condition;

Here:

  • query_condition is a Boolean expression that evaluates to 1 (TRUE), 0 (FALSE), or NULL.
  • Combine conditions with AND, OR, and NOT.
  • MySQL evaluates the condition for each row and returns rows for which it is TRUE.

The most used case is testing whether a column has a specified value. Just as the following:

column_name = value

You can use WHERE clause not only in SELECT statement but also in UPDATE and DELETE statements.

Comparison Operators

The following table lists some comparison operators.

Operator Description Examples
= Equal to. name = 'Jim'
<> Not equal to. name <> 'Jim'
!= Not equal to. name != 'Jim'
> Greater than. age > 16
< Less than. age < 18
>= Greater than or equal to. age >= 17
<= Less than or equal to. age <= 17
BETWEEN Test a value between A and B age BETWEEN 18 AND 20
IN Test a value in a list. age IN (18, 19, 20)
LIKE Test a string matches a pattern. name LIKE 'Jim%'
EXISTS Test whether a subquery returns a row. EXISTS (SELECT 1 FROM users)
IS NULL Test whether a column is NULL. age IS NULL

Note: In SQL, the equality operator is =, not ==. This is not the same as some common programming languages.

Logical Operators

The following table lists some logical operators.

Operator Examples
AND age >= 16 AND age <= 18
OR age < 16 OR age > 18
NOT age NOT IN (18, 19, 20)

Examples

In the following example, we will use the actor table from the Sakila sample database for the demonstration.

The following is the definition of the actor table:

+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| actor_id    | smallint unsigned | NO   | PRI | NULL              | auto_increment                                |
| first_name  | varchar(45)       | NO   |     | NULL              |                                               |
| last_name   | varchar(45)       | NO   | MUL | NULL              |                                               |
| last_update | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+

Use equality operation

The following query uses the WHERE clause to find all actors whose last names are ALLEN:

SELECT *
FROM actor
WHERE last_name = 'ALLEN'

In this statement, last_name = 'ALLEN' means that the row’s last_name column’s value must be equal to ALLEN.

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      118 | CUBA       | ALLEN     | 2006-02-15 04:34:33 |
|      145 | KIM        | ALLEN     | 2006-02-15 04:34:33 |
|      194 | MERYL      | ALLEN     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
17 rows in set (0.00 sec)

This statement returns all actors whose last names are ALLEN from the actor table.

Using AND

The following query finds actors whose last name is DAVIS and first name is SUSAN:

SELECT *
FROM actor
WHERE last_name = 'DAVIS' AND first_name = 'SUSAN';

In this statement, last_name = 'DAVIS' AND first_name = 'SUSAN' means that the row’s last_name column has a value DAVIS, and the row’s first_name has a value SUSAN.

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      101 | SUSAN      | DAVIS     | 2006-02-15 04:34:33 |
|      110 | SUSAN      | DAVIS     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
2 rows in set (0.00 sec)

In the actor table, 2 rows meet the query conditions.

Using OR

The following query finds actors whose last name is either ALLEN or DAVIS:

SELECT *
FROM actor
WHERE last_name = 'ALLEN' OR last_name = 'DAVIS';

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      118 | CUBA       | ALLEN     | 2006-02-15 04:34:33 |
|      145 | KIM        | ALLEN     | 2006-02-15 04:34:33 |
|      194 | MERYL      | ALLEN     | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS     | 2006-02-15 04:34:33 |
|      101 | SUSAN      | DAVIS     | 2006-02-15 04:34:33 |
|      110 | SUSAN      | DAVIS     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

In this statement, query last_name = 'ALLEN' OR last_name = 'DAVIS' means that the row’s last_name column has a value ALLEN or DAVIS.

Conclusion

The article described basic syntax of the WHERE clause, and how to use the WHERE clause to filter data. The main points of the WHERE clause are as follows:

  • The SELECT statements using the WHERE clause to filter data.
  • You can use multiple comparison operators in the WHERE clause.
  • You can use the logical operators AND, OR and NOT to combine a variety of expressions.
  • You can use the WHERE clause in UPDATE and DELETE statements.