<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

SQL 101 - SELECT, Filtering Results

Published on
8,157 Points
2,057 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

CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Join & Write a Comment

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month