Hello Nick Wong,
Please correct me if my understanding is wrong. You are looking for cross database queries across tables in two different SQL pools under one workspace.
If yes, cross database queries are not supported in synapse at this time.
This feature request is indeed on the backlog for a future release
Feedback items:
https://feedback.azure.com/d365community/idea/3c98f9e3-0925-ec11-b6e6-000d3a4f07b8
https://feedback.azure.com/d365community/idea/ebb4a2dd-0925-ec11-b6e6-000d3a4f07b8
As a work aroud, you can use other methods to join tables from two different SQL pools, such as using ADF to copy data from the two SQL pools into a single destination, and then performing the join operation on the destination data.
I hope this answers your question.
Please let me know if you have any further questions.
If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.