MS SQL Server

MySQL WHERE Clause

MySQL is a Powerful and efficient RDMS to store huge amounts of data in a structured way, developed by Oracle. The MySQL WHERE clause filters the results of the SELECT statement that meets specific criteria, range, or pattern in data. It is an efficient and flexible solution to search specific data from large databases in a few seconds.

This post will provide syntax along with examples for the WHERE clause for your better understanding.

Prerequisites

You must install MySQL in your system. After its installation, connect a Local MySQL server using this syntax:

mysql -u <username> -p

Once you are logged into the Local server, use this command to display all the available databases:

SHOW DATABASES;

To change the working database, type this command:

USE <db-name>;

A success message will display.

Syntax of WHERE Clause
The syntax for the MySQL WHERE clause is simple and straightforward, in the syntax condition can be any simple equality comparison or a complex expression:

SELECT * FROM <table-name> WHERE condition;

Example 1: Equality Comparison Using WHERE Clause
Let’s suppose that you want to get the data where the “Country” value is equal to “USA”, type this command:

SELECT * FROM Customer
WHERE Country = 'USA';

Here you have filtered the records successfully.

Example 2: Comparison With Operators Using WHERE Clause
You can use different operators in conditions to filter the data that matches the specific condition. For instance, if you want to get records of “UnitPrice” that are less than and equal to “25”, type:

SELECT * FROM OrderItem
WHERE UnitPrice <= 5;

You can also type this for better understanding:

SELECT * FROM OrderItem
WHERE Quantity >= 100;

It is visible that each record is greater than “100”.

Example 3: WHERE Clause With Multiple Conditions
You can also use “AND” and “OR” operators in combination with WHERE Clause to get the result based on multiple conditions, type this command to get data based on the condition for the “Id” column and condition applied on “UnitPrice” condition:

SELECT * FROM Product
WHERE Id <= 10 AND UnitPrice >= 25;



Example 4: Using the IN Operator in a WHERE Clause

The IN operator is used in a WHERE clause to retrieve records based on multiple possible values for a specific column, Type the following code to get the value of “city” for values “London” and “Singapore”:

SELECT * FROM Supplier
WHERE city IN ('London', 'Singapore');

The WHERE Clause can be nested to find multiple possibilities, let’s suppose you want to search a condition for “Id” in “Customer” table depending upon the “OrderNumber” in “Order” table:

SELECT * FROM Customer
WHERE id IN (
  SELECT CustomerId FROM `Order`
  WHERE OrderNumber IN (542381, 542413, 542430, 542551, 542883)
);

You are now capable of retrieving data based on specific conditions with the help of WHERE Clause.

Conclusion

The WHERE clause in MySQL is an essential part of any SELECT statement and is used to filter the results based on specified conditions. If you are searching for specific values, ranges of values, or patterns within your data in a huge database, the WHERE clause will provide a flexible and efficient way to do so.

About the author

Abdul Mannan

I am curious about technology and writing and exploring it is my passion. I am interested in learning new skills and improving my knowledge and I hold a bachelor's degree in computer science.