What is the WHERE Clause in MySQL?
MySQL WHERE Clause: The WHERE clause in MySQL is used to filter records based on specific conditions in SQL queries. It can be applied to SELECT, UPDATE, DELETE, and INSERT statements to retrieve only the required data.
When querying a database, you may not always want to retrieve all rows. The WHERE clause helps narrow down results based on given conditions.
WHERE Clause Syntax
MySQL WHERE Clause: The basic syntax of the WHERE clause in a SELECT query is:
sql
SELECT * FROM tableName WHERE condition;
- SELECT * FROM tableName: Fetches all records from a table.
- WHERE condition: Filters records based on the given condition.
Example: To retrieve details of a specific member with membership_number = 1:
sql
SELECT * FROM members WHERE membership_number = 1;
Using WHERE with Logical Operators
1. WHERE Clause with AND Operator
MySQL WHERE Clause: The AND operator is used to filter data when multiple conditions must be met.
Example: Retrieve movies in category 2 that were released in 2008.
sql
SELECT * FROM movies WHERE category_id = 2 AND year_released = 2008;
Output:
| movie_id | title | director | year_released | category_id |
| 2 | Forgetting Sarah Marshal | Nicholas Stoller | 2008 | 2 |
2. WHERE Clause with OR Operator
MySQL WHERE Clause: The OR operator returns results if at least one condition is true.
Example: Fetch all movies from category 1 or 2.
sql
SELECT * FROM movies WHERE category_id = 1 OR category_id = 2;
Output:
| movie_id | title | director | year_released | category_id |
| 1 | Pirates of the Caribbean 4 | Rob Marshall | 2011 | 1 |
| 2 | Forgetting Sarah Marshal | Nicholas Stoller | 2008 | 2 |
3. WHERE Clause with IN Operator
MySQL WHERE Clause: The IN operator simplifies multiple OR conditions by checking if a value exists in a given list.
Example: Fetch members with membership_number 1, 2, or 3.
sql
SELECT * FROM members WHERE membership_number IN (1, 2, 3);
Output:
| membership_number | full_names | gender | date_of_birth | physical_address | |
| 1 | Janet Jones | Female | 21-07-1980 | First Street Plot No 4 | janetjones@yagoo.cm |
| 2 | Janet Smith Jones | Female | 23-06-1980 | Melrose 123 | jj@fstreet.com |
| 3 | Robert Phil | Male | 12-07-1989 | 3rd Street 34 | rm@tstreet.com |
4. WHERE Clause with NOT IN Operator
MySQL WHERE Clause: The NOT IN operator returns rows that do not match the values listed.
Example: Fetch members excluding those with membership_number 1, 2, or 3.
sql
SELECT * FROM members WHERE membership_number NOT IN (1, 2, 3);
Output:
| membership_number | full_names | gender | date_of_birth | physical_address | |
| 4 | Gloria Williams | Female | 14-02-1984 | 2nd Street 23 | NULL |
Using WHERE Clause with Comparison Operators
1. WHERE with Equal to (=) Operator
MySQL WHERE Clause:: Retrieve all female members.
sql
SELECT * FROM members WHERE gender = ‘Female’;
Output:
| membership_number | full_names | gender | date_of_birth | physical_address | |
| 1 | Janet Jones | Female | 21-07-1980 | First Street Plot No 4 | janetjones@yagoo.cm |
| 2 | Janet Smith Jones | Female | 23-06-1980 | Melrose 123 | jj@fstreet.com |
2. WHERE with Greater than (>) Operator
Fetch all payments greater than 2,000.
sql
SELECT * FROM payments WHERE amount_paid > 2000;
Output:
| payment_id | membership_number | payment_date | description | amount_paid |
| 1 | 1 | 23-07-2012 | Movie rental payment | 2500 |
| 3 | 3 | 30-07-2012 | Movie rental payment | 6000 |
3. WHERE with Not Equal to (<>) Operator
Retrieve all movies that do not belong to category 1.
sql
SELECT * FROM movies WHERE category_id <> 1;
Output:
| movie_id | title | director | year_released | category_id |
| 2 | Forgetting Sarah Marshal | Nicholas Stoller | 2008 | 2 |
| 5 | Daddy’s Little Girls | NULL | 2007 | 8 |
Brain Teaser: Find Overdue Movie Rentals
Retrieve a list of movies that were not returned by 25th June 2012.
sql
SELECT * FROM movierentals WHERE return_date < ‘2012-06-25’ AND movie_returned = 0;
Output:
| reference_number | transaction_date | return_date | membership_number | movie_id | movie_returned |
| 14 | 21-06-2012 | 24-06-2012 | 2 | 2 | 0 |
Conclusion
- The WHERE clause filters records based on conditions in SELECT, UPDATE, and DELETE queries.
- Logical operators like AND and OR help refine filtering.
- The IN and NOT IN operators simplify multiple OR conditions.
- Comparison operators like =, >, <, and <> help set precise conditions.
By mastering the WHERE clause, you can efficiently manage and retrieve data in MySQL.
