(SQL examples for Beginners)
In this end-to-end example, you will learn – SQL Tutorials for Business Analyst: MySQL WHERE Clause with Examples – AND, OR, IN, NOT IN.
MySQL WHERE Clause with Examples – AND, OR, IN, NOT IN
What is the WHERE Clause?
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 SQL WHERE clause comes in handy in such situations.
WHERE clause Syntax
The basic syntax for the WHERE clause when used in a SELECT 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 clause 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;
Executing the above script in MySQL workbench against the “myflixdb” produces the following results.
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;
Executing the above script in MySQL workbench against the “myflixdb” produces the following results.
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 clause 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.
SELECT * FROM `movierentals` WHERE `return_date` < '2012-06-25' AND movie_returned = 0;
reference_number | transaction_date | return_date | membership_number | movie_id | movie_returned |
---|---|---|---|---|---|
14 | 21-06-2012 | 24-06-2012 | 2 | 2 | 0 |
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.
Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners
Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:
All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R.
End-to-End Python Machine Learning Recipes & Examples.
End-to-End R Machine Learning Recipes & Examples.
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.
MySQL Tutorials for Business Analyst: MySQL Workbench Tutorial and MySQL Introduction