WHERE Condition in MySQL with 16 Different Query Examples

April 11th, 2022

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

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

Comments are closed.