# JET Database/Select

Data can be retrieved from tables using the Select statement. The statement takes the following basic form:
| {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:
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 {column-list} is the asterisk, which specifies that all available columns from the tables listed in the {table-expression} 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 {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:
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:

## 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 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

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:

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

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

### Multiple joins

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

## Limit clause

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

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:

### DistinctRow

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.
DistinctRow is ignored when there is only one table selected from.

### Top n

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.