Book HomeMySQL and mSQLSearch this book

6.7. Queries

The last common SQL command used is the one that enables you to view the data in the database: SELECT. This action is by far the most common action performed in SQL. While data entry and modifications do happen on occasion, most databases spend the vast majority of their lives serving up data for reading. The general form of the SELECT statement is as follows:

SELECT column1, column2, ..., columnN 
FROM table1, table2, ..., tableN
[WHERE clause]

This syntax is certainly the most common way in which you will retrieve data from any SQL database. Of course, there are variations for performing complex and powerful queries, especially under MySQL. We cover the full range of the SELECT syntax in Chapter 15, "SQL Reference".

The first part of a SELECT statement enumerates the columns you wish to retrieve. You may specify a "*" to say that you want to select all columns. The FROM clause specifies which tables those columns come from. The WHERE clause identifies the specific rows to be used and enables you to specify how to join two tables.

6.7.1. Joins

Joins put the "relational" in relational databases. Specifically, a join enables you to match a row from one table up with a row in another table. The basic form of a join is what you may hear sometimes described as an inner join . Joining tables is a matter of specifying equality in columns from two tables:

SELECT book.title, author.name 
FROM author, book
WHERE book.author = author.id

Consider a database where the book table looks like Table 6-1.

Table 6-1. A book Table

ID

Title

Author

Pages

1

The Green Mile

4

894

2

Guards, Guards!

2

302

3

Imzadi

3

354

4

Gold

1

405

5

Howling Mad

3

294

And the author table looks like Table 6-2.

An inner join creates a table by combining the fields of both tables for rows that satisfy the query in both tables. In our example, the query specifies that the author field of the book table must be identical to the id field of the author table. The query's result would thus look like Table 6-3.

Table 6-2. An author Table

ID

Name

Citizen

1

Isaac Asimov

US

2

Terry Pratchet

UK

3

Peter David

US

4

Stephen King

US

5

Neil Gaiman

UK

An inner join creates a table by combining the fields of both tables for rows that satisfy the query in both tables. In our example, the query specifies that the author field of the book table must be identical to the id field of the author table. The query's result would thus look like Table 6-3.

Neil Gaiman is nowhere to be found in these results. He is left out because there is no value for his author.id value found in the book.author table. An inner join only contains those rows that exactly match the query. We will discuss the concept of an outer join later in the chapter for situations where we would be interested in the fact that we have an author in the database who does not have a book in the database.

Table 6-3. Query Results Based on an Inner Join

Book Title

Author Name

The Green Mile

Stephen King

Guards, Guards!

Terry Pratchet

Imzadi

Peter David

Gold

Isaac Asimov

Howling Mad

Peter David

Neil Gaiman is nowhere to be found in these results. He is left out because there is no value for his author.id value found in the book.author table. An inner join only contains those rows that exactly match the query. We will discuss the concept of an outer join later in the chapter for situations where we would be interested in the fact that we have an author in the database who does not have a book in the database.

6.7.2. Aliasing

When you use column names that are fully qualified with their table and column name, the names can grow to be quite unwieldy. In addition, when referencing SQL functions, which will be discussed later in the chapter, you will likely find it cumbersome to refer to the same function more than once within a statement. The aliased name, usually shorter and more descriptive, can be used anywhere in the same SQL statement in place of the longer name. For example:

# A column alias
SELECT long_field_names_are_annoying AS myfield
FROM table_name
WHERE myfield = 'Joe'
# A table alias under MySQL
SELECT people.names, tests.score 
FROM tests, really_long_people_table_name AS people
# A table alias under mSQL
SELECT people.names, tests.score
FROM tests, really_long_people_table_name=people

While mSQL fully supports table aliasing, it does not support column aliasing.

6.7.3. Grouping and Ordering

The results you get back from a select are, by default, indeterminate in the order they will appear. Fortunately, SQL provides some tools for imposing order on this seemingly random list. The first tool -- available in both MySQL and mSQL -- is ordering. You can tell a database that it should order any results you see by a certain column. For example, if you specify that a query should order the results by last_name, then the results will appear alphabetized according to the last_name value. Ordering comes in the form of the ORDER BY clause:

SELECT last_name, first_name, age
FROM people
ORDER BY last_name, first_name

In this situation, we are ordering by two columns. You can order by any number of columns, but the columns must be named in the SELECT clause. If we had failed to select the last_name above, we could not have ordered by the last_name field.

Grouping is an ANSI SQL tool that MySQL implements but mSQL does not. Because mSQL does not have any concept of aggregate functions, grouping simply does not make sense in mSQL. As its name implies, grouping lets you group rows with a similar value into a single row in order to operate on them together. You usually do this to perform aggregate functions on the results. We will go into functions a little later in the chapter.

Consider the following:

mysql> SELECT name, rank, salary FROM people\g
+--------------+----------+--------+
| name         | rank     | salary |
+--------------+----------+--------+
| Jack Smith   | Private  |  23000 |
| Jane Walker  | General  | 125000 |
| June Sanders | Private  |  22000 |
| John Barker  | Sargeant |  45000 |
| Jim Castle   | Sargeant |  38000 |
+--------------+----------+--------+
5 rows in set (0.01 sec)

If you group the results by rank, the output changes:

mysql> SELECT rank FROM people GROUP BY rank\g
+----------+
| rank     |
+----------+
| General  |
| Private  |
| Sargeant |
+----------+
3 rows in set (0.01 sec)

Now that you have the output grouped, you can finally find out the average salary for each rank. Again, we will discuss more on the functions you see in this example later in the chapter.

mysql> SELECT rank, AVG(salary) FROM people GROUP BY rank\g
+----------+-------------+
| rank     | AVG(salary) |
+----------+-------------+
| General  | 125000.0000 |
| Private  |  22500.0000 |
| Sargeant |  41500.0000 |
+----------+-------------+
3 rows in set (0.04 sec)

The power of ordering and grouping combined with the utility of SQL functions enables you to do a great deal of data manipulation even before you retrieve the data from the server. You should take great care not to rely too heavily on this power. While it may seem like an efficiency gain to place as much processing load as possible onto the database server, it is not really the case. Your client application is dedicated to the needs of a particular client, while the server is being shared by many clients. Because of the greater amount of work a server already has to do, it is almost always more efficient to place as little load as possible on the database server. MySQL and mSQL may be two of the fastest databases around, but you do not want to waste that speed on processing that a client application is better equipped to manage.

If you know that a lot of clients will be asking for the same summary information often (for instance, data on a particular rank in our previous example), just create a new table containing that information and keep it up to date as the original tables change. This is similar to caching and is a common database programming technique.



Library Navigation Links

Copyright © 2001 O'Reilly & Associates. All rights reserved.

This HTML Help has been published using the chm2web software.