Search data in multiple tables

Question

Let’s say, I have 2 tables in Azure SQL – Employee & Address and both the tables have common field, say Address Id.

Questions:

1. If I create separate indexes for tables, can I search the data in both indexes from single search API? Is it possible to join 2 indexes? Just cross checking if such functionality exists.
2. Another option I am aware of is – load data from both the tables to single index using solution given in below. Is this possible only via .NET API? Can we develop it from Portal?
   https://learn.microsoft.com/en-us/azure/search/tutorial-multiple-data-sources
3. Any other recommended approach?

Answer 1

@Vaibhav Chaudhari , Apologies for the delay in responding here. To benefit the community find the answer, I'm copying the response from one of our PG, on which you'd started an internal discussion.

1. No, you cannot query multiple indexes with a single request, but you can issue two requests to query either table.
   Azure Search FAQ: Can I search across multiple indexes? No, this operation is not supported. Search is always scoped to a single index.
2. The .NET SDK calls into the REST APIs for our service. Anything you can do in the SDK you can do via the APIs as well.
   If you're asking whether two indexers can be configured to target 2 different datasources and the same single index, then yes that is also possible.

The index writes made by the indexer are all of type “mergeOrUpload” which means that it will only update fields that have non-null values.
As long as your indexers are configured to populate different fields within the target index, you should be able to compose multiple datasources into the same index.

Thanks for the great question and your patience!