Skip to main content

FROM and Joins

The FROM clause is required for a SELECT statement and specifies the blockchain(s) to be queried from.

bSQL Syntax Conventions

Syntax​

FROM { [LIFETIME] <blockchain_reference> } [ <join_expression> [,...n] ]

<blockchain_reference> ::= 
    <blockchain_name> [ AS blockchain_alias ] 

<blockchain_name> ::= 
    (<database_name>.)blockchain_alias
  
<join_expression> ::=   
{  
    <join_type> <blockchain_reference> 
        [ ON boolean_expression | USING(<column_list>) ]
}
  
<join_type> ::=   
    { 
      [ 
          INNER
        | LEFT  [OUTER] 
        | RIGHT [OUTER] 
        | FULL  [OUTER]  
        | NATURAL 
      ]  
      JOIN  
    } | ,
    
<column_list> ::=  
    column_name [ ,...n ]

Arguments​

The order of blockchain sources after the FROM keyword does not affect the result set that is returned. bSQL returns errors when duplicate names appear in the FROM clause.

LIFETIME
Specifies that we are querying from the history of the data source. For more information on time travel queries see the

blockchain_name
Is the name of a blockchain.

If the blockchain exists in another database, use a fully qualified name in the form database.object_name.

AS blockchain_alias
Is an alias for blockchain_source that can be used either for convenience or to distinguish a blockchain in a self-join. An alias is frequently a shortened blockchain name used to refer to specific columns of the blockchains in a join. If the same column name exists in more than one blockchain in the join, bSQL requires that the column name be qualified by a blockchain name or alias.

Joined blockchain​

A joined blockchain is a result set that is the product of two or more blockchains.

Join type​

Specifies the type of join operation.

INNER
Specifies all matching pairs of rows are returned. Discards unmatched rows from both blockchains. When no join type is specified, this is the default.

FULL [ OUTER ]
Specifies that a row from either the left or right blockchain that does not meet the join condition is included in the result set, and output columns that correspond to the other blockchain are set to NULL. This is in addition to all rows typically returned by the INNER JOIN.

LEFT [ OUTER ]
Specifies that all rows from the left blockchain not meeting the join condition are included in the result set, and output columns from the other blockchain are set to NULL in addition to all rows returned by the inner join.

RIGHT [OUTER]
Specifies all rows from the right blockchain not meeting the join condition are included in the result set, and output columns that correspond to the other blockchain are set to NULL, in addition to all rows returned by the inner join.

ON \<search_condition>
Specifies the condition on which the join is based. The condition can specify any predicate, although columns and comparison operators are frequently used, for example: 

SELECT *
FROM financials.companies AS c   
JOIN financial.pricing AS p  
ON p.symbol = c.symbol
  • When the condition specifies columns, the columns do not have to have the same name or same data type; however, if the data types are not the same, they must be casted using a bSQL function.
  • There can be predicates that involve only one of the joined blockchains in the ON clause. Such predicates also can be in the WHERE clause in the query. Although the placement of such predicates does not make a difference for INNER joins, they might cause a different result when OUTER joins are involved. This is because the predicates in the ON clause are applied to the blockchain before the join, whereas the WHERE clause is semantically applied to the result of the join. left_blockchain_source
    Is a blockchain source as defined in the previous argument. right_blockchain_source
    Is a blockchain source as defined in the previous argument.

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 a simple FROM clause​

The following example retrieves the symbol and price columns from the pricing blockchain. 

SELECT symbol, price  
FROM financial.pricing
ORDER BY price DESC
FILTER 10
SYMBOL [STRING] PRICE [FLOAT32]
AAPL    530.44
BLK     310.79
PCP     262.96
CF      261.09
GWW     256.95
V       225.56
ICE     212.64
SHW     206.71
PPG     200.07
EOG     189.92

B. Using the FULL OUTER JOIN syntax​

The following example returns the company metadata and any prices in the pricing blockchain financial database. It also returns any companies no price listed in the pricing blockchain, and any companies with a price not listed in the companies blockchain. 

SELECT * 
FROM companies FULL OUTER JOIN pricing
ON companies.symbol = pricing.symbol

E. Using LEFT JOIN syntax​

The following example joins two blockchains on symbol and preserves the unmatched rows from the left blockchain. The companies blockchain is matched with the pricing blockchain on the symbol columns in each blockchain. All companies, without a price appear in the result set. 

SELECT * 
FROM companies LEFT JOIN pricing
ON companies.symbol = pricing.symbol

F. Using the bSQL INNER JOIN syntax​

The following example returns all company names and their corresponding prices. 

-- By default, bSQL performs an INNER JOIN if only the JOIN   
-- keyword is specified.  
SELECT c.name, p.price
FROM financial.companies AS c  
INNER JOIN financial.pricing AS p  
ON p.symbol = c.symbol  
ORDER BY p.price DESC

A WHERE clause could also be used with this query to limit results. This example limits results to price values higher than '100': 

SELECT c.name, p.price
FROM financial.companies AS c  
INNER JOIN financial.pricing AS p  
ON p.symbol = c.symbol
WHERE p.price > 100
ORDER BY p.price DESC

G. Using the bSQL RIGHT OUTER JOIN syntax​

The following example joins two blockchains on symbol and preserves the unmatched rows from the right blockchain. The companies blockchain is matched with the pricing blockchain on the symbol column in each blockchain. All prices appear in the result set, whether or not they are assigned a company. 

SELECT * 
FROM companies RIGHT JOIN pricing
ON companies.symbol = pricing.symbol

See Also​