Using a set in Tableau generally turns into a filter in the database, sometimes a filter that is joined back to the original data.
Tableau doesn't ever use the database's mechanism to put parameters into queries. All queries are completely generated within Tableau code.
You can use the performance recorder to see the queries Tableau generates.
Thanks for your lovely reply.
So, ultimately sets acts like a filter in tableau which hits the database.