MySQL WHERE Clause: AND, OR, IN, NOT IN Query Example
Marcus Allen
What is WHERE Clause in MySQL?
WHERE Clause in MySQL is a keyword used to specify the exact criteria of data or rows that will be affected by the specified SQL statement. The WHERE clause can be used with SQL statements like INSERT, UPDATE, SELECT, and DELETE to filter records and perform various operations on the data.
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.
They are however, times when we want to restrict the query results to a specified condition. The WHERE clause in SQL comes handy in such situations.
WHERE clause Syntax
The basic syntax for the WHERE clause when used in a MySQL SELECT WHERE statement is as follows.
SELECT * FROM tableName WHERE condition;
HERE
- “SELECT * FROM tableName” is the standard SELECT statement
- “WHERE” is the keyword that restricts our select query result set and “condition” is the filter to be applied on the results. The filter could be a range, single value or sub query.
Let’s now look at a practical example.
Suppose we want to get a member’s personal details from members table given the membership number 1, we would use the following script to achieve that.
SELECT * FROM `members` WHERE `membership_number` = 1;
Executing the above script in MySQL workbench on the “myflixdb” would produce the following results.
| membership_number | full_names | gender | date_of_birth | physical_address | postal_address | contct_number | |
|---|---|---|---|---|---|---|---|
| 1 | Janet Jones | Female | 21-07-1980 | First Street Plot No 4 | Private Bag | 0759 253 542 | janetjones@yagoo.cm |
WHERE clause combined with – AND LOGICAL Operator
The WHERE condition in MySQL when used together with the AND logical operator, is only executed if ALL filter criteria specified are met.
SELECT * FROM `movies` WHERE `category_id` = 2 AND `year_released` = 2008;
| movie_id | title | director | year_released | category_id |
|---|---|---|---|---|
| 2 | Forgetting Sarah Marshal | Nicholas Stoller | 2008 | 2 |
WHERE clause combined with – OR LOGICAL Operator
SELECT * FROM `movies` WHERE `category_id` = 1 OR `category_id` = 2;
| 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 |
WHERE clause combined with – IN Keyword
SELECT * FROM `members` WHERE `membership_number` IN (1,2,3);
Executing the above script in MySQL workbench against the “myflixdb” produces the following results.
| membership_number | full_names | gender | date_of_birth | physical_address | postal_address | contct_number | |
|---|---|---|---|---|---|---|---|
| 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 |
WHERE clause combined with – NOT IN Keyword
SELECT * FROM `members` WHERE `membership_number` NOT IN (1,2,3);
Executing the above script in MySQL workbench against the “myflixdb” produces the following results.
| membership_number | full_names | gender | date_of_birth | physical_address | postal_address | contct_number | |
|---|---|---|---|---|---|---|---|
| 4 | Gloria Williams | Female | 14-02-1984 | 2nd Street 23 | NULL | NULL | NULL |
WHERE clause combined with – COMPARISON Operators
= Equal To
SELECT * FROM `members` WHERE `gender` = 'Female';
Executing the above script in MySQL workbench against the “myflixdb” produces the following results.
| membership_number | full_names | gender | date_of_birth | physical_address | postal_address | contct_number | |
|---|---|---|---|---|---|---|---|
| 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
| 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
SELECT * FROM `movies` WHERE `category_id`<> 1;
| 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 |
Summary
- The SQL WHERE clause is used to restrict the number of rows affected by a SELECT, UPDATE or DELETE query.
- The WHERE condition in SQL can be used in conjunction with logical operators such as AND and OR, comparison operators such as ,= etc.
- When used with the AND logical operator, all the criteria must be met.
- When used with the OR logical operator, any of the criteria must be met.
- The key word IN is used to select rows matching a list of values.
Brain Teaser
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 |