JET Database/Select

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