Switch to: V12V11V10V9V8V7V6V5

SELECT Statement

This command is for retrieving rows from the database and getting the selection of one or many rows or columns from one or many tables. SELECT's main statements are:



SELECT Grammar

direct_select_statement_multiple_rows
    :    query_expression 
             [vext_for_xml | vext_for_json | vext_for_report]

query_expression
    :    query_core [order_by_clause] [vext_select_limit]

query_core
    :    query_term  [ {UNION | EXCEPT} [ ALL ] query_term ]*  

query_term
    :    query_primary  [ INTERSECT [ ALL ] query_primary ]*

query_primary
    :    VALUES table_value_constructor_list 
    |    TABLE table_name 
    |    LINK link_name                                                         -- vext, v4.7
    |    vext_get_property
    |    vext_show_statement
    |    vext_recursive_table
    |    table_reference 
    |    SELECT [ALL | DISTINCT] select_list [INTO variable_name_list] [table_expression] 
table_expression
    :   FROM table_reference, ...
            [ WHERE search_condition ]
            [ GROUP BY grouping_column_reference_list [WITH ROLLUP] ]
            [ HAVING search_condition ]
            [ WINDOW window_name AS ( window_specification ), ... ]

grouping_column_reference_list
    :    select_expr_ref, ... 
table_reference
    :    non_join_table [[NATURAL][join_type] JOIN non_join_table [join_specification]]*
    |    pivoted_table

non_join_table
    :	table_name [ [ AS ] IDENT ]
    |	subquery   [ [ AS ] IDENT ]

subquery
    :    ( query_expression )

join_type
    :    { LEFT | RIGHT | FULL } [ OUTER ] 
    |    INNER 

join_specification
    :    ON search_condition 
    |    ON link_name [ TO {ParentDirection | ChildDirection} ] 
    |    USING ( column_name_list )
table_value_constructor_list
    :    row_expr, ... 

row_expr
    :    row_list_element
    |    ( row_list_element, ... ) 
              
row_list_element
    :    expr | NULL | DEFAULT 

Note, the order of statements is strict.

SELECT * FROM T
 
SELECT ** FROM T
 
SELECT f1, f2 FROM T
SELECT T.f1, T.f2 FROM T
 
SELECT f1 AS 'Name', f2 FROM T
 
SELECT 1 + 1
  • If there are any blanks in the field name, it should be included into brackets, e.g. [First Name] should be used for “First Name” column name.
  • Star (*) is used for selecting all fields in the table excluding methods (calculated fields) and internal fields (RecID and OID).
  • Double star (**) is used for getting all table fields (including methods and internal fields).
  • If FROM clause table list refers to more than one table and these tables have same-named fields you should qualify field name with table name - TableName.FieldName - for avoiding ambiguity.
  • Each table has two internal fields - “RecID” and “OID”. You can use it in your queries

SELECT Clause

Describes the columns of the resulting cursor. It is a comma-separated list. It can contain field names, constants, expressions. Each select-list element defines the source of the data for the column. Usually, it is a reference to a source table field the data is coming from but can be any other expression. One field will be created for each element of this list in the query results table. The fields in the result' table will be arranged in the same order as the elements of the selected list.

So, select-list element can represent:

  • field name identifying one of the fields contained in the tables which are listed in the FROM clause. DBMS just takes this field value for each record of the source table and putting it to the corresponding record of the query result table;
  • “pure data” (constants or non-correlated expressions - such as 1+1 ). Each record of the query result will contain the same value;
  • expression. DBMS should compute the value placed into the query result using the formula defined in the expression.
  • Star (*). It's a select list element which means - all source table's fields, but excluding the source table's methods and internal fields.
  • Double star (**). It's select list element which means - all source table fields, including source table's methods and internal fields.
  • ALL. This keyword is used by default. It means - select records including duplicates. You can omit this keyword in all cases.
  • DISTINCT. This keyword eliminates duplicate rows from the results of a SELECT clause.

FROM Clause

It contains a list of the tables from which the query retrieving the data.

The FROM clause can also contain JOIN specifications, which define the rules for getting join of two or more tables.

FROM clause consists of the FROM keyword followed by the comma-separated list of the table definitions.

WHERE Clause

The WHERE clause works like a source-records filter. It defines the conditions each row in the source tables must meet to qualify for the result. So, the resulting table can be blank - if no source rows meet the conditions. WHERE clause consists of WHERE keyword followed by the selection condition. All table records are scrolled one by one and the selection condition is applied to each of them. If the record comes at the selection condition, the value of the field is used to form the current result record. For each of the records, the selection condition can have one of three listed values.


WHERE clause consists of WHERE keyword followed by the selection condition. All table records are scrolled one by one and the selection condition is applied to each of them. If the record name comes at the selection condition, the value of the field is used form the current record. For each of the records, the selection condition can have one of three listed values.

  • If selection condition has TRUE value, the record will be included in the query result.
  • If selection condition has FALSE value, the record is deleting out of query result.
  • If the selection condition has NULL value, the record is deleting out of query result.


The most often WHERE searches are:

GROUP BY Clause

This clause gives the possibility to create row-selection as the usual query does. Then perform grouping on values mentioned in the columns of the group_by_list. And finally, select rows with grouping results only - one result row per one source records group.

HAVING Clause

HAVING clause almost every time is used in combination with GROUP BY clause. However, the syntax of SELECT command doesn't demand it. If HAVING clause is used without GROUP BY clause, DBMS considers the whole query result as one group. In other words, aggregative functions contained in HAVING clause should be applied to one and only one group, and this group consists of all records. In practice HAVING clause is used without corresponding GROUP BY clause very rarely.

WINDOW Clause

[new in v10.0]

The optional WINDOW clause has the general form

WINDOW window_name AS ( window_specification ), ...

where window_name is a name that can be referenced from OVER clauses or subsequent window definitions, and window_specification is

window_specification
    :    [existing_window_name]
         [window_partition_clause]
         [window_order_clause]
         [window_frame_clause]
 
window_partition_clause
    :    PARTITION BY column_reference, ...
 
window_order_clause
    :    ORDER BY column_reference [ASC | DESC] [ NULLS { FIRST | LAST } ], ...      
 
window_frame_clause
    :    { ROWS | RANGE } frame_start
    |    { ROWS | RANGE } BETWEEN frame_start AND frame_end
 
 
frame_start, frame_end
    :    UNBOUNDED PRECEDING
    |    uint PRECEDING
    |    CURRENT ROW
    |    uint FOLLOWING
    |    UNBOUNDED FOLLOWING

The purpose of a WINDOW clause is to specify the behavior of window functions appearing in the query's SELECT List. These functions can reference the WINDOW clause entries by name in their OVER clauses. A WINDOW clause entry does not have to be referenced anywhere, however; if it is not used in the query it is simply ignored. It is possible to use window functions without any WINDOW clause at all since a window function call can specify its window definition directly in its OVER clause. However, the WINDOW clause saves typing when the same window definition is needed for more than one window function.

Currently, window functions always require presorted data, and so the query output can be ordered according to one or another of the window functions' PARTITION BY/ORDER BY clauses. It is not recommended to rely on this, however. Use an explicit top-level ORDER BY clause if you want to be sure the results are sorted in a particular way.

Window functions are described in detail here.

ORDER BY Clause

order_by_clause
    :    [ORDER BY sort_specification_list] 

sort_specification_list
    :    sort_specification, ...

sort_specification
    :    select_expr_ref [ordering_specification] [null_order]

select_expr_ref
    :    column_reference
    |    UINT

ordering_specification
    :    ASC | DESC

null_order
    :    NULLS (FIRST | LAST)
uint_or_var
    :    UINT
    |    variable_name

It is for sorting query result purposes. This clause contains a comma-separated list of column names or (and) column order numbers. The first column is the main sorting key; the following columns are the more minor keys.

You can sort the query results by any element of the returning fields' list.

In this clause, you can use the ascending or descending sorting order. By default, the data is sorting in ascending order. To sort in descending order you should insert DESC keyword into the sorting clause.

SELECT city, region, sales
FROM offices
ORDER BY sales DESC

To define the ascending sorting order you should use ASC keyword.

If the resulting field used for the sorting is an expression - it is possible that it has no name which can be mentioned in the ORDER BY clause. In this case, you should use a column order number instead of its name.

SELECT city, region, (sales - target)
FROM offices
ORDER BY 3 DESC

In the given example the query result which we've got is sorted according to the third field. The values of this field are the difference between SALES and TARGET fields' values for each office value.

You can use simultaneously names and numbers of the fields in ORDER BY clause as ascending and descending sorting order.