Query External and Dedicated Tables

Question

Hello,

I would like to know if it is feasible to use a dedicated table and an external table in the same SQL query, for example to do a join between both ?

Thank you,

Answer 1

Hi Wajih Arfaoui,

Thanks for reaching out to Microsoft Q&A.

Yes, It should. Suppose you have a dedicated table called 'dedtbl' and an external table called 'exttbl'. You can perform joins, filters, and aggregations across these tables.

SELECT    ds.ProductID,     ds.SalesAmount,     es.ProductName FROM    dedtbl ds JOIN 
exttbl es ON ds.ProductID = es.ProductID;

Ensure that the column definitions (data types, etc.) match between the dedicated and external tables. The actual data resides externally hence performance considerations may differ based on the data source. The benefit is that the external tables do not require compute resources, so you don’t need to unpause the dedicated sql pool to work with them.

Try and let me know.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.

Answer 2

Hi @Wajih Arfaoui

Thank you for reaching out to the community forum with your query.

In addition to the above answer. It is feasible to use both a dedicated table and an external table in the same SQL query, including doing a join between them.

To join an external table with a dedicated table, you can use the T-SQL syntax for joining tables. For example, you can use the following syntax to join an external table named "external_table" with a dedicated table named "dedicated_table" on a common column named "common_column":

SELECT * 
FROM external_table 
JOIN dedicated_table ON external_table.common_column = dedicated_table.common_column;

You can also use the same syntax to join multiple external tables with dedicated tables or with other external tables.

For more information on joining tables in Azure SQL Database, you can refer to Join tables in Azure SQL Database.

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.