EasyTable: Single-File Embedded Database,
a BDE replacement for Delphi and C++Builder



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 [MEMORY] 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.
MEMORY option specifies that in-memory table will be referenced or created.

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.
The ORDER BY clause specifies the sorting order for rows retrieved by the query.
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, Company, Contact

Use PASSWORD option to retrieve rows from encrypted table(s). Here is an example:

SELECT * FROM Encryption PASSWORD 'password'



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 EasyTable is that it supports ALL types of JOINS: Cartesian, Inner, Left Outer, Right Outer, Full Outer.
Moreover, the EasyTable 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.
EasyTable 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 EasyTable:

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