SQL SELECT

Class

SQL Applications

Purpose

Retrieves data from one or more tables or views

Syntax

SELECT [ALL | DISTINCT | DISTINCTROW | TOP <expN> [PERCENT]] * | [<t_alias>|<"t_alias">.]<column>|<"column"> | <constant> | <function> | <aggregate> [[ AS] <c_alias>] [,...] [, *]

FROM {OJ <"t_alias"> <table> [LEFT | RIGHT | FULL] OUTER JOIN <"t_alias"> <table2> ON <exp> = <exp>} | [FORCE] [<database>!]<table> | <view> [AS <t_alias> [, ...]]

[INNER JOIN | OUTER JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN (<nested select>) | [<database>!]<table2>

ON [<database>!]<table>.<column> = [<database>!]<table2>.<column> | CROSS JOIN | FULL [OUTER] JOIN [<database>!]<table2>]

[WHERE <condition>]

[GROUP BY <expr> | <column> | <number> [ASC | DESC] [,...]]

[HAVING <condition>]

[ORDER BY <expr> |<column> | <number> [ASC | DESC] [,...]]

[FOR UPDATE]

[INTO [,...] | ARRAY <array-name> | CURSOR <cursor-name> [NOFILTER | READWRITE] | DBF | TABLE <table-name> [DATABASE <database> [NAME <long table-name>]]

| SAVE AS [<database>!]<table-name> | XML <xml filename> [FORMAT <RECITAL | ADO>]] | TO FILE <text filename> [DELIMITED] | PRINTER [PROMPT] | SCREEN

[PREFERENCE <preference>] [NOCONSOLE] [PLAIN] [NOWAIT]

[UNION [ALL] <nested select>]

See Also

AGGREGATES, CREATE TABLE, FETCH, INSERT, SET TCACHE, SET XMLFORMAT, SYSTEM TABLES, OPERATORS, PREDICATES, PSEUDO COLUMNS, UPDATE

Description

The SELECT statement is used to retrieve data from one or more tables or views. It creates a logical table from other tables. A logical table is a temporary collection of data that satisfy conditions specified in a SELECT statement. To select data you must be the owner of the table or have already been granted SELECT privileges.

If no destination is specified for the results (INTO, SAVE AS or TO), then they are saved to a cursor, a temporary table with the alias name 'cursor'. This table is automatically opened in the next empty workarea and a BROWSE is issued.

Keywords	Description
ALL	Returns all the selected rows including duplicates. This is the default.
DISTINCT	Only returns one copy of each set of duplicate rows selected. Duplicate rows are those with matching values of each expression in the select list.
DISTINCTROW	Only returns one copy of each set of duplicate rows selected. Duplicate rows are those with matching values of the entire row, not just the columns in the select list.
TOP <expN> [PERCENT]	The <expN> defines the TOP number of rows to be returned from the specified select statement. The optional PERCENT keyword causes the <expN> to be used as the percentage of rows to be returned.
*	Selects all columns from all tables listed in the FROM clause.
t_alias	Provides a different name for the table. Other references to table name throughout the query must refer to this alias name. The t_alias can be enclosed in double-quotes if required.
column	The name of the column you are selecting. The column can be enclosed in double-quotes if required.
AS	Used to specify an alternative name for a table or a column
c_alias	Provides a different name for the column and column heading.
constant	This specifies a constant expression. See expressions for more information.
function	A 4GL function that may or may not include column names. See the function references for more information.
aggregate	An aggregate expression uses an aggregate function to summarize selected data from a table.
FORCE	The FORCE keyword specifies that the tables are joined in the order in which they are listed in the SELECT statement.
database	The name of the database to which the table belongs. Databases in Recital are implemented as directories containing files that correspond to the tables and associated files in the database. Operating System file protection can be applied individually to the files for added security. The directory is a sub-directory of the Recital data directory. The environment variable / symbol DB_DATADIR points to the current Recital data directory and can be queried using the GETENV() function. Files from other directories can be added to the database using the ADD TABLE command or via the database catalog and SET AUTOCATALOG functionality. The '!' character must be included between the database name and the table name.
table	The table name from which to select data. When data is being selected from encrypted tables, the table reference can include the three-part encryption key, enclosed in angled brackets, appended to the table name. The SET ENCRYPTION command allows a default encryption key to be defined. If the key is not included in the <table>, this default key will be used. If the default key is not the correct key for the table, an error will be given. If no default key is active, a dialog box will be displayed in Recital Terminal Developer to allow the user to enter the key.
view	The name of a view defined with the CREATE VIEW statement
{OJ … OUTER JOIN …}	Specifies the join type as left outer. This will return all the rows from the left table and matching rows from the right or a null row if no match is found.
{OJ … LEFT OUTER JOIN …}	Specifies the join type as left outer. This will return all the rows from the left table and matching rows from the right or a null row if no match is found.
{OJ … RIGHT OUTER JOIN …}	Specifies the join type as right outer. This will return all the rows from the right table and the matching rows from the left or a null row if no match is found.
{OJ … FULL OUTER JOIN …}	Specifies the join type as full outer. This will return all the rows from both tables.
<table2>	The name of the joined table in the query. When data is being selected from encrypted tables, the table2 reference can include the three-part encryption key, enclosed in angled brackets, appended to the table name. The SET ENCRYPTION command allows a default encryption key to be defined. If the key is not included in the <table2>, this default key will be used. If the default key is not the correct key for the table, an error will be given. If no default key is active, a dialog box will be displayed in Recital Terminal Developer to allow the user to enter the key.
ON <exp> = <exp>	The expression used to JOIN the two tables together.
INNER JOIN	Specifies the join type as inner. An inner join names the linking criterion used to find matches between the two tables. Only rows for which a match is found in both tables are returned.
OUTER JOIN	Specifies the join type as outer. An outer join takes two tables and displays all the rows from one table and the matching rows from the other or a null row if no matches are found.
LEFT [OUTER] JOIN	Specifies the join type as left outer. This will return all the rows from the left table and matching rows from the right or a null row if no match is found.
RIGHT [OUTER] JOIN	Specifies the join type as right outer. This will return all the rows from the right table and the matching rows from the left or a null row if no match is found.
CROSS JOIN	Specifies the join type as cross join. This will return a Cartesian product: all combination of rows.
FULL [OUTER] JOIN	Specifies the join type as full outer. This will return all the rows from both tables.
<nested select>	An additional SELECT statement.
ON <table>.column = <table2>.column	The expression used to JOIN the two tables together.
WHERE	This restricts the rows selected to those for which the condition is TRUE. If this clause is omitted than all rows are returned. The condition can be a SELECT sub-query.
GROUP BY	Groups the selected rows based on the value of an expression, the column name or number for each row and returns a single row of summary information for each group. The default, ASC, returns the data in ascending order, specifying DESC returns the data in descending order.
HAVING	Restricts the groups of rows returned to those groups for which the specified condition is TRUE. If you omit this clause then all rows are returned.
ORDER BY	Orders rows based on the value returned by an expression, a column name or number. The default, ASC, returns the data in ascending order, specifying DESC returns the data in descending order.
FOR UPDATE	Locks the selected rows.
INTO <data variable> [,…]	Specify data variables to receive the data retrieved by the select statement. The select statement can only be a singleton select. The data variables will be created if they do not exist and overwritten if they do.
INTO ARRAY <array-name>	Specify an array to receive the data retrieved by the select statement. The array is automatically created, so need not be pre-defined.
INTO CURSOR <cursor-name>	Specify a cursor to receive the data retrieved by the select statement. This saves the data into a temporary table in a workarea. The Recital/4GL SELECT command can be used to select the workarea for processing with Recital/4GL commands. The NOFILTER keyword is used for creating a cursor that can be used in subsequent queries. The READWRITE keyword is used to create a temporary modifiable cursor.
TABLE <table-name>	Specify a table to receive the data retrieved by the select statement. The table's database and a long name can optionally be specified using the DATABASE and NAME clauses respectively.
SAVE AS <table-name>	Specify a table name to be created and populated with the result of the select statement.
ADO>]	ADO>.
TO FILE [DELIMITED]	Saves the results to the specified text file. If the DELIMITED keyword is included, the results are written out in delimited format.
TO PRINTER	Sends the results to the currently defined printer. The optional PROMPT keyword is used to display a print dialog before printing.
TO SCREEN	Sends the results to the main screen or active window.
PREFERENCE	The PREFERENCE clause is used to save BROWSE window preferences.
NOCONSOLE	PRINTER) also being displayed on the screen.
PLAIN	The PLAIN keyword is used to disable the display of column headings.
NOWAIT	The NOWAIT keyword is used to continue program execution immediately after BROWSE window display instead of when the BROWSE window is closed.
UNION [ALL]	Combines the end result of the main SELECT statement with a secondary <nested select> SELECT statement. The ALL keyword specifies that duplicates should be retained.

Example

// Select all rows, including duplicates, from an encrypted table
EXEC SQL
SELECT ALL *
FROM enctab<key_1,key2,key_3>;
 
// Select "last_name" column from rows with a unique "last_name"
EXEC SQL
SELECT DISTINCT last_name FROM customer;
 
// Select "last_name" column from unique rows
EXEC SQL
SELECT DISTINCTROW last_name FROM customer;
 
// Select first 10 rows
EXEC SQL
SELECT TOP 10 * FROM accounts;
 
// Select first 50% of the rows
EXEC SQL
SELECT TOP 50 PERCENT * FROM accounts;
 
// Crystal Reports / ODBC style JOINS: LEFT OUTER, RIGHT OUTER, FULL OUTER
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
FROM
{OJ "customer" customer LEFT OUTER JOIN "accounts" accounts
ON customer.account_no = accounts.account_no};
 
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
FROM
{OJ "customer" customer RIGHT OUTER JOIN "accounts" accounts
ON customer.account_no = accounts.account_no};
 
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
FROM
{OJ "customer" customer FULL OUTER JOIN "accounts" accounts
ON customer.account_no = accounts.account_no};
 
// JOINS: INNER, LEFT OUTER, RIGHT OUTER, CROSS, FULL
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
FROM customer
INNER JOIN accounts
ON customer.account_no = accounts.account_no;
 
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
FROM customer
LEFT OUTER JOIN accounts
ON customer.account_no = accounts.account_no;
 
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
FROM customer
RIGHT OUTER JOIN accounts
ON customer.account_no = accounts.account_no;
 
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
FROM customer
CROSS JOIN accounts;
 
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
FROM customer
FULL OUTER JOIN accounts;
 
// JOINs with nested SELECTs
EXEC SQL
SELECT contactname FROM customers WHERE customerid IN (SELECT customerid FROM orders WHERE orderdate = {07/19/1996}); 
 
EXEC SQL
SELECT shipname FROM orders,customers WHERE orders.customerid = customers.customerid AND employeeid = (SELECT max(employeeid) FROM orders WHERE orderdate = {07/19/1996}) order by shipname;
 
EXEC SQL
SELECT contactname FROM customers WHERE ctod("07/19/1996") > ANY (SELECT orderdate FROM orders WHERE customers.customerid = orders.customerid);
 
EXEC SQL
SELECT orders.customerid FROM orders WHERE 30 > ALL (SELECT sum(quantity) FROM order_details WHERE orders.orderid = order_details.orderid);
 
EXEC SQL
SELECT orderid,customerid
FROM orders as o1
WHERE 2 < (SELECT quantity
FROM order_details as i1
WHERE o1.orderid = i1.orderid AND i1.productid = 71); 
 
EXEC SQL
SELECT lastname FROM employees WHERE exists
(SELECT * FROM orders WHERE employees.employeeid = orders.employeeid AND orderdate = CTOD("11/11/1996")) ;
 
EXEC SQL
SELECT lastname FROM employees WHERE not exists
(SELECT * FROM orders WHERE employees.employeeid = orders.employeeid AND orderdate = CTOD("11/11/1996")) ;
 
EXEC SQL
SELECT companyname,(select MAX(orderid) FROM orders as o1 WHERE o1.customerid = c1.customerid) FROM customers as c1;
 
// Multiple JOINs
EXEC SQL
SELECT customer.account_no, customer.state, state.descript, accounts.ord_value
FROM customer RIGHT OUTER JOIN accounts
ON customer.account_no = accounts.account_no,
customer INNER JOIN state
ON customer.state = state.state
ORDER BY account_no;
 
// Select account number and order value details for Massachusetts customers 
EXEC SQL
SELECT account_no, ord_value
FROM accounts
WHERE account_no in (SELECT account_no FROM customer WHERE state = 'MA')
ORDER BY account_no;
 
// Select all overdue accounts with 15% commission in sorted "last_name" order.
EXEC SQL
SELECT last_name, zip, balance, balance*1.15
FROM customer
WHERE balance > 0
ORDER BY last_name;
 
// Select total and average balance for all overdue accounts, grouped by "limit"
EXEC SQL
SELECT SUM(balance), AVG(balance)
FROM customer
WHERE balance > 0
GROUP BY limit;
 
// Select total and average balance for all overdue accounts, grouped by "limit" with column aliases
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
FROM customer
WHERE balance > 0
GROUP BY limit;
 
// Select total and average balance for all overdue accounts, grouped by "limit" with column aliases
// For Massachusetts customers only
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
FROM customer
WHERE balance > 0
GROUP BY limit
HAVING state = "MA";
 
// Save into an array
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
FROM customer
WHERE balance > 0
INTO ARRAY temp;
 
// Create a cursor
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
FROM customer
WHERE balance > 0
INTO CURSOR temp;
 
// Save as a table
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
FROM customer
WHERE balance > 0
INTO TABLE temp DATABASE mydbc;
 
//or
 
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
FROM customer
WHERE balance > 0
SAVE AS temp;
 
// Save in Microsoft® ActiveX® Data Objects XML format
// Any XML files created in the ADO format can be loaded
// with the Open method of the ADO Recordset object. 
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
FROM customer
WHERE balance > 0
SAVE AS XML temp.xml FORMAT ADO;
// In Visual Basic the file can then be loaded:
// Set adoPrimaryRS = New Recordset
// adoPrimaryRS.Open "temp.xml"
 
// Save in text file format
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
FROM customer
WHERE balance > 0
TO FILE temp.txt;
 
// Select all customer accounts that have an outstanding balance or are based in Massachusetts
EXEC SQL
SELECT account_no
FROM customer
WHERE state = 'MA'
UNION SELECT account_no
FROM accounts
WHERE balance > 0
ORDER BY account_no;

Products

Recital Database Server, Recital Mirage Server, Recital Terminal Developer