SfC Home > Web Design > SQL >
Explanation of Filtering Data Retrieved from Database Table in SQL - Succeed in Using SQL. Also refer to Structured Query Language, Access, MySQL, MS SQL Server, WHERE clause, equals, less than, greater, between, AND, OR, operator, Ron Kurtus, School for Champions. Copyright © Restrictions
Filtering Data Retrieved from Database Table in SQL
by Ron Kurtus (5 February 2007)
You can filter the data you retrieve from a database table by using the WHERE clause to specify search conditions in SQL. This clause has various conditional operators, such as "equals" and "is greater than", to define the type of filtering used. You can also combine WHERE clauses with the AND and OR operators.
Questions you may have include:
- How do you use the WHERE clause?
- What is a listing of operators?
- How are AND and OR operators used?
This lesson will answer those questions. There is a mini-quiz near the end of the lesson.
Using WHERE clause
The WHERE clause filters the data retrieved from a specific column.
SELECT Farm, Product
FROM table_name
WHERE Product = apples;
This search would result in a listing of all elements in Farm and Product where rows or records in Product consisted of the word apples. For example:
Farm |
Product |
| Johnson Farms | apples |
| Wilson Co. | apples |
| Happy Farms | apples |
In other words, the filtering results in the list of all farms that sell apples.
List of operators
Operators used with the WHERE clause are:
Operator |
Description |
| = | Equals |
| <> | Not equals |
| != | Not equals |
| < | Less than |
| <= | Less than or equal to |
| !< | Not less than |
| > | Greater than |
| >= | Greater than or equal to |
| !> | Not greater than |
| BETWEEN | Between two specified values |
| IS NULL | Is the NULL value or empty |
Note: Using ! for NOT may not be universal in all database software.
Also note: NULL means the field is empty. It does not mean it contains the number 0.
An example of using BETWEEN is:
SELECT Cost, Product
FROM table_name
WHERE Cost BETWEEN $10.00 AND $15.00;
AND and OR operators
You can have multiple filtering, using the the AND or OR operators with the WHERE clause.
AND operator
You can use AND to filter a combination of different columns.
SELECT brand, price, item
FROM table_name
WHERE price > $1000 AND item = television sets;
This results in listing the brand names of all television sets costing greater than $1000.
You could have a series of AND operations for the various columns.
SELECT brand, price, item, availability
FROM table_name
WHERE price > $1000 AND item = television sets AND availability = Yes;
OR operator
You can use the OR operator to filter the search in a single column.
SELECT brand, item
FROM table_name
WHERE item = television set OR item = radio;
This would result in a listing of all brands that are selling either television sets or radios.
Although you could use OR to filter two different columns, that information often would not be useful.
Combinations of AND and OR
It is possible to have combinations of AND and OR operators in your filtering. Explanation of that will be taken up in another lesson.
Summary
Data can be filtered from a table by using the WHERE clause to specify search conditions. This clause has various conditional operators, such as "equals" and "is greater than", to define the type of filtering used. You can also combine WHERE clauses with the AND and OR operators.
Always do your best
Resources
The following are resources on this subject.
Websites
Books
Mini-quiz to check your understanding
If you got all three correct, you are on your way to becoming a Champion in working with SQL. If you had problems, you had better look over the material again.
What do you think?
Do you have any questions, comments, or opinions on this subject? If so, send an email with your feedback. We will try to get back to you as soon as possible.
Share link
Feel free to establish a link from your website to pages in this site.
Or use our form to send this link to yourself or a friend.
Students and researchers:
The Web address of this page is
www.school-for-champions.com/sql/filtering_data.htm.
Please include it as a reference in your report, document, or thesis.
Where can you go from here?
Filtering Data Retrieved from Database Table in SQL