<

SQL 101 - SELECT, Filtering Results

Published on
8,127 Points
2,027 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
Comment
0 Comments

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month