Skip to main content

SELECT - Blockchain Query

Retrieves rows from the database and enables the selection of one or many rows or columns from one or many blockchains or containers. The full syntax of the SELECT statement is complex, but the main clauses can be summarized as:

SELECT select_list

FROM blockchain_source

[ WHERE search_condition ]

[ GROUP BY group_by_expression ]

[ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ]

[ FILTER filter_expression ]

[ SET_SEMANTIC select_statement ]

[ EXPORT export_expression ]

bSQL Syntax Conventions

Syntax​

-- Syntax for SELECT  
  
    SELECT [ ALL | DISTINCT ] { column_expression [,...n] }  
    { <query_expression> }   
    [ ORDER BY { order_by_column [ ASC | DESC ] } [ ,...n ] ] 
    [ FILTER <filter_expression> ]  
    [ <set_semantic> <select_statement> ] | 
    [ EXPORT <export_expression> FORMAT { JSON | XML | CSV } ]  

<query_expression> ::=   
    FROM { [LIFETIME] <blockchain_source> } [ ,...n ]
    [ WHERE search_condition ]
    [ GROUP BY *group_by_expression* ]  
    [ HAVING *search_condition* ]  

<filter_expression> ::= 
    { 
        top_uint 
        | (TOP = top_uint, OFFSET = offset_uint) 
        | PAGE(page_size, num_pages, start_page) 
    }

<set_semantic> ::=
    { 
          INTERSECT | INTERSECT ALL 
        | EXCEPT    | EXCEPT ALL 
        | UNION     | UNION ALL 
    }

<export_expression> ::=
    { = file_path | TO file_path }

<select_statement> ::= 
    SELECT ...

Remarks​

The order of the clauses in the SELECT statement is significant. Any one of the optional clauses can be omitted, but when the optional clauses are used, they must appear in the appropriate order.

Logical Processing Order of the SELECT statement​

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the blockchains defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 7, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. The actual physical execution of the statement is determined by the query optimizer and the order may vary from this list.

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. HAVING
  7. SELECT
  8. DISTINCT
  9. ORDER BY
  10. FILTER
  11. SET OPERATION
  12. FORMAT
  13. EXPORT

Permissions​

READ permissions required on the target blockchain(s).

READ permissions on all blockchains default to members of the admin fixed database role. READ permissions on user-defined blockchains default to the developer and reader fixed database roles. Members of the admin role can transfer permissions to other users.

Examples:​

The following examples use the DEMO DATABASE.

A. Using SELECT to retrieve rows and columns​

This section shows three code examples. This first code example returns all rows (no WHERE clause is specified) and all columns (using the *) from the companies blockchain. 

SELECT *  
FROM financial.companies;

This next example using blockchain aliasing to achieve the same result. 

SELECT c.*  
FROM financial.companies AS c;

This example returns all rows (no WHERE clause is specified) and a subset of the columns (name, sector) from the companies blockchain in the financial database. The second column heading is renamed to vertical and used to order the rows ascending by the vertical. 

SELECT name, sector AS vertical
FROM financial.companies   
ORDER BY vertical;

This example returns only the rows for companies in the sectors Information Technology or Telecommunications Services. 

SELECT name, sector AS vertical  
FROM financial.companies 
WHERE sector = "Information Technology"   
OR sector = "Telecommunications Services";

B. Using SELECT with column headings and calculations​

This example calculates the percentage of a companies current price in relation to its 52_week_high using the pricing blockchain. 

SELECT symbol, ( price / 52_week_high ) * 100 AS percentage 
FROM financial.pricing
ORDER BY percentage;

C. Using DISTINCT with SELECT​

The following example uses DISTINCT to generate a list of all unique sectors in the companies table. 

SELECT DISTINCT sector  
FROM financial.companies
ORDER BY sector;

D. Using GROUP BY​

The following example finds the total amount of companies in each sector. 

SELECT sector, COUNT(*)  
FROM financial.companies  
GROUP BY sector;

Because of the GROUP BY clause, only one row containing the count of each sector is returned for every sector.

E. Using GROUP BY and WHERE​

The following example puts the results into groups after retrieving only the rows with sector = Industrials. 

SELECT sector, COUNT(*)  
FROM financial.companies  
WHERE sector = "Industrials"
GROUP BY sector;

F. Using the HAVING clause​

This query uses the HAVING clause to restrict results. 

SELECT sector, COUNT(*) AS count
FROM financial.companies  
GROUP BY sector 
HAVING COUNT(*) > 10;

G. Using set operations.​

Set operations are used to combine two data sets and require column sets of the same data type to perform the operation. 

SELECT name
    FROM financial.companies  
UNION
SELECT name
    FROM financial.pricing;

See Also​