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

Question

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

Answer 1

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.