How to use join(inner full left right) in synapse

2021-09-01T02:45:30.757+00:00

How to use join(inner full left right) in synapse

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,565 questions
0 comments No comments
{count} votes

Accepted answer
  1. Thomas Boersma 806 Reputation points
    2021-09-01T08:59:00.887+00:00

    Hi @Andrew Huang (Beyondsoft Corporation)

    Azure Synapse uses the same JOIN syntax as the other SQL servers / applications (see the green checkmark before Azure Synapse Analytics where it says 'Applies to' under the header of this page).

    Example with OPENROWSET:

    SELECT  
      *  
    FROM OPENROWSET(  
      BULK 'https://datalake.dfs.core.windows.net/data/Cleansed/Category/*.parquet',  
      FORMAT='PARQUET'  
    ) AS [Category]  
    INNER JOIN (  
      SELECT  
        *  
      FROM OPENROWSET(  
        BULK 'https://datalake.dfs.core.windows.net/data/Cleansed/SubCategory/*.parquet',  
        FORMAT='PARQUET'  
      ) AS [SubCategory]  
    ) SubCategory ON SubCategory.CategoryId= Category.Id  
    

    But, in my opinion, it is better to first create a VIEW or TABLE for every OPENROWSET and then perform the JOIN on the created VIEWs or TABLEs.

    2 people found this answer helpful.

0 additional answers

Sort by: Most helpful