The MySQL WHERE Clause restricts the number of records or rows returned by the SELECT Statement. It means SELECT Statement will return the records only If the condition specified after the WHERE keyword is TRUE.
Let me take an example to demonstrate the functionality of MySQL where clauses in real-time. For instance, we want to order a shirt from the Amazon store. When we type a shirt in the search bar, it will show thousands of shirts. And to customize the search results, we have to use filters such as Gender, Price range between $50 to $100, brand = Levis, or River Island, etc. Once you apply the above filters, it will only display the shirts that match the above requirements.
Let me show you what happens internally (mean query against Amazon). It will go something like this:
SELECT Product_Name, Size, Brand, Price, Discount FROM Products_table WHERE Product_Name = 'Shirts' AND Gender = 'Male' AND Brand = 'Levis' OR 'River Island' AND Price BETWEEN 50 AND 100
The SELECT Statement with MySQL WHERE Clause is
SELECT [Column Name 1], [Column Name 2], ...., [Column Name N] FROM Source_Table WHERE Conditions
- Column Names: Number of columns from the tables. It may be one or more.
- Source_Table: Table(s) present in the Database. Use Joins to join multiple tables.
- Conditions: Here, we have to provide filters or conditions. If the condition is TRUE, then only the SELECT Statement will return the records.
MySQL WHERE Clause Examples
Let us see how to use the WHERE Clause to filter the extracting data. For this, we are going to use the below-shown data
MySQL WHERE Clause Single Condition
In this example, we are going to use a single condition.
USE world; SELECT Code, Name, Continent, Region, SurfaceArea, IndepYear, Population, LocalName FROM country WHERE Continent = 'Asia';
The above MySQL statement will retrieve records as mentioned above where the Continent is exactly equal to Asia
WHERE Clause Multiple Condition
In this example, we are going to use Multiple Conditions.
USE world; SELECT Code, Name, Continent, Region, SurfaceArea, IndepYear, Population, LocalName FROM country WHERE Continent = 'Asia' AND Region = 'Southern and Central Asia';
The above SELECT statement retrieves the above-specified records where the Continent is equal to Asia and Region is equal to Southern and Central Asia.
Let me show you another explanation to explain the multiple conditions. In this case, we are going to use less than and greater than operators in the MySQL WHERE Clause.
USE world; SELECT Code, Name, Continent, Region, SurfaceArea, IndepYear, Population, LocalName FROM country WHERE Population > 20000000 AND Population < 32164700;
Command Prompt Example
In this example, we write a query in the Command prompt to demonstrate the where clause along with Multiple Conditions.
USE world; SELECT Code, Name, Continent, Region, SurfaceArea, IndepYear, Population, LocalName FROM country WHERE Continent = 'Asia' AND Population > 40000000;
The above query retrieves the records mentioned above, where the continent is Asia, and the population is greater than 40000000.
Don’t use ALIAS Column Names in the WHERE Condition. Because WHERE Clause will execute first, and then SELECT Statement will select the Columns. So, the WHERE Clause does not understand the ALIAS Column name declared in the SELECT Statement.