FairCom — DB SQL query expressions

Description

A query expression selects the specified column values from one or more rows contained in one or more tables specified in the FROM clause. The selection of rows is restricted by a search condition in the WHERE clause. The temporary table derived through the clauses of a SELECT statement is called a result table.

Query expressions form the basis of other SQL statements and syntax elements:

SELECT statements are query expressions with optional ORDER BY and FOR UPDATE clauses.
CREATE VIEW statements specify their result table as a query expression.
INSERT statements can specify a query expression to add the rows of the result table to a table.
UPDATE statements can specify a query expression that returns a single row to modify columns of a row.
Some search conditions can specify query expressions. Basic predicates can specify query expressions, however, the result table can contain only a single value. Quantified and IN predicates can specify query expressions, however, the result table can contain only a single column.
The FROM clause of a query expression can itself specify a query expression, called a derived table.

Syntax

query_expression ::

WITH [ RECURSIVE ] with_list

| query_specification

| query_expression set_operator query_expression

| ( query_expression )

set_operator ::

{ UNION [ ALL ] | INTERSECT | MINUS }

with_list ::

with_list_element

| with_list_element , with_list

with_list_element ::

query_name tmptbl_column_commalist AS ( query_expr )

query_name ::

tblname

tmptbl_column_commalist ::

( [ , [table.]column_name ] )

query_specification ::

SELECT [ALL | DISTINCT] [TOP N] [SKIP N]

{

| { table_name | alias } . * [, { table_name | alias } . * ] ...

| { { { expr | NULL } [ [ AS ] [ ' ] column_title [ ' ] ] }

{ [ [ ' ] column_title [ '] = ] { expr | NULL } }

}

{ { { expr | NULL } [ [ AS ] [ ' ] column_title [ ' ] ] }

{ [ [ ' ] column_title [ '] = ] { expr | NULL } }

}

] ...

}

FROM table_ref [ { ctree ORDERED } ] [ , table_ref [ { ctree ORDERED } ] ...

[ WHERE search_condition ]

[ GROUP BY [table.]column_name

[, [table.]column_name] ...

[ HAVING search_condition ]

table_ref ::

table_name [ AS ] [ alias [ ( column_alias [ , ... ] ) ] ]

| ( query_expression ) [ AS ] alias [ ( column_alias [ , ... ] ) ]

| [ ( ] joined_table [ ) ]

| table_valued_function

joined_table ::

table_ref CROSS JOIN table_ref

| table_ref [ INNER | LEFT [ OUTER ] ] JOIN table_ref ON search_condition

Arguments

WITH [ RECURSIVE ] with_list

RECURSIVE specifies query_expr in with_list_element is a recursive query.

SELECT [ ALL | DISTINCT ]

DISTINCT specifies that the result table omits duplicate rows. ALL is the default, and specifies that the result table includes all rows.

SELECT [ SKIP N ]

SKIP skips the leading rows in result sets. N specifies the number of rows to be skipped.

SELECT [ TOP N ]

TOP limits the number of rows in result sets. N specifies the number of rows to be returned. When used in conjunction with SKIP, rows are first skipped, then the TOP N rows remaining are returned.

SELECT * | { table_name | alias } . *

Specifies that the result table includes all columns from all tables named in the FROM clause. For instance, the following examples both specify all the columns in the customers table:

SELECT * FROM customers;

SELECT customers.* FROM customers;

The tablename.* syntax is useful when the select list refers to columns in multiple tables, and you want to specify all the columns in one of those tables:

SELECT CUSTOMERS.CUSTOMER_ID, CUSTOMERS.CUSTOMER_NAME, ORDERS.*

FROM CUSTOMERS, ORDERS ...

SELECT { { { expr | NULL } [ [ AS ] [ ' ] column_title [ ' ] ] } | { [ [ ' ] column_title [ ' ] = ] { expr | NULL } } }

Specifies a list of expressions, called a select list, whose results will form columns of the result table. Typically, the expression is a column name from a table named in the FROM clause. The expression can also be any supported mathematical expression, scalar function, aggregate function, or scalar sub-query that returns one value.

The example for an expr which is a scalar sub-query is as follows.

SELECT e.ename,

( SELECT d.dname FROM dept d where d.deptno = e.deptno )

FROM emp e;

The optional 'column_title’ argument specifies a new heading for the associated column in the result table. Enclose the new title in single or double quotation marks if it contains spaces or other special characters:

SELECT order_value, order_value * .2 AS 'order "markup"' FROM orders;

ORDER_VALUE ORDER "MARKUP"

------------ ---------------

5000000.00 1000000.00

110000.00 22000.00

3300000.00 660000.00

You can qualify column names with the name of the table they belong to:

SELECT CUSTOMER.CUSTOMER_ID FROM CUSTOMERS

You must qualify a column name if it occurs in more than one table specified in the FROM clause:

SELECT CUSTOMERS.CUSTOMER_ID

FROM CUSTOMERS, ORDERS

Qualified column names are always allowed even when they are not required.

Note: You should not use column_title elsewhere in the query such as WHERE, GROUP BY and HAVING clauses.

FROM table_ref ...

Specifies optional FROM clause. Without the FROM clause, the select list can contain all possible expressions that do not have references to columns of tables. For example, the select list can contain constants, scalar functions, aggregate functions, etc. Other clauses such as WHERE and ORDER BY and set operators such as UNION can also be used without the FROM clause.

Examples

SELECT statement with constant expression:

SELECT 10;

SELECT statement with scalar function:

SELECT abs(-10);

SELECT statement with UNION set operator:

SELECT 10 UNION SELECT 20;

If a FROM clause is specified, it is used to specify one or more table references. Each table reference resolves to one table (either a table stored in the database or a virtual table resulting from processing the table reference) whose rows the query expression uses to create the result table. There are three forms of table references:

A direct reference to a table, view or synonym
A derived table specified by a query expression in the FROM clause
A joined table that combines rows and columns from multiple tables

The usage notes specific to each form of table reference follow.

If there are multiple table references, FairCom DB SQL joins the tables to form an intermediate result table that is used as the basis for evaluating all other clauses in the query expression. That intermediate result table is the Cartesian product of rows in the tables in the FROM clause, formed by concatenating every row of every table with all other rows in all tables.

FROM table_name [ AS ] [ alias ]

Explicitly names a table. The name listed in the FROM clause can be a table name, a view name, or a synonym.

alias is a name used to qualify column names in other parts of the query expression. Aliases are also called correlation names.

If you specify an alias, you must use it, and not the table name, to qualify column names that refer to the table. Query expressions that join a table with itself must use aliases to distinguish between references to column names.

For example, the following query expression joins the table customer with itself. It uses the aliases x and y and returns information on customers in the same city as customer ‘SMITH’:

SELECT y.cust_no, y.name

FROM customer x, customer y

WHERE x.name = 'SMITH'

AND y.city = x.city ;

Similar to table aliases, the column_alias provides an alternative name to use in column references elsewhere in the query expression. If you specify column aliases, you must specify them for all the columns in table_name. Also, if you specify column aliases in the FROM clause, you must use them-not the column names-in references to the columns.

FROM ( query_expression ) [ AS ] alias [ ( column_alias [ , ... ] ) ]

Specifies a derived table through a query expression. With derived tables, you must specify an alias to identify the derived table.

Derived tables can also specify column aliases. Column aliases provides an alternative name to use in column references elsewhere in the query expression. If you specify column aliases, you must specify them for all the columns in the result table of the query expression. Also, if you specify column aliases in the FROM clause, you must use them, and not the column names, in references to the columns.

FROM [ ( ] joined_table [ ) ]

Combines data from two table references by specifying a join condition. The syntax currently allowed in the FROM clause supports only a subset of possible join conditions:

CROSS JOIN specifies a Cartesian product of rows in the two tables
INNER JOIN specifies an inner join using the supplied search condition
LEFT OUTER JOIN specifies a left outer join using the supplied search condition

You can also specify these and other join conditions in the WHERE clause of a query expression. See Inner Joins and Outer Joins for further details on both ways of specifying joins.

{ ctree ORDERED }
Directs the FairCom DB SQL optimizer to join tables in a specified order. Use this clause when you want to override the SQL engine's join-order optimization. This is useful for special cases when you know in fact a particular join order results in better performance. Since this clause bypasses join-order optimization, carefully test queries that use it to make sure your specified join order is faster than relying on the optimizer. In addition, future FairCom DB SQL releases will further improve join ordering, outperforming manually created orders.

Note that the braces ( { and } ) are part of the required syntax.

SELECT sc.tbl 'Table', sc.col 'Column',

sc.coltype 'Data Type', sc.width 'Size'

FROM admin.syscolumns sc, admin.systables st

{ ctree ORDERED }

WHERE sc.tbl = st.tbl AND st.tbltype = 'S'

ORDER BY sc.tbl, sc.col;

WHERE search_condition

The WHERE clause specifies a search_condition that applies conditions to restrict the number of rows in the result table. If the query expression does not specify a WHERE clause, the result table includes all the rows of the specified table reference in the FROM clause.

The search_condition is applied to each row of the result table set of the FROM clause. Only rows that satisfy the conditions become part of the result table. If the result of the search_condition is NULL for a row, the row is not selected.

Search conditions can specify different conditions for joining two or more tables. See Inner Joins and Outer Joins for more information.

Refer to Search Conditions for details on the different kinds of search conditions.

SELECT *

FROM customer

WHERE city = 'COLUMBIA' AND state = 'MO' ;

SELECT *

FROM customer

WHERE city IN (

SELECT city

FROM customer

WHERE name = 'SMITH') ;

Full Text Index Search

FTS: Query Syntax

FTS: Quotes, Operators, Examples

GROUP BY column_name ...

Specifies grouping of rows in the result table:

For the first column specified in the GROUP BY clause, FairCom DB SQL arranges rows of the result table into groups whose rows all have the same values for the specified column.
If a second GROUP BY column is specified, FairCom DB SQL then groups rows in each main group by values of the second column.
SQL groups rows for values in additional GROUP BY columns in a similar fashion.

All columns named in the GROUP BY clause must also be in the select list of the query expression. Conversely, columns in the select list must also be in the GROUP BY clause or be part of an aggregate function.

Note: Prior to version 10.0 of FairCom DB SQL, rows in result tables were in ascending order of the GROUP BY columns. Subsequent use of a hashing scheme to group rows, results in the rows in the result table may not be in any specific order. Any explicit ordering must be defined by the SQL statement.

HAVING search_condition

The HAVING clause allows conditions to be set on the groups returned by the GROUP BY clause. If the HAVING clause is used without the GROUP BY clause, the implicit group against which the search condition is evaluated is all the rows returned by the WHERE clause.

A condition of the HAVING clause can compare one aggregate function value with another aggregate function value or a constant.

-- select customer number and number of orders for all

-- customers who had more than 10 orders prior to

-- March 31st, 1991.

SELECT cust_no, count(*)

FROM orders

WHERE order_date < to_date ('3/31/1991')

GROUP BY cust_no

HAVING count (*) > 10 ;

UNION [ALL]

Appends the result table from one query expression to the result table from another.

The two query expressions must have the same number of columns in their result table, and those columns must have the same or compatible data types.

The final result table contains the rows from the second query expression appended to the rows from the first. By default, the result table does not contain any duplicate rows from the second query expression. Specify UNION ALL to include duplicate rows in the result table.

-- Get a merged list of customers and suppliers.