» Documentation » SQL Reference

Select

Syntax

select_statement syntax

SELECT {* | ALL | [DISTINCT| | [TOP limit]] {  expression [[AS] alias]] [, ...] | @param = expression [, ...] }
  FROM table_sources
  [WHERE search_expression]
  [GROUP BY expression [, ...]]
  [HAVING search_expression]
  [RESTRICT offset, limit]
  [ORDER BY expression [ASC | DESC] [, ...]] 

table_sources syntax

{table_path [XLOCK ROW | XLOCK TABLE | READ CONSISTENT] | (select_statement)} [[AS] alias]
  [[INNER | {LEFT | RIGHT} [OUTER]] JOIN
    {table_path | (select_statement)} [[AS] alias]
      ON search_expression]* 

search_expression syntax

expression {= | > | < | >= | <= | != | <>} {expression | (sub_select)}
expression BETWEEN lower_expression AND upper_expression
expression IS [NOT] NULL
expression [NOT] LIKE 'sql_like_string'
EXISTS (sub_select)
expression [NOT] IN (sub_select)
search_expression {AND | OR} search_expression 

Description

The select statement retrieves tables of data from a database. The expressions in the select list are used to produce the row data. They can be any combination of internal functions and arithmetic operators on constants and valid column_names (or table_reference.column_names) produced by the table_sources. aliases can be specified to define titles for columns.

@param = expression 

Assign the projection value to the parameter. In this case, SELECT command does not return any rows to the client. @@ROWCOUNT will contain the number of read rows.

Table Sources

For each table, you may specify a lock type for concurrency control. XLOCK ROW puts an exclusive lock on each row that is used in the query. XLOCK TABLE puts an exclusive lock on the whole table.

The table_sources specify the database tables, or sub-queries, or any joined combination of these to draw the data from. A JOIN can be used to select from multiple tables. Scimore supports the standard SQL join types:

Join type

Explanation

INNER (default)

This will return a row for each combination of rows from the left and right tables: the Cartesian product. The ON clause is used to select only the matches desired.

LEFT [OUTER]

Like the INNER join, this will return a row for each combination matching the left and right tables. Additionally, a row is returned for any row that does not satisfy the join ON condition in the left table, for which, values from the right table will be NULL.

RIGHT [OUTER]

The same as the LEFT OUTER join, but with the left and right roles reversed.

Search Clauses

The WHERE clause will narrow the results of the selected table to whever the search_expression is true. The comparative operators will evaluate for numerical-numerical, datetime-datetime varchar-varchar combinations (where the codepage of the database determines the relative ordering), numerical-varchar, where the varchar will be type converted to a numeric type and datetime-varchar, where the varchar will be attempted to be type-converted to a datetime value. The same combinations can be used for the expression BETWEEN lower_expression AND upper_expression condition, which will return true only when expression lies exclusively between lower_expression and upper_expression.

The LIKE term can match a string expression against a constant search string, in the standard SQL form. Ie. _ matches any single character and % matches any number of any character. Specifying NOT LIKE inverts the result.

The IN search term will return true if the expression can be found within the single-column sub_select subquery given. Specifying NOT IN inverts the result.

Grouping

Query rows can be 'grouped' on columns so grouping functions can be used on the un-grouped columns. The HAVING search clause is another search that is applied after grouping. Grouping functions can be used with it aswell.

Restrict

The RESTRICT offset, limit or TOP limit clause is used to restrict the number of rows output to limit with an offset from the beginning of offset rows.

RESTRICT behaves in the same way as LIMIT and OFFSET in other SQL engines.

Sub-queries

sub_select follows exactly the same syntax as select_statement, except that it doesn't support the ORDER BY clause. Sub-query select statements can be used to generate a pseudo table to select from as a source table. Note that it is often more efficient to use joins than subqueries as table sources. Also, they can be used in the search expression with a comparator, where they must return one row. An EXISTS clause will return true if the subquery returns any rows whatsoever.