Introducing SELECT

One of the most important functions of any database application is finding the data that's in the database. We're going to spend the next few columns exploring the SQL SELECT command, the workhorse of most database applications. Hopefully you've got a database up and running so you can try this yourself as we work through the command.

Simple SELECT

We'll be using the simple database we started with last week as a starting point for our experiments with the SELECT command. We'll be SELECTing data from our tiny music database table (which we'll name MusicCollection for ease of reference):

As was mentioned in the original article, SQL is very English-like. Commands typically consist of a verb, an object, and possibly a set of clauses that modify the object. So to find all of the musical artists in the database, we could say something like "Choose all the values from the Artist field of the database." The SQL translation of this sentence is

SELECT Artist FROM MusicCollection;

The is the most basic version of the SELECT command. It returns an entire column of data from the database. In this case the results would look something like the following.

So what else can you do with SELECT? Plenty! You can retrieve multiple columns.

SELECT Artist,Title FROM MusicCollection;

which would return

You can also use a shortcut command to return ALL the columns of a database, basically displaying the entire thing. This is accomplished by using an asterisk (*) instead of any column names.

SELECT * FROM MusicCollection;

which would return

You can even get really fancy and assign a field a new temporary name, or an alias. This technique is normally used when there are fields in two or more tables with the same name, which is a topic for a later column -- but just for fun (for the sake of completeness), we could do something like the following:

SELECT Title AS AlbumName FROM MusicCollection;

So here's what we know so far about the SELECT statement:

1. SELECT returns one or more columns from a data table chosen by field name.
2. The FROM clause identifies the database table to use as a source of data.
3. The asterisk (*) is a shortcut for returning all field names.
4. Column names can be aliased.

And to whet your appetite for future columns, we can use the SELECT statement to display data from more than one table. But that's a story for another column. With the space that's left this time, we've got another important topic to cover.

Sorting data with SELECT

SQL is designed to manipulate data, so you can safely assume that SQL has built-in tools for doing almost any sort of common data manipulation task. One exceedingly simple example is sorting data alphabetically. We can take any of the SELECT queries from the previous section and sort them in alphabetical order using the ORDER BY clause.

SELECT Artist, Title FROM MusicCollection ORDER BY Title;

Now we've got Title-sorted data as a result of the SELECT statement. There are plenty of variations on this theme! One possibility is that we can sort in reverse order

SELECT Artist, Title FROM MusicCollection ORDER BY Title DESC;

The DESC keyword is short for DESCending order. The ASCending keyword can also be used, but since it is the default behavior, it is almost always left out.

You can sort on several different columns, each in its own order:

SELECT Artist, Title FROM MusicCollection ORDER BY Title DESC, Artist ASC;

In this example, Title is ordered in descending alphabetical order and is then further sorted by Artist in ascending order (which has no effect in this case since all the artists and titles are distinct). We can even sort by columns that aren't displayed:

SELECT Artist, Title FROM MusicCollection ORDER BY Year;

This selection is properly sorted by Year, even though it wasn't displayed in the output of the SELECT statement.

Next steps

We've only just started getting your feet wet with data selection. So far, the syntax is

SELECT column_name(s) [AS alias] FROM table_name [ORDER BY column_name(s) (ASC | DESC) ]

where the statements in square brackets are optional.

Next, we're going to start filtering the results of the SELECT command using the WHERE clause. This is one of the most powerful tools/capabilities you'll run into as you learn aboutSQL.

John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.

Read more aboutSQL columns.

Discuss this article in the O'Reilly Network Linux Forum.

Return to the Linux DevCenter.

Copyright © 2009 O'Reilly Media, Inc.