Lesson 5 – Filtering Records using the WHERE Clause
Till now, in this course, we have studied how to create tables in a database, how to insert records in those tables and how to select records from those tables. In this lesson, you will see how to filter records based on a certain condition. Consider a scenario where you have to search for all the products with a price greater than 500, you can do so using the WHERE clause.
Note: Please make sure that before executing these queries, you have inserted records in the database as mentioned in the previous lessons.
The Where Clause
Let’s see a simple example of the WHERE clause. The following query search products with price greater than 500.
SELECT * from products WHERE price > 500
To use the WHERE clause with a SELECT query, you have to write the normal SELECT query followed by the WHERE clause and the condition for filtering rows. For instance, in the above script, the filter condition is `price > 500` which means that SELECT only those rows where the price is greater than 500. The output of the above script is as follows:
You can see that only the products with a price greater than 500 have been selected.
Multiple Conditions with WHERE Clause
You can specify multiple conditions in the WHERE clause using logical AND and OR operators. For instance, if you want to SELECT only the mobile phones with a price greater than 500 you can use the WHERE clause as follows:
SELECT * from products WHERE price > 500 AND category_id == 2
In the output, you will see the mobiles phones (mobiles have category_id = 2), with price greater than 500 as shown in the following output.
Let’s now select all the products from category 3 OR where the products have a price less than 500.
SELECT * from products WHERE price < 500 OR category_id == 1
In the output, you will see all the products from category 1 OR the products with price less than 500 as shown below:
In this lesson, you saw how to filter records using the WHERE clause. In the next lesson, you will see how to update existing records.