List of Topics

SfC Home > Web Design > SQL >

 

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:

This lesson will answer those questions.



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 and references

Ron Kurtus' Credentials

Websites

SQL Resources

Books

Top-rated books on Using SQL


Questions and comments

Do you have any questions, comments, or opinions on this subject? If so, send an email with your feedback. I will try to get back to you as soon as possible.


Share

Click on a button to bookmark or share this page through Twitter, Facebook, email, or other services:

 

Students and researchers

The Web address of this page is:
www.school-for-champions.com/sql/
filtering_data.htm

Please include it as a link on your website or as a reference in your report, document, or thesis.

Copyright © Restrictions


Where are you now?

School for Champions

SQL topics

Filtering Data Retrieved from Database Table in SQL




SQL topics

Also see



Let's make the world a better place

Be the best that you can be.

Use your knowledge and skills to help others succeed.

Don't be wasteful; protect our environment.

You CAN influence the world.





Live Your Life as a Champion:

Take care of your health

Seek knowledge and gain skills

Do excellent work

Be valuable to others

Have utmost character

Be a Champion!



The School for Champions helps you become the type of person who can be called a Champion.