STATEMENT: SELECT
SELECT [predicate] { * | table.* |
[table.]field1 [AS alias1]
[, table.]field2 [AS alias2] [, ...] ] }
FROM tableexpression [, ...]
[IN externaldatabase]
[WHERE... ] [NOT] [IN] (value1,[value2,[...] ] )
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]
[subqueryclause [subqueryclause [...] ] ]
The SELECT statement returns information from a database as a set
of records without altering the database. It searches the database,
extracts the chosen columns and selects those records
that meet the criteria, sorting and grouping the
results into a specified order.
A SELECT statement can be nested inside of another SELECT statement
which is nested inside of another SELECT and so on.
When a SELECT is nested it is refered to as a
subquery clause.
At its simplest, you can use an asterisk ( * ) to select
all of the fields in a table (in
this case the table 'Musicians' ):
SELECT * FROM Musicians;
Or you can be more selective and choose just one
or a few of the fields in a table,
and they will be returned in the order listed:
SELECT MusicianName, Instrument FROM Musicians;
You can also have a query display a field under a different
heading than the one used in the table
by establishing an "alias" with the reserved word AS:
SELECT MusicianName AS Musician, Instrument
FROM Musicians;
...and you can even have a query
combine two or more fields from a table into one field in the
returned list under a new heading using the ampersand
character:
SELECT Name, City & ", " & Country AS Location
FROM Customers;
WHERE
You can use the WHERE clause to further focus your selection
by specifying certain criteria
to be met by the values. The following example returns the
names of all musicians who play the flute:
SELECT MusicianName AS Musician FROM Musicians
WHERE Instrument = 'flute';
...and this example returns the names
of all jigs in a 'Tunes' table:
SELECT TuneName AS Name, Source1 AS Recording FROM Tunes
WHERE TuneType = 'jig';
You can combine more
than one criterion in a WHERE clause using any of the logical
operators. Here the query returns a list of all items which are blue
and cost less than $100:
SELECT Item, UnitPrice AS Price FROM Products
WHERE Color = 'blue' AND UnitPrice < 100;
The optional, reserved word IN can be used either as a clause or as an operator.
If you want to get fields from a database other than the one you are
currently working in, you
use the IN as a clause:
SELECT Name, Address
FROM PianoTuners IN USAPiano.mdb
WHERE state='TN';
If the database is a non-Microsoft Jet database, you must append a semicolon ( ; )
to the database file name and enclose it within a pair of single ( ' )
or double quotes ( " ).
SELECT Name, Address
FROM PianoTuners IN "InstrumentTuners.xls;"
WHERE state='TN';
You can also specify a path and designate the type of file.
Note the position of the ( ; ):
SELECT Name, Address
FROM PianoTuners IN "C:\Music\InstrumentTuners.xls" "Excel 5.0;"
WHERE state='TN';
When used as an operator, IN can determine if the values of a specified
expression matches any values in a specified list.
This example determines if any piano tuners live in 'Knoxville', 'Nashville',
or 'Memphis'. A pair of single quotes must enclose each value and commas
must separate each value:
SELECT * FROM TennPianoTuners
WHERE City IN ( 'Knoxville', 'Nashville', 'Memphis' );
You can also add a NOT. This causes the query to select all values
other than those listed:
SELECT * FROM TennPianoTuners
WHERE City NOT IN ( 'Knoxville', 'Nashville', 'Memphis' );
The SELECT statement can optionally be followed
by one of these four predicates: ALL, DISTINCT, DISTINCTROW, TOP.
These limit the number of records returned.
The ALL predicate is the default, but it is rarely used.
Note that the following two code examples yield the exact same results:
SELECT *
FROM RockAndRoll
WHERE Artist = 'Elvis';
SELECT ALL *
FROM RockAndRoll
WHERE Artist = 'Elvis';
The DISTINCT predicate is used to omit duplicate values just in a field.
Consider a table of names, where you have the last name, "Jones", repeated numerous times.
This code returns only one "Jones":
SELECT DISTINCT LastName
FROM SongWriters;
The DISTINCTROW predicate is used to omit duplicate values in an entire record of fields.
This can be very useful when you use a INNER JOIN to join two tables together and you do not
want any duplication. This code create a table that does not repeat any of the last names:
SELECT DISTINCTROW LastName
FROM SongWriters INNER JOIN Performers
ORDER BY LastName;
The TOP predicate returns the specified number of records from the top of the specified table.
The following example returns the first 3 records:
SELECT TOP 3 MusicianName AS Musician, Instrument
FROM Musicians;
You can also carry out calculations on fields containing numeric values
using the aggregate functions:
AVG - average
COUNT - count how many items
MAX - maximum value
MIN - minimum value
STDEV - sample standard deviation
STDEVP - standard deviation
SUM - add the values
VAR - sample variance
VARP - variance
This next example uses the COUNT function to count the number of items that
have an entry in the SalePrice field (i.e. they are on sale) and returns that number
under the heading 'ReducedCount':
SELECT COUNT(SalePrice) AS ReducedCount
FROM Products;
...and this next one returns current prices
along with what the prices would be after a 10% increase:
SELECT Item, UnitPrice AS CurrentPrice, UnitPrice * 1.1
AS IncreasedPrice
FROM Products;
...and this one lists all items that are reduced
along with the price and the amount of the reduction:
SELECT Item, SalePrice AS Price, UnitPrice - SalePrice AS Reduction
FROM Products
WHERE SalePrice <> Null;
Of course, you may want to select fields from more than one table,
and you can do that as well.
In this case it is best to precede a field name
with the name of the table from which it comes,
followed by the dot operator ( . ).
You must do this for fields of the same name, but from different tables
that are used in the SELECT statement. The following example
uses two tables, Task and Assignment, and returns the names of all
Tasks belonging to Assignments that are incomplete:
SELECT Task.Name, Task.TaskID
FROM Task INNER JOIN Assignment
ON Task.TaskID = Assignment.TaskID
WHERE Assignment.CompletionDate Is Null;
As an alternative to using the explicit INNER JOIN syntax, columns
from multiple tables can be combined in a single query by specifying
the appropriate table list, and applying the filter condition in the
WHERE clause. This is illustrated in the following query, which
returns the same recordset as the previous example:
SELECT Task.Name, Task.TaskID
FROM Task, Assignment
WHERE Task.TaskID = Assignment.TaskID
AND Assignment.CompletionDate Is Null;
GROUP BY
The optional GROUP BY clause groups into a single record all records
that have identical values in a particular field or combination
of fields. The following example returns a list
of the different products in the Product field of Suppliers.
SELECT Product FROM Suppliers
GROUP BY Product;
HAVING
The HAVING clause is optional and qualifies
a GROUP BY clause. It is similar to the WHERE clause but determines
which records are displayed after they have been
grouped. The following example displays a list of different items,
along with their count, but only where there are more than one.
SELECT Item, Count(Item) AS Tally FROM Products
GROUP BY Item HAVING Count(Item) > 1;
ORDER BY
The ORDER BY clause can be used to dictate
the order of the records returned. The following example returns
records listed primarily in order of tune type (jigs then reels),
and then for each type the relevant names are also listed in
alphabetical order.
SELECT TuneType AS Type, Name FROM Tunes
WHERE TuneType = 'jig' OR TuneType = 'reel'
ORDER BY TuneType, Name;
WITH OWNERACCESS OPTION
In a multi-user environment utilizing secure workgroups, the
WITH OWNERACCESS OPTION declaration allows the query to be executed
with the same permissions as the owner of the query.
Copyright 2000 by Infinite Software Solutions, Inc.
Trademark Information
|