Databases are a significant building block of modern applications. However, databases store a tremendous amount of data. Hence, we need ways to filter out the data and only fetch the necessary records.
One of SQL’s most prevalent data filtering methods is using the WHERE clause. The WHERE clause in SQL allows us to filter the records based on a given condition. This clause can specify one or more conditions which allows us to filter the records that are returned by a SELECT statement.
This tutorial explores how to work with the WHERE clause and specify the multiple conditions in a single clause which allows for more controlled and granular data filtering.
SQL WHERE Clause Basics
Let us start with the basics and learn the fundamentals of working with the WHERE clause in SQL.
Suppose we have a table which holds the country information, and we fetch only the countries with a population (per square km) that is greater than 100 and an area that is greater than 2000000.
We can run a query as follows:
WHERE area > 2000000 AND population_per_km_sq > 100;
In the previous example, we use the WHERE clause and the AND operator to specify two conditions. Using the AND operator tells SQL that both conditions MUST be fulfilled for the record to be included in the result set.
This should return only the countries that match the specified criteria as follows:
Let’s look at the other operators to specify the multiple conditions in a WHERE clause.
SQL OR Operator
The OR operator allows us to specify the multiple conditions in a WHERE clause. Still, unlike an AND operator that requires all the conditions to be met, the OR operator requires at least one of the conditions to be met.
For example, to select the countries that have an area that is greater than 2000000 or a population (per square km) that is greater than 100, we can run the following query:
WHERE area > 2000000 OR population_per_km_sq > 100;
In this case, the query should return the results as follows:
SQL IN Operator
We can also use the IN operator to specify the multiple values for a column. For example, to select the countries whose name includes the “United States” and “Russia” strings, we can run the query as follows:
WHERE country IN ('United States', 'Russia');
SQL NOT Operator
The NOT operator allows us to negate a given condition.
For example, select the countries that do not have the specified area and popular per sq km:
WHERE NOT area > 2000000 AND population_per_km_sq > 100;
SQL Multiple Operators
We can also combine the multiple operators to specify more complex conditions.
WHERE (country_information.area > 2000000 AND population_per_km_sq > 100) OR country = 'China';
In this example, we use the parentheses to group the first two conditions so they are evaluated as a single condition before being compared to the third condition.
We learned how to use the WHERE clause to specify the multiple conditions in SQL. In addition, we covered the AND, OR, IN, and NOT operators and discovered how to combine them to create more complex conditions.