SfC Home > SQL >

Explanation of Filtering Data Retrieved from Database Table in SQL - Succeed with SQL. Also refer to 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:

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:

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.

Answers to Readers' Questions

Resources

The following are resources on this subject.

Websites

SQL Resources

Books

Top-rated books on SQL

Miscellaneous

Mini-quiz to check your understanding

1. Can you use WHERE when retrieving just one column?

2. Can the ">" operator be used with text fields?

3. Why can't you use AND to filter two items from the same column?

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?

School for Champions

SQL topics

Filtering Data Retrieved from Database Table in SQL