Clauses, Sorting, and SQL Queries
(Page 1 of 5 )

In this conclusion to a three-part series on the Select statement in SQL, you'll learn about the where clause, ascending vs. descending sort ordering, and more. This article is excerpted from chapter three of the book Learning SQL, written by Alan Beaulieu (O'Reilly; ISBN: 0596007272). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

 The where Clause

The queries shown thus far in the chapter have selected every row from the employee, department, or account tables (except for the demonstration of distinctearlier in the chapter). Most of the time, however, you will not wish to retrieve every row from a table but will want a way to filter out those rows that are not of interest. This is a job for thewhereclause.

Thewhere clause is the mechanism for filtering out unwanted rows from your result set.

For example, perhaps you are interested in retrieving data from theemployeetable, but only for those employees who are employed as head tellers. The following query employs awhere clause to retrieve only the four head tellers:

    mysql> SELECT emp_id, fname, lname, start_date, title
        -> FROM employee
        -> WHERE title = 'Head Teller';

emp_id fname			lname		start_date  title
	6  Helen	Fleming  2004-03-17 Head Teller
	10  Paula	Roberts  2002-07-27  Head Teller
	13  John	Blake		2000-05-11  Head Teller
	16  Theresa  Markham  2001-03-15  Head Teller

4 rows in set (0.00 sec) 

In this case, 14 of the 18 employee rows were filtered out by thewhereclause. Thiswhereclause contains a single filter condition, but you can include as many
conditions as required; individual conditions are separated using operators such asand,or, andnot(see Chapter 4 for a complete discussion of thewhere clause and filter conditions). Here’s an extension of the previous query that includes a second condition stating that only those employees with a start date later than January 1, 2002, should be included:

    mysql> SELECT emp_id, fname, lname, start_date, title
        -> FROM employee
        -> WHERE title = 'Head Teller'
        ->   AND start_date > '2002-01-01';

emp_id fname  lname		start_date  title
	6  Helen Fleming  2004-03-17  Head Teller
	10  Paula  Roberts 2002-07-27 Head Teller

2 rows in set (0.00 sec)

The first condition (title='Head Teller') filtered out 14 of 18 employee rows, and the second condition (start_date > '2002-01-01') filtered out an additional 2 rows, leaving 2 rows in the result set. Let’s see what would happen if you change the operator separating the two conditions fromandtoor:

    mysql> SELECT emp_id, fname, lname, start_date, title
        -> FROM employee
        -> WHERE title = 'Head Teller'
        ->   OR start_date > '2002-01-01';  

 

emp_id  fname		lname	start_date  title
	2  Susan	Barker		2002-09-12  Vice President
	4 Susan	Hawthorne | 2002-04-24 Operations Manager
	5 John	Gooding			2003-11-14 Loan Manager
	6 Helen	Fleming			2004-03-17  Head Teller

 

	7  Chris		Tucker		2004-09-15 Teller
	8  Sarah  Parker				2002-12-02 Teller
	9  Jane	Grossman			2002-05-03 Teller
	10 Paula		Roberts		2002-07-27  Head Teller
	12 Samantha  Jameson				2003-01-08 Teller
	13 John			Blake	2000-05-11  Head Teller
	14 Cindy			Mason	2002-08-09  Teller
	15  Frank			Portman	2003-04-01 Teller
	16 Theresa			Markham	2001-03-15  Head Teller
	17 Beth			Fowler	2002-06-29 Teller
	18  Rick			Tulman	2002-12-12 Teller

15 rows in set (0.00 sec)

Looking at the output, you can see that all four Head Tellers are included in the result set, along with any other employee who started working for the bank after January 1, 2002. At least one of the two conditions is true for 15 of the 18 employees in theemployeetable. Thus, when you separate conditions using theand operator, all conditions must evaluate totrueto be included in the result set; when you useor, however, only one of the conditions need evaluate totruefor a row to be included.

So what should you do if you need to use bothandandoroperators in yourwhere clause? Glad you asked. You should use parentheses to group conditions together. The next query specifies that only those employees who are Head Tellers and began working for the company after January 1, 2002, or those employees who are Tellers and began working after January 1, 2003, be included in the result set:

    mysql> SELECT emp_id, fname, lname, start_date, title
        -> FROM employee
        -> WHERE (title = 'Head Teller' AND start_date > '2002-01-01')
        ->   OR (title = 'Teller' AND start_date > '2003-01-01');

emp_id  fname		lname	start_date  title
	6  Helen	Fleming  2004-03-17 Head Teller
	7 Chris	Tucker		2004-09-15  Teller
	10  Paula	Roberts  2002-07-27  Head Teller
	12  Samantha  Jameson 2003-01-08  Teller
	15 Frank	Portman  2003-04-01  Teller

5 rows in set (0.00 sec)

You should always use parentheses to separate groups of conditions when mixing different operators so that you, the database server, and anyone who comes along later to modify your code will be on the same page.

The group by and having Clauses

All of the queries thus far have retrieved raw data without any manipulation. Sometimes, however, you will want to find trends in your data that will require the database server to cook the data a bit before you retrieve your result set. One such mechanism is the group by clause, which is used to group data by column values. For example, rather than looking at a list of employees and the departments to which they are assigned, you might want to look at a list of departments along with the number of employees assigned to each department. When using the group by clause, you may also use the having clause, which allows you to filter group data in the same way thewhereclause lets you filter raw data.

I wanted to briefly mention these two clauses so they don’t catch you by surprise later in the book, but they are a bit more advanced than the other fourselectclauses. Therefore, I ask that you wait until Chapter 8 for a full description of how and when to usegroup byandhaving.

Next: The order by Clause >>
 

More Database Articles Articles
More By O'Reilly Media

Comments On: Clauses, Sorting, and SQL Queries

· This article is an excerpt from the book "Learning SQL," published by O'Reilly. We...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter three of the book Learning SQL, written by Alan Beaulieu (O'Reilly; ISBN: 0596007272). Check it out today at your favorite bookstore. Buy this book now.