WHERE Condition in MySQL with 16 Different Query Examples

In this article, we are going to talk about the WHERE clause and how to use it in MySQL. Besides SELECT, the scope of the WHERE clause includes the UPDATE and DELETE statements. The WHERE clause is indispensable for quality work with MySQL databases. That’s why the current article will deal with this clause and explore its distinguishing features in-depth.

When we deal with MySQL, we need conditional clauses for the SELECT statements in the absolute majority of cases. Such clauses determine the selection criteria, so we get only the data we need.

The MySQL WHERE clause is the handiest means of filtering results. It is flexible, allowing the users to specify various conditions for the data selection and combine several conditions in one statement. In order to make your everyday working routine even more pleasant, it is always better to resort to MySQL profiling and query optimization.

Contents

• MySQL WHERE syntax
• Using WHERE in MySQL
• WHERE query single condition example
• Multiple WHERE conditions
• WHERE AND operator
• WHERE OR operator
• Using AND with OR conditions
• WHERE BETWEEN operator
• WHERE LIKE operator
• WHERE NOT LIKE example
• WHERE IN operator
• WHERE NOT IN example
• WHERE IS NULL operator
• WHERE IS NOT NULL example
• WHERE NOT EXISTS
• WHERE EXISTS
• Using MySQL WHERE clause with comparison operators
• Summary

MySQL WHERE syntax

To begin with, let’s take a closer look at using WHERE in the SELECT statement. The basic syntax is as follows:

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

SELECT … FROM is the standard statement defining which columns and which table we target. The WHERE keyword identifies the data selection criteria. There can be a single value, the range between values, the subquery, and more than one condition.

Automate your work with query criteria. See and visually edit them on the separate tabs of our visual MySQL query tool where you’re drawing your query. You don’t need to type the columns’ names or remember complex rules. Just select the required data from the lists and enjoy the results.

Using WHERE in MySQL

MySQL works in the following way when executing the SELECT statement containing the WHERE clause:

1. Evaluate the FROM clause to identify the target table.
2. Proceed to the WHERE clause to evaluate the criteria for data selection.
3. Check the SELECT statement to define the columns to retrieve. 

Note: The SELECT statement often includes the ORDER BY clause that puts the results in ascending or descending order. If so, MySQL will evaluate it in the end.

The principle of work of MySQL WHERE is similar to the IF condition in programming languages. It compares the values we provide with those of the target MySQL table. When these values match, the RDBMS fetches those records to the users. 

When the condition for the WHERE clause is a text value, we put that text into single quotes. However, no quotes are necessary for the numeric values.

SELECT first_name, last_name, city 
FROM Readers
WHERE City = ‘Seattle’;

SELECT * 
FROM Readers
WHERE CustomerID = 24;

Level-up your scripting abilities with the advanced MySQL formatter that allows beautifying your code to get it exactly as you like it.

Now, let us review the usage of the WHERE clause. It involves plenty of specific cases. The best way to explore them is to refer to a practical MySQL WHERE example for each scenario.

WHERE query single condition example

The simplest scenario of using this clause is MySQL WHERE having one single criterion for the data selection. Assume that some company wants to retrieve the list of its employees working in the Customer Care department as support managers:

SELECT 
    last_name, 
    first_name, 
    job_title
FROM
    employees
WHERE
    jobtitle = 'Support';

The query will retrieve data from the employees table and filter the records according to the value in the job_title column. It should return the list of all last and first names of all employees who work as support managers. The output is below:

last_name	first_name	job_title
Fox	Mary	Support
Black	John	Support
Johnson	Steve	Support
Hudson	Diana	Support
Williams	Katie	Support
Green	Jake	Support

Note: Сode less and accomplish more with MySQL syntax checker. dbForge Studio for MySQL is a feature-rich IDE designed to facilitate MySQL and MariaDB database development, maintenance, and administration.

Multiple WHERE conditions

The example provided above proves that the MySQL WHERE clause brings data that matches the condition. In this case, it filtered all those employees having the specified job title. However, we often need to set several criteria to retrieve the data. It is feasible – we need to apply the MySQL WHERE with the specific operators – the AND operator and the OR operator.

WHERE AND operator

Let’s make our previous example a little bit more complicated. We want the list of employees working in the Support department, hired last year.

To accomplish that, we’ll set two conditions and unite them with the AND logical operator:

SELECT 
    last_name, 
    first_name, 
    job_title,
    hire_year
FROM
    employees
WHERE
    jobtitle = 'Support' AND 
    hire_year = 2021;

The output:

last_name	first_name	job_title	hire_year
Fox	Mary	Support	2021
Black	John	Support	2021
Johnson	Steve	Support	2021

Code writing itself takes up lion’s share of the total time you spend on database development and administration. Double your coding speed with the refined and optimized MySQL Server code editor that comes with dbForge Studio for MySQL.

WHERE OR operator

Using the OR logical operator is another method of working with several conditions in the MySQL WHERE clause. If we submit this query, it will return data matching the criteria we set.

The company wants to check the regular customers coming from two cities. For that, we’ll specify those cities and use the OR operator.

SELECT 
    lastName, 
    firstName, 
    city 
FROM
    customers
WHERE
    city = 'Seattle' OR 
    city = 'Bellevue';

last_name	first_name	city
Anderson	Mary	Seattle
Black	John	Seattle
Thompson	Steve	Bellevue
Smith	Diana	Bellevue
Williams	Katie	Seattle
Peters	Jake	Seattle

Using AND with OR conditions

MySQL allows for combining the MySQL WHERE AND OR – both logical operators can be present in a single statement. In this case, MySQL will check three conditions at once.

For instance, the library wants to get information about its readers residing in Bellevue, who became the library’s regular readers in 2021 or have read more than 20 books.

In this case, the MySQL WHERE string becomes more complex. To fit and organize AND & OR conditions, we use the parentheses to determine how MySQL must evaluate them.

Have a look at the example below:

SELECT *
FROM readers
WHERE (city = 'Bellevue' AND reg_year = '2020')
OR (books_read > 20);

The output:

last_name	first_name	city	reg_year	books_read
Anderson	Mary	Seattle	2021	25
Jones	Chris	Bellevue	2020	12
Thompson	Lora	Seattle	2020	35
Farrell	Brian	Bellevue	2021	48
Smith	Jeff	Bellevue	2020	50
Peters	Anna	Bellevue	2020	10
Patterson	John	Bellevue	2020	11

This output shows some results matching the condition of the city of residence + year of registration. It also includes results matching the second condition of having read more than 20 books.

WHERE BETWEEN operator

The BETWEEN operator allows for setting wider limits. Assume that the library wants to get the list of its readers who joined in 2019, 2020, and 2021. Instead of submitting several queries or using the AND operator, we can set the range for the WHERE condition using the BETWEEN operator.

To retrieve the list of readers joining between 2019 and 2022, we submit the following query:

SELECT 
    last_Name, 
    first_Name, 
    reg_year
FROM
    readers
WHERE
    reg_year BETWEEN 2019 AND 2021
ORDER BY reg_year;

The output:

last_name	first_name	reg_year
Anderson	Mary	2019
Jones	Chris	2019
Thompson	Lora	2019
Farrell	Brian	2019
Smith	Jeff	2020
Peters	Anna	2020
Patterson	John	2020
Williams	Mark	2021
Hiks	Andrew	2021
Grant	Valentina	2021

WHERE LIKE operator

Applying the LIKE operator lets you use the WHERE clause to select certain records that include a specific pattern. To define that pattern, we use the percent sign (%) to replace a character or a string of several characters of any length. 

The most common patterns for MySQL SELECT WHERE LIKE are the following: 

WHERE value LIKE 'abc%‘ – finds and returns all values starting with “abc”

WHERE value LIKE '%abc' – finds and returns all values ending with “abc”

WHERE value LIKE '%abc%' – finds and returns values having “abc” anywhere between the start and end

Let’s have a look at the WHERE LIKE example. The company wants to get the list of its customers from the USA and UK. The query is as follows:

SELECT ContactName, Country, City
FROM Customers
WHERE Country LIKE 'U%'
ORDER BY ContactName;

The output is:

ContactName	Country	City
Helen Bennett	UK	Cowes
Simon Crowther	UK	London
Hari Kumar	UK	London
Howard Snyder	USA	Eugene
Yoshi Latimer	USA	Elgin
John Steel	USA	Walla Walla
Jaime Torres	USA	San Francisco
Fran Wilson	USA	Portland

WHERE NOT LIKE example

There is another case where you need to exclude some data from the results. We can use the MySQL WHERE NOT LIKE operator. It finds and returns the data that does not have specific characters. 

The company wants a list of its customers from all countries except for the UK and the USA. The syntax of WHERE NOT LIKE in MySQL will look as below:

SELECT ContactName, Country, City
FROM Customers
WHERE Country NOT LIKE 'U%'
ORDER BY Country;

The output:

ContactName	Country	City
Roland Mendel	Austria	Graz
Georg Pipps	Austria	Salzburg
Catherine Dewey	Belgium	Bruxelles
Pascale Cartrain	Belgium	Charleroi
Elizabeth Lincoln	Canada	Tsawassen
Yoshi Tannamuri	Canada	Vancouver
Jean Fresnière	Canada	Montréal
Jytte Petersen	Denmark	København
Palle Ibsen	Denmark	Århus
Pirkko Koskitalo	Finland	Oulu
Matti Karttunen	Finland	Helsinki
Annette Roulet	France	Toulouse
Marie Bertrand	France	Paris
Maria Anders	Germany	Berlin
Hanna Mooss	Germany	Mannheim

WHERE IN operator

Using the IN and NOT IN keywords is another option to filter the rows and retrieve only a portion of data that matches our strict criteria. The primary purpose is to reduce the number of OR clauses in one SELECT statement. 

MySQL WHERE IN appeals to the rows containing the values we specified for the WHERE condition. For instance, we want to retrieve the list of customers who reside in several European countries. The WHERE IN syntax is as follows in MySQL:

SELECT ContactName, Country, City
FROM Customers
WHERE Country IN ('Belgium', 'France', 'Germany', 'UK')
ORDER BY Country;

Please note that we enclose our values that specify the search condition in parenthesis. In the case of working with text values, we must put them all into single quotes. However, if our values and numerical, we’ll list them without single quotes, e.g., (25, 35, 45).

The output of the above MySQL WHERE IN clause in the SELECT statement is:

ContactName	Country	City
Catherine Dewey	Belgium	Bruxelles
Pascale Cartrain	Belgium	Charleroi
Frédérique Citeaux	France	Strasbourg
Laurence Lebihans	France	Marseille
Janine Labrune	France	Nantes
Martine Rancé	France	Lille
Alexander Feuer	Germany	Leipzig
Henriette Pfalzheim	Germany	Köln
Horst Kloss	Germany	Cunewalde
Karin Josephs	Germany	Münster
Rita Müller	Germany	Stuttgart
Thomas Hardy	UK	London
Victoria Ashworth	UK	London
Elizabeth Brown	UK	Manchester
Ann Devon	UK	London

WHERE NOT IN example

The opposite case is MySQL WHERE NOT IN clause. It is used to make sure that your query will ignore those rows containing the values specified for the condition. In the above example, we requested customers from the 4 definite European countries. The MySQL WHERE NOT IN SELECT query for the same request will return all customers residing in other countries except for those 4 defined earlier.

SELECT ContactName, Country, City
FROM Customers
WHERE Country NOT IN ('Belgium', 'France', 'Germany', 'UK')
ORDER BY Country;

The output:

ContactName	Country	City
Patricio Simpson	Argentina	Buenos Aires
Sergio Gutiérrez	Argentina	Buenos Aires
Brian Citeaux	Canada	Montréal
Laura Smith	Canada	Toronto
Jane Green	Canada	Toronto
Martine Rancé	Canada	Vancouver
Alexander Feuer	Canada	Århus
Paolo Accorti	Italy	Rome
Giovanni Rovelli	Italy	Torino
Anna Moroni	Italy	Pavia

Note: We recommend using [NOT] EXISTS over [NOT] IN since the EXISTS logical operator works faster than IN, when comparing data sets using a subquery. Moreover, NOT EXISTS will avoid unexpected results when the source data contains NULL values.

WHERE IS NULL operator

To check if a value is NULL or not, use the IS NULL operator. The following statement uses the WHERE clause with the IS NULL operator to get the rows without a value in the books_read column:

SELECT last_name, first_name, reg_year, books_read
FROM readers
WHERE books_read IS NULL;

The output will look like this:

last_name	first_name	reg_year	books_read
Janett	Overacker	2020	NULL
Lidia	Swenson	2017	NULL
Albert	Crepeau	2018	NULL
Anastasia	Johnson	2021	NULL
Will	Smith	2017	NULL

WHERE IS NOT NULL example

Unlike the previous command, WHERE IS NOT NULL, on the contrary, shows only those rows that contain some sort of value in the column in question. In the following example, we are going to select only those rows where the books_read column is not empty:

SELECT last_name, first_name, reg_year, books_read
FROM readers
WHERE books_read IS NOT NULL;

In the output, you will see only those rows that contain a value in the books_read column:

last_name	first_name	reg_year	books_read
Cory	Baster	2021	8
Maddison	Winston	2022	3
Isabella	Garsia	2014	45
Jeremy	Koh	2022	1
Aaron	Peterson	2021	15

WHERE EXISTS

The EXISTS operator checks if the subquery returns any value at all. Typically, this operator is used to indicate that at least one row in a table satisfies a certain condition.

In the example below,

SELECT last_name, first_name
FROM customers
WHERE EXISTS 
(SELECT * FROM discounts WHERE customers.id = discounts.customer_id)

WHERE NOT EXISTS

However, the NOT EXISTS operator is there for us in case we want to make sure whether there are rows in the table that do not satisfy a certain condition. For example, let’s find all products from the Products table for which there were no orders in the Orders table:

SELECT last_name, first_name
FROM customers
WHERE NOT EXISTS 
(SELECT * FROM discounts WHERE customers.id = discounts.customer_id)

There is a separate article in our blog for those willing to learn more about the difference between EXISTS and NOT EXISTS in SQL.

Using MySQL WHERE clause with comparison operators

As we know, the WHERE condition value can be any number, text, string, or subquery. Besides specifying the condition strictly, we can set a wider meaning for it by using the comparison operators. One example of a similar function is using the BETWEEN operator. Now, let us review other options to set the condition range.

Operator	Description	Example
=	Equal to the value. It is applicable for almost any data type.	SELECT * FROM VisitorsWHERE City =’London’;
<> or !=	Not equal to. It excludes the rows matching the value from the results.	SELECT * FROM CustomersWHERE Country=’Germany’ AND City !=’Berlin’;
<	Less than. Mostly applies to numbers and date/time.	SELECT * FROM CustomersWHERE CustomerID < 7;
>	Greater than. Works in the same way, for numeric values and date/time.	SELECT * FROM CustomersWHERE CustomerID > 10;
<=	Less than or equal to. The results set includes the rows with the same values or less than the condition value.	SELECT * FROM CustomersWHERE CustomerID <= 5;
>=	Greater than or equal to. The result set includes rows having the same values exceeding the condition values.	SELECT * FROM CustomersWHERE CustomerID >= 55;

In our example, we want to retrieve the list of customers with a customer ID lower than 90 who reside in the USA. The query is the following:

SELECT CustomerID, ContactName, City, Country 
FROM Customers
WHERE CustomerID < 90 AND Country ='USA';

The output is:

CustomerID	ContactName	City	Country
65	Paula Wilson	Albuquerque	USA
55	Rene Phillips	Anchorage	USA
78	Liu Wong	Butte	USA
36	Yoshi Latimer	Elgin	USA
32	Howard Snyder	Eugene	USA
82	Helvetius Nagy	Kirkland	USA
48	Fran Wilson	Portland	USA
77	Liz Nixon	Portland	USA
45	Jaime Torres	San Francisco	USA
89	Karl Jablonski	Seattle	USA
55	Rene Phillips	Anchorage	USA

Summary

As you see, MySQL WHERE is among the most helpful clauses for all MySQL users. The variety of different logical operators lets you focus on the specific portion of data only, filter it correctly, and receive the results you need. Besides, modern professional tools for MySQL databases allow you to perform all tasks faster and easier. They take the burden of manual routines off your shoulders and make the performance much more stable and reliable. To sum up, working with a WHERE statement is not too complicated. However, it requires being familiar with the corresponding commands. The command-line tools are more oriented toward experienced tech-savvy users. On the contrary, dbForge Studio for MySQL can boast an intuitive graphical interface. The intelligent code completion feature will be of use while working with the WHERE clause, navigating the code, obtaining quick object information, and instantly performing syntax checks. Moreover, dbForge Query Builder will be of use to easily build WHERE clauses visually.

Useful Links

• Joins in MySQL
• MySQL update query