Is it better to run 100 SQL queries with a WHERE clause that has one condition, or one query with a WHERE clause that has 100 conditions?

Question

Is it better to run 100 SQL queries with a WHERE clause that has one condition, or one query with a WHERE clause that has 100 conditions? For example, if I was looking to see whether 100 usernames are already present in a table, would it be better to iterate through 100 queries or do one complex query that looks for 100 usernames at a time.

Answer 1

I haven't done any tests but I would prefer one large query. With 100 queries, you have to connect to the database, send the query string, and handle the response/results 100 times. With one single query, you send one (larger) query, and get one response back. I don't know the exact cost of 100 context switches, but it's probably not insignificant. The database will probably have to do the same amount of work anyway with one large query. And if all you're checking is 1 username vs. 100 usernames, it's not a very complex query it's more like

select *
from users
where username in ('value1',...,'value100');

Answer 2

The fewer queries, the better.