Switch to: V9V8V7V6V5

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 ]

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

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 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 select list element which means - all source table's fields, but excluding 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 keywords 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

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 source-records filter. It defines the conditions each row in the source tables must meet to qualify for the result. So, 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 key word 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 into query result.
  • If selection condition has FALSE value, the record is deleting out of query result.
  • If 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 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 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.

ORDER BY Clause

It is for sorting query result purpose. This clause contains 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 key word into the sorting clause.

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

To define the ascending sorting order you should use ASC key word.

If 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 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.