<

SQL 101 - SELECT, Filtering Results

Published on
8,194 Points
2,094 Views
1 Endorsement
Last Modified:
Approved
Community Pick
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'

Open in new window

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

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:
=                (equals)
>, >=          (greater than, greater than or equal to)
<, <=           (less than, less than or equal to)
<>, !=           (not equal to)
LIKE             (pattern matching)
IN (...)          (match one of)
BETWEEN  (range matching)
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%'

Open in new window

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

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')

Open in new window

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

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

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:
1
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free