Select a conversation between exact users (by users IDs)

I’m woking on a simple chat app. It should allow users to run conversations user-to-user or in groups (multiple users). So here are my tables:

table users
ID   | username      | ...

table conversations
ID   | ...

table conversations_users
ID   | user_id       | conversation_id

Let’s say I pick a few users, ids 11, 22 and 33 and want to check, whether a conversation between these exact users (two, three or more) already exists in database. I could achieve this with multiple queries and some backend operations, but I’m pretty sure this would impact performance a lot.

Is it even possible with one single query?

---

Side question: if there is a simple solution, will it still be effective for really long tables (e.g. 1.000.000 conversations, ~3.000.000 rows in conversations_users) and multiple-users queries (let’s check whether a conversation between there 100 users exist)?

Answer

When you say:

…whether a conversation between these exact users…

I undersatand that you want in that conversation only these users and nobody else.
In this case just a simple:

sum(case when user_id in (11, 22, 33) then 1 else 0 end) = 3

does not give the correct result, because it would return all conversation_ids where these 3 users participate but maybe with others.
You need to compare against count(*):

select conversation_id
from conversation_users
group by conversation_id
having sum(user_id in (11, 22, 33)) = count(*);

I believe there are no duplicate user_ids for each conversation_id, so there is no need for count(distinct user_id).
For the case of a conversation between these 3 users and maybe others, you can use a where clause:

select conversation_id
from conversation_users
where user_id in (11, 22, 33)
group by conversation_id
having count(*) = 3;