Advanced Visual Basic Techniques
(Publisher: John Wiley & Sons, Inc.)
Author(s): Rod Stephens
ISBN: 0471188816
Publication Date: 06/01/97

In addition to field names and the asterisk character, the select list can include literal values and aggregate functions. Literal values can include numbers and text strings. Aggregate functions include the following:

•  Avg. The average of the selected field’s values.

•  Count. The number of records returned by the query. Note that COUNT (*) is much faster than statements such as COUNT (column_name).

•  Min, Max. The minimum or maximum value of the selected field.

•  StDev, StDevP. An estimate of the standard deviation for a population (StDevP) or a population sample (StDev).

•  Sum. The sum of the values in the selected column.

•  Var, VarP. Estimates of the variance for a population (VarP) or a population sample (Var).

For example, the following statement returns the average number of pages for the books in the Books table:

SELECT AVG(Length) AS [Average Length] FROM Books

From Clause The FROM clause lists the table or tables from which the records are selected. An SQL statement can join together the data in multiple tables using the FROM clause in several ways. The simplest is to just list the tables. A WHERE clause can specify a link between the tables. For example, the following SQL statement displays information on books and films that share the same title. The clause “WHERE Books.Title = Films.Title” allows the database to match information from the two tables. Because both tables contain a field named Title, the statement must qualify the field as “Books.Title” or “Films.Title” so the database knows which value to select.

SELECT Books.Title, Author, Producer FROM Books, Films
    WHERE Books.Title = Films.Title

Suppose the Books table contains the records shown in Table 9.1, and the Films table contains the records shown in Table 9.2.

Then the previous SELECT statement would return these results:

Title                           Author               Producer
-----                           ------               --------
Visual Basic Algorithms         Stephens             Elliott
Curve Ahead                     Sierpinski           Jackson
Selected 2 records.

This query returns rows only if there are records in both tables with matching titles. This type of join is called an inner join or equi-join. The previous query can be rewritten to make the fact that it is an inner join more obvious, like this:

SELECT Books.Title, Author, Producer FROM Books
    INNER JOIN Films ON Books.Title = Films.Title

Sometimes it may be necessary to return all of the rows in one table plus any matching rows from the other table. This kind of join is called an outer join. There are two kinds of outer joins. A left join selects all of the rows from the first table plus any matching rows in the second. A right join selects all of the rows from the second table plus any matching rows in the first. The following code shows a left join statement and the results it produces.

SELECT Books.Title, Author, Producer FROM Books
    LEFT JOIN Films ON Books.Title = Films.Title;

Title                           Author               Producer
-----                           ------               --------
Visual Basic Algorithms           Stephens             Elliott
The Trial and Death of Socrates   Plato                Null
Curve Ahead                      Sierpinski           Jackson
This Thing Called Calculus        Newton               Null
Selected 4 records.

The next example shows a right outer join and the results it produces. Notice that the statement selects Films.Title rather than Books.Title. Because this is a right join, Books.Title will have a null value for records from the Films table that do not have a corresponding record in the Books table.

SELECT Films.Title, Author, Producer FROM Books
    RIGHT JOIN Films ON Books.Title = Films.Title
Title                           Author               Producer
-----                           ------               --------
Visual Basic Algorithms           Stephens             Elliott
Curve Ahead                      Sierpinski           Jackson
Keys to Success                  Null                 Brahms
The Killer Tortoises of Maui      Null                 Kipster
Selected 4 records.

Where Clause The previous sections have shown several examples of WHERE clauses. A WHERE clause indicates which records should be chosen by a SELECT statement.

SELECT * FROM Books WHERE Length > 300

The WHERE clause can contain the operators =, <>, >, <, >=, and <= to select records. These operators work on fields of most data types. When comparing two date fields, for example, “date1 >= date2” means the first date falls on or after the second.

A WHERE clause can join two or more tables together, as in the following statement:

SELECT Books.Title, Author, Producer FROM Books, Films
    WHERE Books.Title = Films.Title

WHERE clauses can contain the logical operators AND, OR, and NOT to form more complex clauses.

SELECT Books.Title, Author, Producer FROM Books, Films
    WHERE Books.Title = Films.Title AND Length > 300
     AND Producer <> “Jackson”