SQL Where Clause on Multiple Conditions

June 6, 2023 | by Arround The Web | No comments

SQL Where Clause on Multiple Conditions

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:

SELECT * FROM sample_db.country_information
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:

SELECT * FROM sample_db.country_information
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:

SELECT * FROM sample_db.country_information
WHERE country IN ('United States', 'Russia');

Result:

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:

SELECT * FROM sample_db.country_information
WHERE NOT area > 2000000 AND population_per_km_sq > 100;

Result:

SQL Multiple Operators

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

SELECT * FROM sample_db.country_information
WHERE (country_information.area > 2000000 AND population_per_km_sq > 100) OR country = 'China';

Output:

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.

Conclusion

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.

Source: linuxhint.com