question

AndrewHuangBeyondsoftCorporation-5706 avatar image
0 Votes"
AndrewHuangBeyondsoftCorporation-5706 asked AndrewHuangBeyondsoftCorporation-5706 commented

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

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

azure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ThomasBoersma avatar image
0 Votes"
ThomasBoersma answered AndrewHuangBeyondsoftCorporation-5706 commented

Hi @AndrewHuangBeyondsoftCorporation-5706

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.