SQL 101 - SELECT, Filtering Results

After you learn how to get data out of a table with the SELECT command (please see my previous article), you'll soon ask the question, how do I limit the number of results I get back.  If you don't ask the question, your DBA will ask you that question.  The answer to this question is to use the WHERE clause.  Adding this clause to your query will let you limit the number of rows the server will return.

SELECT
                         productName
                      FROM products
                      WHERE
                         productName = 'Mountain Dew'

Select all Open in new window

productName 
                      ----------- 
                      Mountain Dew

Select all Open in new window

The WHERE clause will let you limit the number of rows based on any column in the table you're querying.  Now, I want to give you a heads up, you can only limit based on the values stored in the table.  Any computed value like a SUM or COUNT will not work with the WHERE clause.  I'll show you how to use those to limit the rows later.

For now, you'll only be able to limit your results based on the values in the columns.  You have many comparison operators you can use.  In my above example you saw the equals operator.  Let's show you a few more Comparison Operators:
The first four should look familiar if you've been through a few math classes, but the last three are specific to SQL.  Let's walk through how to use these.

LIKE allows you to do partial matching.  You could find all the productNames that start with the letter 'p' using the following:

SELECT
                         productName
                      FROM products
                      WHERE
                         productName like 'p%'

Select all Open in new window

productName 
                      ----------- 
                      pencil 
                      pen 
                      paper

Select all Open in new window

The LIKE operator can do some pretty advanced things.  If you are familiar with regular expressions, you should know that the LIKE comparator works with regular expressions.  I'd like to point out additional uses for the like clause in my article here.

If you wanted to limit the results to a handful of results you could use the IN (...) comparitor, you can then list the values you want to return.

SELECT
                         productName
                      FROM products
                      WHERE
                         productName IN ('pen','paper')

Select all Open in new window

productName 
                      ----------- 
                      pen 
                      paper

Select all Open in new window

Last we have BETWEEN, you'll use this most often when looking for records between two values.  A simple example of the between clause would be to show all productNames that have a price between $2.50 and $25.00.

SELECT
                         productName
                      FROM products
                      WHERE
                         price between 2.50 and 25.00

Select all Open in new window

Experiment with the WHERE clause.  If you have any questions, please, feel free to comment below!  I'm here to help you grow stronger in the ways of The Force, err... SQL.

Previous Articles you may want to visit: