Accuracer: The First And Only BDE Alternative
Client-Server Single-File Embedded Database for Delphi / C++Builder / Kylix



SELECT Statement

Previous  Top  Next


Introduction

The SELECT statement is used to retrieve data from tables.

Syntax

SELECT [DISTINCT | ALL] [TOP n [, first_row_number]]
* | column [AS correlation_name | correlation_name], [column...]
[INTO destination_table]
FROM table_reference [AS correlation_name | correlation_name] [PASSWORD 'password_string']
[[[[NATURAL] [INNER | [LEFT | RIGHT | FULL] OUTER JOIN] table_reference [AS correlation_name | correlation_name]
[ON join_condition] | USING (join columns)]
[WHERE predicates]
[GROUP BY group_fields_list]
[HAVING predicates]
[ORDER BY order_list]
[UNION [ALL] [CORRESPONDING [BY (column_list)]] SELECT...]
[EXCEPT | MINUS [ALL] [CORRESPONDING [BY (column_list)]] SELECT...]
[INTERSECT [ALL] [CORRESPONDING [BY (column_list)]] SELECT...]

The SELECT clause specifies list of retrieved columns. Use asterisk to select all columns.
The ALL option (Default) retrieves all rows from specified tables.
The DISTINCT option retrieves only different rows. Here is a simple example that retrieves all different contacts from table Customer.
The TOP option specifies that only the first
n rows are to be output from the query result set. If first_row_number specified then n rows starting from this row number will be retreived.

SELECT DISTINCT Contact FROM Customer

The INTO clause specifies table name for storing data retrieved by SELECT statement.
The FROM clause specifies columns to be retrieved from table(s).
The WHERE clause specifies filtering conditions for the SELECT statement.
The GROUP BY clause divides a result set into groups.

SELECT CustNo, SUM(ItemsTotal)
FROM Orders
GROUP BY CustNo

The HAVING clause specifies a search condition for a group or an aggregate.


ORDER BY clause

Syntax:
1) ORDER BY <order_list>
or
2) ORDER BY INDEX IndexName

<order_list> ::= [TableName.]FieldName [ASC | DESC] [NOCASE]

The ORDER BY clause specifies the sorting order for rows retrieved by the query.

Syntax 1 allows you to specify fields for sorting.
If options are not specified the sorting order will be ascending and case-sensitive.
Option ASC means ascending sorting for this field.
Option DESC means descending sorting for this field.
Option NOCASE means case-insensitive sorting for string field.

The following example shows how to save rows from table Customers to a new table:

SELECT CustNo, Contact, Company, City, Country
INTO NewCustomer
FROM Customer
ORDER BY Country DESC, City DESC NOCASE, Company NOCASE, Contact

Syntax 2 allows you to specify the name of existing index for sorting.
This syntax can be used only for queries on single table.

Here is an example:

SELECT * from Customer_findKey ORDER BY INDEX ByCompany


Join clauses, Natural and Using operators

There are three types of joins that can be used in FROM clause to perform relational joins: Cartesian, Inner and Outer.

A Cartesian join combines source tables without any correlation. The result of such join is a table containing all columns from source tables and all combinations of all rows from source tables. For example, if first table contains 5 records and second table contains 10 records the result of Cartesian join of these tables will contain 50 records. The syntax is as follows:

FROM table_reference, table_reference [,table_reference...]

Here is an example:

SELECT * FROM Members,Departments


An Inner join includes all combined rows from source tables that have common values of specified columns.
An Outer join includes all combined rows from source tables that have common values of specified columns and rows from left, right or both source tables that does not have corresponding rows in other source table. Thus Outer joins can be LEFT, RIGHT or FULL.
The non-corresponding rows from left table contain NULL values for columns from right table and vice versa.
One of the main advantages of Accuracer is that it supports ALL types of JOINS: Cartesian, Inner, Left Outer, Right Outer, Full Outer.
Moreover, the Accuracer provides high performance on joins due to its flexible and well-designed architecture that excludes all unnecessary data transfers.


There are three ways of specifying inner or outer joins:

1) The common columns are specified in ON clause:
FROM table_reference [INNER | LEFT | RIGHT | FULL] JOIN table_reference
ON predicate
[[INNER | LEFT | RIGHT | FULL] JOIN table_reference ON predicate...]

2) The common columns are specified by Using operator (all source tables should contain these columns):
FROM table_reference [INNER | LEFT | RIGHT | FULL] JOIN table_reference USING (column_name[,column_name...])
[[INNER | LEFT | RIGHT | FULL] JOIN table_reference USING (column_name[,column_name...])...]

3) The common columns are all columns from source tables that have the same names.
FROM table_reference NATURAL [INNER | LEFT | RIGHT | FULL] JOIN table_reference
[NATURAL [INNER | LEFT | RIGHT | FULL] JOIN table_reference...]

Here are some examples of joins:

SELECT Contact, Customer.CustNo, Company, Orders.OrderNo, Orders.CustNo
FROM Customer INNER JOIN Orders
ON (Customer.CustNo = Orders.CustNo)
WHERE Contact LIKE 'E%'
ORDER BY Contact,Orders.CustNo,Orders.OrderNo

SELECT Contact, Customer.CustNo, Company, Orders.OrderNo, Orders.CustNo
FROM Customer INNER JOIN Orders Using (CustNo)
ORDER BY Contact,Orders.CustNo,Orders.OrderNo

SELECT cb.*
FROM Customer_Base cb NATURAL INNER JOIN Customer_Base

SELECT Contact, Customer.CustNo, Company, Orders.OrderNo, Orders.CustNo
FROM Customer NATURAL LEFT JOIN Orders
WHERE State IS NOT NULL
ORDER BY Contact,Orders.CustNo,Orders.OrderNo

SELECT Contact, Customer.CustNo, Company, Orders.OrderNo, Orders.CustNo
FROM Customer NATURAL RIGHT JOIN Orders
WHERE State IS NOT NULL
ORDER BY Contact,Orders.CustNo,Orders.OrderNo

SELECT Contact, Customer.CustNo, Company, Orders.OrderNo, Orders.CustNo
FROM Customer NATURAL FULL JOIN Orders
WHERE State IS NOT NULL
ORDER BY Contact,Orders.CustNo,Orders.OrderNo


UNION clause

Combines the results of two or more queries into a single result set consisting of all the rows belonging to all queries in the union. This is different from using joins that combine columns from two tables. The syntax is:

[UNION [ALL] [CORRESPONDING [BY (column_list)]] SELECT...]

Three basic rules for combining the result sets of two queries with UNION are:
·If CORRESPONDING option is not specified then the number and the order of the columns must be identical in both combined queries.  
·If CORRESPONDING option is specified then the listed columns must exist in both queries  
·The data types must be identical.  

ALL option incorporates all rows into the results, including duplicates. If not specified, duplicate rows are removed.

Example:

SELECT Company FROM customer_Base
UNION
SELECT Company FROM customer_Filter
UNION
SELECT Company FROM customer_Range


EXCEPT (MINUS) clause

Returns the result set consisting of the rows belonging to the first query, excluding the rows having identical ones in the second query, optionally retaining duplicates.

[EXCEPT [ALL] [CORRESPONDING [BY (column_list)]] SELECT...]

Three basic rules for EXCEPT are:
·If CORRESPONDING option is not specified then the number and the order of the columns must be identical in both queries.  
·If CORRESPONDING option is specified then the listed columns must exist in both queries  
·The data types must be identical.  

ALL option incorporates all rows into the results, including duplicates. If not specified, duplicate rows are removed.

Example:

SELECT * FROM customer_Range
EXCEPT CORRESPONDING BY (Company)
SELECT * FROM customer_Filter


INTERSECT clause

Returns the result set consisting of the rows belonging to the first query having identical ones in the second query, optionally retaining duplicates.

[INTERSECT [ALL] [CORRESPONDING [BY (column_list)]] SELECT...]

Three basic rules for INTERSECT are:
·If CORRESPONDING option is not specified then the number and the order of the columns must be identical in both queries.  
·If CORRESPONDING option is specified then the listed columns must exist in both queries  
·The data types must be identical.  

ALL option incorporates all rows into the results, including duplicates. If not specified, duplicate rows are removed.

Example:

SELECT * FROM customer_Range
INTERSECT CORRESPONDING BY (Company)
SELECT * FROM customer_Filter


WHERE Clause

Specifies the conditions that must be satisfied for all records retrieved by the query.
WHERE clause can include any supported functions and operators excepting aggregative functions.
Accuracer supports only
uncorrelated subqueries, i.e. you cannot use fields of the parent query in a subquery.

Example of correlated query - will not work in Accuracer:

SELECT field1 FROM table1 T1
WHERE T1.field2 = (SELECT MAX(field1) FROM table2 T2 WHERE T2.field2 = T1.field3);

Look at examples in Utils\Bin\SQLConsole\SQL\SubQuery folder.

Example:

SELECT * from Jpeg
WHERE ID = (SELECT MIN(ID) from jpeg)

SELECT * FROM orders
WHERE CustNo IN
(SELECT DISTINCT CustNo FROM customer WHERE (Company LIKE 'S%') and (CustNo < 2500))
ORDER BY CustNo

SELECT Count(*) as ROW_COUNT FROM jpeg
WHERE EXISTS
(SELECT * FROM jpeg WHERE (Name LIKE 'A%'))


© AidAim Software Accuracer: Client-server Database Single-file Database Delphi Database Embedded Databas