MySQL WHERE clause

By default, SELECT statement retrieves all rows from the table. If you want to query the rows meet some conditions, you can use WHERE clause.

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:

• The query_condition is a Boolean expression which return 1 (TRUE), 0 (FALSE) or NULL.
• You can combine one or more Boolean expression using the logical operators AND, OR and NOT.
• MySQL will operate the query_condition expression on the rows one by one. If the query_condition return 1 , the involved row will be returned.

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.

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.

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 uses the WHERE clause to find all actors whose last names are ALLEN and first names are 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 uses the WHERE clause to find all actors whose last names are ALLEN or SUSAN:

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.