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 on example to demonstrate the functionality of MySQL where clause 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, and brand = Levis, or River Island, etc. Once you applied the above filters, it will only display the shirts that are matching the above requirements.
Let me show you what happens internally (mean MySQL query against the 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
MySQL WHERE Clause Syntax
The SELECT Statement with WHERE Clause in MySQL 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 MySQL 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 MySQL 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 in MySQL WHERE Clause.
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
MySQL WHERE Clause Multiple Condition
In this example, we are going to use Multiple Conditions in MySQL WHERE Clause.
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 retrieve above specified records where the Continent is equal to Asia, and Region is equal to Southern and Central Asia.
Let me show you another explain to explain the multiple conditions. In this case, we are going to use less than and greater than operators in the WHERE Clause.
USE world; SELECT Code, Name, Continent, Region, SurfaceArea, IndepYear, Population, LocalName FROM country WHERE Population > 20000000 AND Population < 32164700;
Where Clause Command Prompt Example
In this example, we write a query in Command prompt to demonstrate the MySQL 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.
NOTE: 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, MySQL WHERE Clause does not understand the ALIAS Column name declared in the SELECT Statement.