MySQL WHERE Clause: AND, OR, IN, NOT IN Query Example

We looked at how to query data from a database using the SELECT statement in the previous tutorial. The SELECT statement returned all the results from the queried database table.

There are, however, times when we want to restrict the query results to a specified condition. The WHERE clause in SQL comes in handy in such situations, and it works the same way in a DELETE or UPDATE query as it does in a SELECT query.

WHERE clause Syntax

The basic syntax of the WHERE clause is as follows.

SELECT * FROM tableName WHERE condition;

HERE

• “SELECT * FROM tableName” is the standard SELECT statement
• “WHERE” is the keyword that restricts the result set, and “condition” is the filter applied to the results. The filter may be a range, a single value or a sub query.

Suppose we want a member’s details from the members table, given membership number 1. The script below achieves that.

SELECT * FROM `members` WHERE `membership_number` = 1;

Executing this script in MySQL workbench on the “myflixdb” produces the result below.

membership_number
full_names
gender
date_of_birth
physical_address
postal_address
contct_number
email




1
Janet Jones
Female
21-07-1980
First Street Plot No 4
Private Bag
0759 253 542
janetjones@yagoo.cm

A single condition is rarely enough. The sections below combine WHERE with logical operators.

WHERE clause combined with – AND LOGICAL Operator

With the AND logical operator, a row is returned only if ALL specified criteria are met.

Suppose we want all the movies in category 2 that were released in 2008. The script below achieves that.

SELECT * FROM `movies` WHERE `category_id` = 2 AND `year_released` = 2008;

Executing the above script against the “myflixdb” produces the following result.

movie_id
title
director
year_released
category_id




2
Forgetting Sarah Marshal
Nicholas Stoller
2008
2

AND narrows a result set. OR widens it.

WHERE clause combined with – OR LOGICAL Operator

With the OR operator, a row is returned if ANY of the specified criteria is met.

The following script gets all the movies in either category 1 or category 2.

SELECT * FROM `movies` WHERE `category_id` = 1 OR `category_id` = 2;

The result set is shown below.

movie_id
title
director
year_released
category_id




1
Pirates of the Caribean 4
Rob Marshall
2011
1


2
Forgetting Sarah Marshal
Nicholas Stoller
2008
2

Chaining many OR conditions becomes hard to read. The IN keyword solves that.

WHERE clause combined with – IN Keyword

The WHERE clause, when used with the IN keyword, only affects rows whose values match the supplied list. IN reduces the number of OR clauses you would otherwise write.

The following MySQL WHERE IN query gives rows where membership_number is either 1, 2 or 3.

SELECT * FROM `members` WHERE `membership_number` IN (1,2,3);

The result set is shown below.

membership_number
full_names
gender
date_of_birth
physical_address
postal_address
contct_number
email




1
Janet Jones
Female
21-07-1980
First Street Plot No 4
Private Bag
0759 253 542
janetjones@yagoo.cm


2
Janet Smith Jones
Female
23-06-1980
Melrose 123
NULL
NULL
jj@fstreet.com


3
Robert Phil
Male
12-07-1989
3rd Street 34
NULL
12345
rm@tstreet.com

NOT IN performs the opposite filter.

WHERE clause combined with – NOT IN Keyword

With the NOT IN keyword, the WHERE clause excludes every row whose value matches the supplied list.

The following query gives rows where membership_number is NOT 1, 2 or 3.

SELECT * FROM `members` WHERE `membership_number` NOT IN (1,2,3);

The result set is shown below.

membership_number
full_names
gender
date_of_birth
physical_address
postal_address
contct_number
email




4
Gloria Williams
Female
14-02-1984
2nd Street 23
NULL
NULL
NULL

WHERE also filters values by size and equality, using comparison operators.

WHERE clause combined with – COMPARISON Operators

The less than (<), equal to (=), greater than (>) and not equal to (<>) comparison operators can all be used with the WHERE clause.

= Equal To

The following script gets all the female members from the members table using the equal to comparison operator.

SELECT * FROM `members` WHERE `gender` = 'Female';

The result set is shown below.

membership_number
full_names
gender
date_of_birth
physical_address
postal_address
contct_number
email




1
Janet Jones
Female
21-07-1980
First Street Plot No 4
Private Bag
0759 253 542
janetjones@yagoo.cm


2
Janet Smith Jones
Female
23-06-1980
Melrose 123
NULL
NULL
jj@fstreet.com


4
Gloria Williams
Female
14-02-1984
2nd Street 23
NULL
NULL
NULL

> Greater than

The following script gets all the payments that are greater than 2,000 from the payments table.

SELECT * FROM `payments` WHERE `amount_paid` > 2000;

The result set is shown below.

payment_id
membership_number
payment_date
description
amount_paid
external_reference_number




1
1
23-07-2012
Movie rental payment
2500
11


3
3
30-07-2012
Movie rental payment
6000
NULL

<> Not Equal To

The following script gets all the movies whose category id is not 1.

SELECT * FROM `movies` WHERE `category_id` <> 1;

The result set is shown below.

movie_id
title
director
year_released
category_id




2
Forgetting Sarah Marshal
Nicholas Stoller
2008
2


5
Daddy's Little Girls
NULL
2007
8


6
Angels and Demons
NULL
2007
6


7
Davinci Code
NULL
2007
6


9
Honey mooners
John Schultz
2005
8

Quick Reference: WHERE Clause Operators

The operators most often paired with WHERE are summarised below. Full definitions appear in the MySQL operator reference.

⚠️ Note: NULL never matches = or <>. Use IS NULL or IS NOT NULL when a column may hold no value.

Brain Teaser: Find Movies Returned Late

Suppose we want the rented movies that were not returned by the 25/06/2012 cut-off date. Combining WHERE with the less than operator and AND achieves that.

SELECT * FROM `movierentals` WHERE `return_date` < '2012-06-25' AND movie_returned = 0;

Executing the above script in MySQL workbench gives the following results.

reference_number
transaction_date
return_date
membership_number
movie_id
movie_returned


14
21-06-2012
24-06-2012
2
2
0

FAQs