Where Clause To Filter Rows in MySQL

This tutorial is part of the series Learn Basic SQL Queries Using MySQL. In the previous tutorial, we have discussed the SQL queries to fetch the rows or read data from a table in MySQL. The SELECT command returns all the rows in a table unless we specify the limit as shown in the previous tutorial examples. In several situations, we need to filter the resulted rows to get the limited rows meeting certain conditions. We can filter the rows using the WHERE clause by specifying the filter condition. We can also specify multiple conditions using the keywords AND, OR.

Where Clause

The WHERE clause can be used to read filtered rows from a table based on the given conditions. In case you are remotely logged in to the database, you will also need the SELECT privilege for the table in order to read the rows from a table.

# SELECT - Syntax - WHERE
SELECT column_1,column_2,... FROM table_name WHERE <single or multiple conditions>;

Query Explanation

The MySQL command SELECT can be used to read rows or data from the given table name where the select expression and name of the table is mandatory.

We can filter the resulted query set returned by the select query using the WHERE clause. We can specify either single or multiple conditions to filter the results. The keywords AND and OR can be used to apply multiple conditions. We can also use the keywords IN and NOT IN to restrict the column values to a set of values.

Operators

We can use the below-listed operators to perform comparisons while applying the filter conditions. The comparison will be done using the given value and the values stored in the table column.

= It can be used to check if the values of the two operands are equal or not.
!= It checks whether the values of the two operands are not equal.
> It checks whether the value of the left operand is greater than the value of the right operand.
< It checks whether the value of the left operand is less than the value of the right operand.
>= It checks whether the value of the left operand is greater than or equal to the value of the right operand.
<= It checks whether the value of the left operand is less than or equal to the value of the right operand.
 

Examples

This section provides examples to read filtered data from the table using the SELECT command with the WHERE clause. Use the below-mentioned query to create the user table having the id, first name, and last name columns to store user data.

# Create the User Table
CREATE TABLE `user` (
  `user_id` bigint(20) NOT NULL,
  `first_name` varchar(45),
  `last_name` varchar(45)
);

The below-mentioned query can be used to insert data in the user table.

# Insert Rows - All Columns
INSERT INTO `user` ( `user_id`, `first_name`, `last_name`) VALUES 
    ( 1, 'John', 'Smith' ),
    ( 2, 'Rick', 'Jones' ),
    ( 3, 'Catherine', 'Ponting' ),
    ( 4, 'Harsh', 'Upadhyay' ),
    ( 5, 'Tajwinder', 'Singh' ),
    ( 6, 'Leo', NULL ),
    ( 7, 'Leo', 'Murphy' ),
    ( 8, 'Ram', 'Choudhary' ),
    ( 9, 'Nicole', NULL ),
    ( 10, 'Ram', 'Choudhary' );

The above-mentioned query will insert 10 rows in the table having the id, first name and last name to represent 10 different users.

Now we will read the filtered data inserted by us in the user table. It can be done using the SELECT command as shown below.

# Filter - WHERE - Read all rows having first name set to Leo
SELECT * FROM `user` WHERE `first_name` = 'Leo';

# Result
6	Leo	
7	Leo	Murphy

# Filter - WHERE - AND - Read all rows having first name set to Leo and last name set to Murphy
SELECT * FROM `user` WHERE `first_name` = 'Leo' AND `last_name` = 'Murphy';

# Result
7	Leo	Murphy

# Filter - WHERE - IS NOT - Read all rows having last name without NULL values
SELECT * FROM `user` WHERE `last_name` IS NOT NULL;

# Result
1	John		Smith
2	Rick		Jones
3	Catherine	Ponting
4	Harsh		Upadhyay
5	Tajwinder	Singh
7	Leo		Murphy
8	Ram		Choudhary
10	Ram		Choudhary

# Filter - WHERE - OR, IN - Read all rows having first name is either John or Harsh
SELECT * FROM `user` WHERE `first_name` = 'John' OR `first_name` = 'Harsh';
SELECT * FROM `user` WHERE `first_name` IN ( 'John', 'Harsh' );

# Result
1	John	Smith
4	Harsh	Upadhyay

The above-mentioned queries perform various filter operations on string values using single or multiple conditions. Make sure to use the single quotes while applying the filters for columns having the string data type.

# Filter - WHERE - Read all rows having id lesser than 5
SELECT * FROM `user` WHERE `user_id` < 5;

# Result
1	John		Smith
2	Rick		Jones
3	Catherine	Ponting
4	Harsh		Upadhyay

# Filter - WHERE - LIMIT - Read all rows having id lesser than 5
SELECT * FROM `user` WHERE `user_id` < 5 LIMIT 2;

# Result
1	John		Smith
2	Rick		Jones

# Filter - WHERE - AND - Read all rows having id greater than 3 and lesser than 6
SELECT * FROM `user` WHERE `user_id` > 3 AND `user_id` < 6;

# Result
4	Harsh		Upadhyay
5	Tajwinder	Singh

The above mentioned queries apply the conditions for numeric data types. We can also mix the different type of values as shown below.

# Filter - WHERE - AND - Read all rows having first name set to Harsh, id greater than 2 and lesser than 5
SELECT * FROM `user` WHERE `first_name` = 'Harsh' AND `user_id` > 3 AND `user_id` < 6;

# Result
4	Harsh	Upadhyay

This is how we can read the filtered data stored in the MySQL tables using the WHERE clause.