How to query the database correctly?

Question

SQL

Can you tell me how to make a query in the database? There are 3 tables:
chat, group, group_chat.

And there is a request to pull all chats that belong to the same group:

SELECT c.id, c.title, c.chat_id, c.is_active
FROM chat as c
LEFT JOIN group_chat as gc
ON gc.chat_id = c.id
WHERE gc.group_id = 9

But how to make a reverse request? In the sense of pulling out just those chats that do not belong to this group?
Just changing the last line where equal to not equal does not work:
WHERE gc.group_id != 9

Answer 1

SELECT *
FROM chat
WHERE NOT EXISTS ( SELECT NULL
                   FROM group_chat 
                   WHERE chat.id = chat_id
                     AND group_id = 9 )

By the way, by replacing NOT EXISTS with EXISTS, you can get the same result as in the query from the question text. With proper indexing, it will likely be even more efficient.