SQL 101 – SELECT, Filtering Results

After you learn how to get data out of a table with the SELECT command, 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'
  productName
  -----------
  Mountain Dew

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

• =
• >, >=
• <, <=
• <>, !=
• LIKE
• IN (...)
• BETWEEN

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

SELECT
   productName
FROM products
WHERE
   productName like 'p%'

output

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

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'll do a post on some advanced LIKE clauses in a later post.

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

output

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

Last we have BETWEEN, you'll use this most often when looking for records between two values.  Such as BETWEEN '1/1/2005' AND '12/31/2005'.  Since our example table really doesn't have a field that works well with BETWEEN, we'll leave that for another post.

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.

Related posts:

1. SQL 101 – SELECT, Filtering Results (Part 2)
2. SQL 101 – SELECT
3. SELECT INTO
4. SELECT * Overhead
5. SQL 101 – The Advanced LIKE Clauses

About Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.