Skip to content Skip to main navigation Skip to footer

Limit Rows Selected

Objectives

This tutorial covers the following objectives:

  • Apply SQL syntax to restrict the rows returned from a query
  • Demonstrate application of the WHERE clause syntax
  • Explain why it is important, from a business perspective, to be able to easily limit data retrieved from a table
  • Construct and produce output using a SQL query containing character strings and date values

Purpose

  • Have you ever had “information overload”?
  • The television is on, your Mom is asking you how school went today, the phone rings, and the dog is barking.
  • Wouldn’t it be nice to be able to restrict the amount of information you have to process at one time?
  • In SQL, this is the job of the WHERE clause.
  • It is important to be able to choose the information you need to see from a table.
  • Tables can have millions of rows of data, and it is a waste of resources to search and return data you don’t need or want.

SELECT Statement

  • You use SELECT to retrieve information from the database.
  • A SELECT statement must include at a minimum a SELECT clause and a FROM clause.
  • The WHERE clause is optional.
SELECT*|{[DISTINCT] column | expression alias]..}
FROM table
[WHERE condition(s)];

SELECT clause general syntax: The [ ] mean this is optional. The | means “or.”

WHERE Clause

  • When retrieving data from the database, you may need to limit the rows of data that are displayed.
  • You can accomplish this using the WHERE clause.
  • A WHERE clause contains a condition that must be met, and it directly follows the FROM clause in a SQL statement.
  • The syntax for the WHERE clause is:
WHERE column_name comparison_condition comparison_value

Note: An alias cannot be used in the WHERE clause!

  • Examine the following SQL statement from the Employees database:
SELECT employee_id, first_name, last_name
FROM employees;
WHERE Clause
  • By adding a WHERE clause, the rows are limited to those rows where the value of employee_id is 101.
WHERE Clause

Comparison Operators in the WHERE Clause

  • As you saw above, the = sign can be used in the WHERE clause.
  • In addition to the “equal to” operator (=), other comparison operators can be used to compare one expression to another:
    • = equal to
    • > greater than
    • >= greater than or equal to
    • < less than
    • <= less than or equal to
    • <> not equal to (or != or ^=)
  • In the example below, the department_id column is used in the WHERE clause, with the comparison operator = .
  • All employees with a department_id of 90 are returned.
SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id = 90;
Comparison Operators in the WHERE Clause

Character and Date Strings in the WHERE Clause

  • Character strings and dates in the WHERE clause must be enclosed in single quotation marks ‘ ‘.
  • Numbers, however, should not be enclosed in single quotation marks.
  • Look at the following example from the Employees database.
  • The WHERE clause contains a string and is enclosed in single quotation marks.
SELECT first_name, last_name
FROM employees
WHERE last_name = 'Taylor';
  • What do you think will happen if the WHERE clause is written as:
WHERE last_name = 'jones';
  • All character searches are case-sensitive.
  • Because the employees table stores all the last names in the proper case, no rows are returned in this example.
  • This is an important point to remember.
  • In another lesson, you will learn to use other SQL keywords UPPER, LOWER, and INITCAP that will make it easier to avoid a case-sensitive mistake.

Comparison Operators in the WHERE Clause

  • Comparison operators can be used in all of the following ways in the WHERE clause:
WHERE hire_date < '01-Jan-2000'
WHERE salary >= 6000
WHERE job_id = 'IT_PROG'

Like character strings, Date values must also be enclosed in single quotes.

  • In the following example from the Employees database, which rows will be selected?
  • Will salaries of 3000 be included in the result set?
SELECT last_name, salary
FROM employees
WHERE salary <= 3000;

Terminology

Key terms used in this tutorial included:

  • WHERE Clause
  • Comparison Operators

Summary

In this tutorial, you should have learned how to:

  • Apply SQL syntax to restrict the rows returned from a query
  • Demonstrate application of the WHERE clause syntax
  • Explain why it is important, from a business perspective, to be able to easily limit data retrieved from a table
  • Construct and produce output using a SQL query containing character strings and date values

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *