--- description: MySQL Where clause' is used to query data from a database and also used with operators like 'OR', 'AND', IN, NOT IN. title: "MySQL WHERE Clause: AND, OR, IN, NOT IN Query Example" image: https://www.guru99.com/images/mysql-where-clause.png --- [Skip to content](#main) **⚡ Smart Summary** MySQL WHERE Clause filters rows before they reach the result set, applying precise criteria to SELECT, UPDATE, and DELETE statements. Understanding AND, OR, IN, NOT IN, and comparison operators lets beginners retrieve exactly the records a query requires. - 🔍 **Core Purpose:** The WHERE clause restricts which rows a statement touches, so SELECT, UPDATE, and DELETE affect only matching rows. - 🔗 **AND Operator:** Rows are returned only when every condition evaluates to true, narrowing the result set. - 🔀 **OR Operator:** Rows are returned when any single condition evaluates to true, widening the result set. - 📋 **IN and NOT IN:** The IN keyword matches a list of values in one expression, while NOT IN excludes that same list. - ⚖️ **Comparison Operators:** Equal to (=), greater than (>), less than (<), and not equal to (<>) filter numeric, text, and date columns. - ⚠️ **NULL Handling:** NULL never matches = or <>, so IS NULL and IS NOT NULL are required to test for missing values. Read More ![](https://www.guru99.com/images/mysql-where-clause.png) ## 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](https://www.guru99.com/introduction-to-database-sql.html) 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](https://www.guru99.com/delete-and-update.html) as it does in a SELECT query. [![WHERE clause in MySQL]()](https://www.guru99.com/images/where_toys.png) ### 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](https://www.guru99.com/select-statement.html) - **“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](https://www.guru99.com/introduction-to-mysql-workbench.html) 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](https://www.guru99.com/vba-logical-operators.html). ## 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. **Don't Miss:** - [MySQL Certification: Exam Guide, Cost, Eligibility](https://www.guru99.com/mysql-certification-guide.html) - [MySQL IS NULL & IS NOT NULL with Examples](https://www.guru99.com/null.html) - [What is ER Modeling? Learn with Example](https://www.guru99.com/er-modeling.html) - [DBMS Normalization: 1NF, 2NF, 3NF Database Example](https://www.guru99.com/database-normalization.html) ## 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](https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html). | Operator | Meaning | Example condition | | --- | --- | --- | | = | Equal to | \`gender\` = ‘Female’ | | <> | Not equal to | \`category\_id\` <> 1 | | > / < | Greater than / less than | \`amount\_paid\` > 2000 | | AND / OR | All criteria / any criteria | \`category\_id\` = 2 AND \`year\_released\` = 2008 | | IN / NOT IN | Matches / excludes a list | \`membership\_number\` IN (1,2,3) | | IS NULL / IS NOT NULL | Tests for missing values | \`postal\_address\` IS NULL | **⚠️ 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 ⚡ What is the difference between the WHERE and HAVING clauses? WHERE filters individual rows before grouping, so it cannot reference aggregate functions. HAVING filters groups after GROUP BY runs, making it the right place for conditions such as COUNT(\*) > 5. 🔎 How do I filter NULL values in a WHERE clause? Use IS NULL or IS NOT NULL. Operators such as = and <> return unknown for NULL, so \`postal\_address\` = NULL matches nothing, while \`postal\_address\` IS NULL works. ⚡ Does the WHERE clause make queries faster? Yes, when the filtered column is indexed, MySQL reads only matching rows instead of scanning the table. Wrapping a column in a function, such as YEAR(\`date\_of\_birth\`) = 1980, prevents index use. 🤖 Can AI tools write MySQL WHERE conditions for me? Yes. AI assistants turn plain English into SQL, and [MySQL Workbench](https://www.guru99.com/introduction-to-mysql-workbench.html) can run the result. Review every condition against your schema, because a model may invent column names. 🛡️ Is it safe to run an AI-generated WHERE clause on live data? Test it with SELECT first. A wrong WHERE clause on a [DELETE or UPDATE query](https://www.guru99.com/delete-and-update.html) can alter every row. Preview the matched rows, then reuse the same condition for the write. #### Summarize this post with: ChatGPTPerplexityGrokGoogle AI **Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** ##### Sign up for the newsletter Subscribe for Free You have successfully subscribed. Please check your inbox. ![AI-Newsletter]() Chosen by over **350,000+** professionals [Scroll to top](#wrapper) × Search for: ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/mysql-where-clause.png","url":"https://www.guru99.com/images/mysql-where-clause.png","width":"700","height":"250","caption":"MySQL WHERE Clause","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/where-clause.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/sql","name":"SQL"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/where-clause.html","name":"MySQL WHERE Clause: AND, OR, IN, NOT IN Query Example"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/where-clause.html#webpage","url":"https://www.guru99.com/where-clause.html","name":"MySQL WHERE Clause: AND, OR, IN, NOT IN Query Example","dateModified":"2026-10-07T16:13:34+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/mysql-where-clause.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/where-clause.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/editorial","name":"Editorial Team","url":"https://www.guru99.com/author/editorial","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/favicon-new-logo.png","url":"https://www.guru99.com/images/favicon-new-logo.png","caption":"Editorial Team","inLanguage":"en-US"},"sameAs":["https://www.guru99.com/editorial-team"],"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"SQL","headline":"MySQL WHERE Clause: AND, OR, IN, NOT IN Query Example","description":"MySQL Where clause' is used to query data from a database and also used with operators like 'OR', 'AND', IN, NOT IN.","keywords":"sql","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/editorial","name":"Editorial Team"},"dateModified":"2026-10-07T16:13:34+05:30","image":{"@id":"https://www.guru99.com/images/mysql-where-clause.png"},"copyrightYear":"2026","name":"MySQL WHERE Clause: AND, OR, IN, NOT IN Query Example","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"What is the difference between the WHERE and HAVING clauses?","acceptedAnswer":{"@type":"Answer","text":"WHERE filters individual rows before grouping, so it cannot reference aggregate functions. HAVING filters groups after GROUP BY runs, making it the right place for conditions such as COUNT(*) > 5."}},{"@type":"Question","name":"How do I filter NULL values in a WHERE clause?","acceptedAnswer":{"@type":"Answer","text":"Use IS NULL or IS NOT NULL. Operators such as = and return unknown for NULL, so `postal_address` = NULL matches nothing, while `postal_address` IS NULL works."}},{"@type":"Question","name":"Does the WHERE clause make queries faster?","acceptedAnswer":{"@type":"Answer","text":"Yes, when the filtered column is indexed, MySQL reads only matching rows instead of scanning the table. Wrapping a column in a function, such as YEAR(`date_of_birth`) = 1980, prevents index use."}},{"@type":"Question","name":"Can AI tools write MySQL WHERE conditions for me?","acceptedAnswer":{"@type":"Answer","text":"Yes. AI assistants turn plain English into SQL, and MySQL Workbench can run the result. Review every condition against your schema, because a model may invent column names."}},{"@type":"Question","name":"Is it safe to run an AI-generated WHERE clause on live data?","acceptedAnswer":{"@type":"Answer","text":"Test it with SELECT first. A wrong WHERE clause on a DELETE or UPDATE query can alter every row. Preview the matched rows, then reuse the same condition for the write."}}]}],"@id":"https://www.guru99.com/where-clause.html#schema-1143729","isPartOf":{"@id":"https://www.guru99.com/where-clause.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/where-clause.html#webpage"}}]} ```