JET Database/Select

Data can be retrieved from tables using the  statement. The statement takes the following basic form:

Here is a simple select statement that returns all rows in a single table, with every column in that table, and sorted by one column:

In JET SQL, much as in other SQL dialects, operations on tables, views and sub-queries are pretty much identical; for the rest of this section, table can be read as either a physical table, a view, or a sub-query utilised as a virtual table. More will be said about sub-queries and virtual tables later.

Column lists
The simplest  is the asterisk, which specifies that all available columns from the tables listed in the   are returned:

Individual columns can be selected from a table by specifying a comma-separate list of column names, and each column can be given an alias if desired:

Where there are multiple tables in the, resulting in the potential for two result columns to have the same name, the table name (or an alias for the table) can be used to specify which column to return. The following two statements achieve the same result, with the second statement using table aliases to make the statement a little more concise:

In addition to columns from the specified tables, columns in the  can be expressions. Such an expression can be a mathematical equation, a function call, a string concatenation, a constant, or a mix of these. Here is an example showing how an extended price can be computed from an item price and a quantity, and how a constant can be introduced as a column:

Joining tables
Querying data often requires looking in more than one table for an answer, especially when good database design principles have been followed. SQL allows a single  statement to do this by joining tables.

Joining two or more tables together can be achieved in JET SQL, much the same as in other SQL dialects. Here are some sample tables for examining join syntax in JET SQL:

Cartesian join
A Cartesian join, sometimes called a cross join, is where each row in the first table is joined with each row in the second table. This type of join can be useful when generating a list of all possible combinations from two or three categories. The syntax for a Cartesian join is the simplest join syntax; just list the tables in the  separated by a comma:

JobName             WorkerName bus driver          Mary doctor              Mary electrician         Mary painter             Mary sales clerk         Mary bus driver          Raphael doctor              Raphael electrician         Raphael painter             Raphael sales clerk         Raphael bus driver          William doctor              William electrician         William painter             William sales clerk         William bus driver          Bruce doctor              Bruce electrician         Bruce painter             Bruce sales clerk         Bruce bus driver          Juanita doctor              Juanita electrician         Juanita painter             Juanita sales clerk         Juanita (25 row(s) returned)

Inner join
There are two ways to achieve an inner join, where each row in the first table is joined to one or more rows in the second table by an expression. The first way is an extension of the Cartesian join, above, with a  expression:

Alternatively, an inner join expression can be given in the. This is no different in a practical sense to the previous example, but some people find that it more clearly shows the type of join and its constraints, as distinct from expressions used as selectors or filters in a :

JobName             WorkerName bus driver          Mary electrician         Raphael painter             William doctor              Bruce doctor              Juanita (5 row(s) returned)

Outer join
Joining two tables such that the first table is joined to no rows, or one or more rows, from the second table, requires a left outer join. Another way of looking at this join is that all rows selected from the first table are returned, whether or not there are rows from the second table to join to; an inner join only returns rows from the first table that can be joined to rows in the second table. In JET SQL, the left outer join requires a  statement in the , with the details of the join specified after the   part of the statement:

JobName             WorkerName bus driver          Mary doctor              Juanita doctor              Bruce electrician         Raphael painter             William sales clerk          (6 row(s) returned)

The converse of a left outer join, where the second table is joined to no rows, or one or more rows, from the first table, is a right outer join. In JET SQL, the right outer join requires a  statement in the  :

Multiple joins
When a  statement joins more than two tables, each pair must be nested within parentheses to group the joining tables together:

Limit clause
The  restricts the quantity of data returned by a query, and whether or not duplicate rows are returned. If it is not specified, then the query will return everything that matches the selection criteria, duplicates and all. This is the same as saying.

Distinct
removes any rows with the same values from the result set. Without, there would be three rows for the name Joe Bloggs with an account, but with   there will only be one Joe Bloggs row returned by the following statement:

FullName

Joe Bloggs Milly Jones Robert Green (3 row(s) returned)

DistinctRow
removes any duplicated table rows from the result set, taking into consideration all columns from tables that have columns in the  – subtly different to   which only takes columns in the   into consideration.

FullName

Joe Bloggs Milly Jones Robert Green Joe Bloggs (4 row(s) returned)

is ignored when there is only one table selected from.

Top n
is used to limit the number of rows returned, to either a specific number of rows, or to a percentage of the complete result set.

ID         FullName             JobTitle --- 1           Joe Bloggs           painter 2          Milly Jones          doctor 3          Robert Green         electrician 4          Joe Bloggs           author (4 row(s) returned)

ID         FullName             JobTitle --- 1           Joe Bloggs           painter 2          Milly Jones          doctor 3          Robert Green         electrician (3 row(s) returned)