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 records based on a given condition. This clause can specify one or more conditions, allowing us to filter the records returned by a SELECT statement.

This tutorial will explore how to work with the WHERE clause and specify multiple conditions in a single clause, allowing for more controlled and granular data filtering.

Sample Table

Create table:

CREATE TABLE country_information (
	country_name VARCHAR(100),
	country_code VARCHAR(10),
	country_distance_sq_km DECIMAL (10,
		2),
	total_population BIGINT,
	population_per_sq_km DECIMAL (10,
		2)
);

Insert sample data:

INSERT INTO country_information (country_name, country_code, country_distance_sq_km, total_population, population_per_sq_km)
		VALUES('China', 'CN', 9596961, 1411778724, 147.73), ('India', 'IN', 3287263, 1382716700, 420.68), ('United States', 'US', 9372610, 333950000, 35.68), ('Indonesia', 'ID', 1904569, 273523621, 143.55), ('Pakistan', 'PK', 881912, 240485658, 272.86), ('Brazil', 'BR', 8515767, 215313351, 25.30), ('Nigeria', 'NG', 923768, 223804632, 242.56), ('Bangladesh', 'BD', 147570, 169180000, 1145.87), ('Russia', 'RU', 17098242, 146075100, 8.56), ('Mexico', 'MX', 1964375, 126190788, 64.23), ('Japan', 'JP', 377975, 125930000, 332.35), ('Ethiopia', 'ET', 1127127, 121611000, 107.88), ('Philippines', 'PH', 300000, 113238000, 377.46), ('Egypt', 'EG', 1001450, 110026900, 109.86), ('Vietnam', 'VN', 331212, 98190500, 296.26);

Resulting table:

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 holding country information, and we would like to fetch only the countries with a population (per square km) greater than 100 and an area greater than 2000000.

We can run a query as shown:

SELECT
	*
FROM
	country_information
WHERE
	country_distance_sq_km > 2000000
	AND population_per_sq_km > 100;

In the example above, 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 shown:

Let's look at other operators to specify multiple conditions in a WHERE clause.

SQL OR Operator

The OR operator allows us to specify 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 greater than 2000000 or a population (per square km) greater than 100, we can run the query:

SELECT
	*
FROM
	country_information
WHERE
	country_distance_sq_km > 2000000
	OR population_per_sq_km > 100;

In this case, the query above should return the results as:

SQL IN Operator

We can also use the IN operator to specify multiple values for a column. For example, to select the countries whose name includes the string "United States" and "Russia", we can run the query as:

SELECT
	*
FROM
	country_information
WHERE
	country_name IN('United States', 'Russia');

Result:

SQL NOT Operator

The NOT operator allows us to negate a given condition.

For example, to select the countries that do not have the specified area and popular per sq km.

SELECT
	*
FROM
	country_information
WHERE
	NOT country_distance_sq_km > 2000000
	AND population_per_sq_km > 100;

Result:

SQL Multiple Operators

We can also combine multiple operators to specify more complex conditions.

SELECT
	*
FROM
	country_information
WHERE (country_information.country_distance_sq_km > 2000000
	AND population_per_sq_km > 100)
	OR country_name = 'China';

Output:

In the example above, we're using parentheses to group the first two conditions so they are evaluated as a single condition before being compared to the third condition.

Conclusion

In this tutorial, we've learned how to use the WHERE clause to specify multiple conditions in SQL. In addition, we've covered the AND, OR, IN, and NOT operators and discovered how to combine them to create more complex conditions.