JET Database/Select

From Wikibooks, open books for an open world
< JET Database
Jump to: navigation, search
JET Database

Introduction · Creating and connecting · Data types · Object names and constants · Data integrity · Security · Data definition language · Select · Insert · Update · Delete · Functions · Views and stored procedures · Manipulation tools · Integration and migration · Design tools

Clipboard

To do:
where clauses, grouping and grouped selection, ordering, external tables, sub-queries, virtual tables, OwnerAccess option

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

SELECT [{limit-clause}] {column-list}
    [INTO {new-tablename}]
    FROM {table-expression}
    [WHERE {where-clause}]
    [GROUP BY {group-by-expression}]
    [HAVING {having-clause}]
    [ORDER BY {order-list}]
    [WITH OwnerAccess OPTION]
{limit-clause}
  • All (default) – select all matching rows
  • Distinct – select matching rows that have unique values for the columns selected
  • DistinctRow – select matching rows that have unique values in all columns accessed, whether selected or not
  • Top n [Percent] – restrict the number of matching rows returned to n, or n% of all matching rows
{column-list} Either an asterisk (*) to specify that all available columns are returned; or a comma-separated list of expressions that evaluate to output columns, where each expression can be of the form:
expression [As display-name]

and can be consist of a column from a table or view, a function call, a constant, or a nested expression.

{new-tablename} The name of a non-existing table into which matching rows will be inserted.
{table-expression} A list of tables, views, or sub-queries, either comma separated or with explicit join directives. Each table / view / sub-query can have an optional alias, of the form:
expression [alias]

e.g.

From account_details a, contact_details c, invoices i

A table may be specified as being in an external database by using the In clause, specifying a Windows path to an external database in which the target table resides. The external database can be another JET database, or any other database that the JET drivers can connect to (include xBase and Paradox databases).

{where-clause} A set of expressions restricting the rows matched in the tables or views specified in {table-expression}. The where-clause can include multiple expressions separated by logical And, Or and Not operators, and grouped by parentheses (...)
{group-by-expression} A comma-separated list of expressions that evaluate to the output columns on which to group, when returning aggregated data (e.g. sums, counts)
{having-clause} A set of expressions restricting the matching rows when returning aggregated data
{order-list} A list of columns by which to sort the matching rows

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:

SELECT * FROM S1 ORDER BY a

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[edit]

The simplest {column-list} is the asterisk, which specifies that all available columns from the tables listed in the {table-expression} are returned:

SELECT * FROM S1

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:

SELECT a, b AS TheSecondColumn FROM S1

Where there are multiple tables in the {table-expression}, 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:

SELECT Invoice.InvoiceNumber, Invoice.CustomerNumber, Customer.CustomerName
    FROM Invoice, Customer
    WHERE Customer.CustomerNumber = 10 AND Invoice.InvoiceNumber = 123
 
SELECT i.InvoiceNumber, i.CustomerNumber, c.CustomerName
    FROM Invoice i, Customer c
    WHERE c.CustomerNumber = 10 AND i.InvoiceNumber = 123

In addition to columns from the specified tables, columns in the {column-list} 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:

SELECT ID, (ItemPrice * ItemQuantity) AS ExtendedPrice, 'A' AS ReportPart
    FROM OrderItem

Joining tables[edit]

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 Select 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:

Table J1
JobName
bus driver
doctor
electrician
painter
sales clerk
 
Table J2
WorkerName JobName
Mary bus driver
Raphael electrician
William painter
Bruce doctor
Juanita doctor

Cartesian join[edit]

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 {table-expression} separated by a comma:

SELECT J1.JobName, J2.WorkerName FROM J1, J2
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[edit]

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 {where-clause} expression:

SELECT J1.JobName, J2.WorkerName FROM J1, J2
    WHERE J1.JobName = J2.JobName

Alternatively, an inner join expression can be given in the {table-expression}. 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 {where-clause}:

SELECT J1.JobName, J2.WorkerName
    FROM J1
    INNER JOIN J2 ON J1.JobName = J2.JobName
JobName              WorkerName
-------------------- --------------------
bus driver           Mary
electrician          Raphael
painter              William
doctor               Bruce
doctor               Juanita
(5 row(s) returned)

Outer join[edit]

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 LEFT JOIN statement in the {table-expression}, with the details of the join specified after the ON part of the statement:

SELECT J1.JobName, J2.WorkerName
    FROM J1
    LEFT JOIN J2 ON J1.JobName = J2.JobName
JobName              WorkerName
-------------------- --------------------
bus driver           Mary
doctor               Juanita
doctor               Bruce
electrician          Raphael
painter              William
sales clerk          <NULL>
(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 RIGHT JOIN statement in the {table-expression}:

SELECT J1.JobName, J2.WorkerName
    FROM J1
    RIGHT JOIN J2 ON J1.JobName = J2.JobName

Multiple joins[edit]

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

SELECT *
    FROM ((J1 LEFT JOIN J2 ON J1.JobName = J2.JobName)
    INNER JOIN J3 ON J2.WorkerName = J3.WorkerName)
    LEFT JOIN J4 ON J3.x = J4.x

Limit clause[edit]

The {limit-clause} 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 Select All.

Table L1
ID FullName JobTitle
1 Joe Bloggs painter
2 Milly Jones doctor
3 Robert Green electrician
4 Joe Bloggs author
5 Lee Wong accountant
6 Joe Bloggs painter
7 Lee Wong plumber
 
Table L2
AccountNumber ID
123456789 1
987654321 2
134679258 3
976431852 4
456789123 4

Distinct[edit]

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

SELECT DISTINCT FullName
    FROM L1 INNER JOIN L2 ON L1.ID = L2.ID
FullName
--------------------
Joe Bloggs
Milly Jones
Robert Green
(3 row(s) returned)

DistinctRow[edit]

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

SELECT DistinctRow FullName
    FROM L1 INNER JOIN L2 ON L1.ID = L2.ID
FullName
--------------------
Joe Bloggs
Milly Jones
Robert Green
Joe Bloggs
(4 row(s) returned)

DistinctRow is ignored when there is only one table selected from.

Top n[edit]

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.

SELECT Top 4 * FROM L1
ID          FullName             JobTitle
----------- -------------------- --------------------
1           Joe Bloggs           painter
2           Milly Jones          doctor
3           Robert Green         electrician
4           Joe Bloggs           author
(4 row(s) returned)
SELECT Top 40 Percent * FROM L1
ID          FullName             JobTitle
----------- -------------------- --------------------
1           Joe Bloggs           painter
2           Milly Jones          doctor
3           Robert Green         electrician
(3 row(s) returned)