I'm in a situation where I'm being submitted a (potentially quite long) list of "match entities", each of which contains user data to match, along with a unique ID for that match information. Actual user data from matched users, along with the unique ID for that match, needs to be returned from my SQL query. So, let's say I were given 2 entities to try and match users against, both of which were trying to match phone numbers against a user's phone number; I could match any users associated with the entities submitted along with the "match entity"'s unique ID by using a union like this (
client_handle is the submitted unique ID):
SELECT [client_handle] = 'axtwe-wasst', [user_id], [email], [mobile_no], [firstname], [surname] FROM [dbo].[vAPP_UsersActive] WHERE [mobile_no] in ('+44 7747 122123', '+44 7904 223323') UNION SELECT [client_handle] = 'zjfft-albwq', [user_id], [email], [mobile_no], [firstname], [surname] FROM [dbo].[vAPP_UsersActive] WHERE [mobile_no] in ('+44 7758 444111', '+44 7758 444222', '+44 7758 444333')
The trouble with this method is that it could potentially result in a very large number of
UNIONs if large numbers of match entities are submitted to me. 1000 submitted match entities would result in 999
UNIONs. Is this actually an issue performance-wise, and is there a better way to achieve the result I want? Alternatively I could just loop through each submitted match entity and run a query to match each, but then I'd have 1000 separate queries if 1000 match entities are submitted, which seems even worse.