For an instructor lead, in-depth look at learning SQL click below.
Structured Query Language (SQL) is a widely-used database language, providing means of data manipulation and data definition. One of its most common applications is retrieving records from a database based on specific criteria – this filtering of data is done with the SQL WHERE clause. In this article, we will be looking at how to use SQL to retrieve records based on multiple conditions.
Using the SQL WHERE Clause
The WHERE clause is used to filter records, and is added after the FROM keyword in the SQL statement. A simple usage of the WHERE clause could look like the following:
|
1 2 3 4 |
SELECT * FROM Customers WHERE Country='Mexico'; |
This SQL statement selects all fields from the “Customers” table where the “Country” field is ‘Mexico’. The result is a recordset of Mexican customers.
Combining Conditions: AND and OR Operators
Handling multiple conditions requires the use of logical operators: AND and OR. The AND operator displays a record if all conditions separated by AND are TRUE. The OR operator displays a record if any of the conditions separated by OR is TRUE.
The AND Operator
|
1 2 3 4 |
SELECT * FROM Customers WHERE Country='Mexico' AND City='Mexico D.F.'; |
This SQL statement selects all fields from “Customers” where the “Country” is ‘Mexico’ AND “City” is ‘Mexico D.F.’. Both conditions have to be true for any record to be selected.
The OR Operator
|
1 2 3 4 |
SELECT * FROM Customers WHERE Country='Mexico' OR Country='USA'; |
This SQL statement selects all fields from “Customers” where the “Country” is ‘Mexico’ OR ‘USA’. Records from both countries will be selected as they meet at least one of the conditions.
Combining AND and OR
You can also combine the AND and OR operators in a single SQL statement. However, be careful with the logic and consider using parentheses to clarify the conditions:
|
1 2 3 4 |
SELECT * FROM Customers WHERE (Country='Mexico' OR Country='USA') AND City='Mexico D.F.'; |
This SQL statement selects all fields from “Customers” where the city is ‘Mexico D.F.’ and the country is either ‘Mexico’ or the ‘USA’. Parentheses help establish clear logic when combining AND and OR operators.
Conclusion
Retrieving records based on multiple conditions is an essential part of working with SQL. Remember to ensure logical consistency when combining AND and OR operators to retrieve the precise data you need.