Selects the specified column values from one or more rows contained in the tables or views specified in the query expression. The selection of rows is restricted by the WHERE clause. You can also use clauses like GROUP BY, HAVING, ORDER BY with the SELECT statement. These clauses are described in detail in the further sections. To limit the number of rows that are returned, you can implement a query paging solution using the OFFSET and FETCH clauses. The temporary table derived through the clauses of a select statement is called a result table.

When selecting rows in a multi-tenant table, a regular tenant can only view the rows in its partition, but a DBA or a super-tenant can view all the tenant partitions in the multi-tenant tables being accessed.

A super-tenant can also view a list of tenant IDs and tenant names that are associated with data from a tenant partition or from a group partition, by using the tenantid_tbl() and the tenantName_tbl() functions, respectively.

These functions logically evaluate to the set of IDs and names of the tenants that are associated with a tenant partition or with a group partition.They also enable the query to specify the tenants for which data should be selected.

When selecting rows from a partitioned table, the SELECT statement returns data from all the partitions created for the table as determined by the predicates (restrictions) in the statement.

Syntax

SELECT [ ALL | DISTINCT ] [TOP n]
 { *  
    |{table_name|alias} * [ , {table_name| alias} * ]...   
     | expr [[ AS ][ ' ] column_title [ ' ]]
       [,  expr [[ AS ][ ' ] column_title [' ]]]...
 } 
FROM table_ref [, table_ref]...[{ NO REORDER }] [ WITH (NOLOCK )]
    [ WHERE search_condition][ GROUP BY [ table ]column_name
      [,[table]column_name ]...
    [HAVING search_condition]; 

  [ORDER BY ordering_condition] 
  [OFFSET offset_value {ROW | ROWS } 
    [FETCH {FIRST | NEXT}fetch_value {ROW | ROWS} ONLY ]] 
  [WITH locking_hints] 
  [FOR UPDATE update_condition];
Note: The SELECT statement does not the support the use of the FOR UPDATE caluse and the ORDER BY clause or any aggreagte function in the same staement. For example, the statement SELECT * FROM table ORDER BY field1 FOR UPDATE; will return an error.

Parameters

column_list

See the COLUMN_LIST clause.

TOP n

See the TOP clause.

FROM table_list

See the FROM clause.

search_condition

See the Search conditions syntax in BNF.

WHERE search_condition

See the WHERE clause.

GROUP BY grouping_condition

See the GROUP BY clause.

HAVING search_condition

See the HAVING clause.

ORDER BY ordering_condition

See the ORDER BY clause.

OFFSET offset_value FETCH { FIRST | NEXT } fetch_value

See the OFFSET and FETCH clauses.

WITH locking_hints

See the WITH clause.

FOR UPDATE update_condition

See the FOR UPDATE clause.

Example

The following example displays the different forms of the SELECT statement:

The example below illustrates the use of SELECT statement.

SELECT * FROM pub.customer;

Assuming the user is mapped to a DBA or a super-tenant, the example below directs the SELECT statement to join three multi-tenant tables.

When joining three multi-tenant tables, the DBA or the super-tenant must make sure that data from one tenant, pub.mtcustomer, is joined only with the data owned by the same tenant in the other tables, pub.mtorder and pub.mtorderline; especially when primary keys, custnum and ordernum, are unique for a tenant, but not unique across all the tenants.

SELECT
    tenantName_tbl (c) as ten_name,
    c.name as c_name,
    COUNT (*)
FROM
    Pub.mtcustomer AS c
    INNER JOIN pub.mtorder AS o
    ON tenantId_tbl (c) = tenantId_tbl (o)
    AND c.custnum = o.custnum
    INNER JOIN pub.mtorderline AS ol
    ON tenantId_tbl (o) = tenantId_tbl (ol)
    AND o.ordernum = ol.ordernum
GROUP BY
    ten_name, c_name;