MySQL WHERE Clause

The MySQL WHERE Clause restricts the number of records or rows returned by the SELECT Statement. It means MySQL SELECT Statement will return the records only If the condition specified after the WHERE keyword or clause 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. It means MySQL Select query with Where Clause 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 MySQL WHERE Clause to filter the extracting data. For this, we are going to use the below-shown data

Table records 1

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

MySQL WHERE Clause 2

MySQL 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.

Multiple Conditions 3

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;
MySQL WHERE Clause 4

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.

Command Prompt Example 5

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.