fetching rows without any filtered child row

Question

The application manages (for the sake of simplicity) Topic and Messages posted to it by Users. Its structure is rather simple, and enforced by Foreign keys:

There are 3 tables, Topics, Users and Messages.

The Topic and User entities are linked through an (,) cardinality link embodied by the Message Entity.

Message: (IdTopic,IdUser,Message)

The other entities aren't of any interest by themselves.

Now the question:

I need to return every topics for which a specific user hasn't made any reply (Message). As you imagine, the database diagram is constitued of 3 tables.

How do I do it without a complex query? I am quite sure the answer is easy... But it's far too late in the night for me to think straight.

Answer 1

Using NOT EXISTS

SELECT t.*
  FROM TOPICS t
 WHERE NOT EXISTS(SELECT NULL
                    FROM MESSAGES m
                   WHERE m.idtopic = t.idtopic
                     AND m.isuser = @userid)

Using NOT IN

SELECT t.*
  FROM TOPICS t
 WHERE t.idtopic NOT (SELECT m.idtopic
                        FROM MESSAGES m
                       WHERE m.isuser = @userid)

Using LEFT JOIN / IS NULL

   SELECT t.*
     FROM TOPICS t
LEFT JOIN MESSAGES m ON m.idtopic = t.idtopic
                      AND m.isuser = @userid
    WHERE m.message IS NULL

Performance Concerns

NOT IN and NOT EXISTS perform better than LEFT JOIN / IS NULL if the columns being compared (idtopic, isuser) are not nullable. See this link for details.

Answer 2

From what you've described I believe that this query will work. I'm sure that it can be made to be more efficient, but I wanted it to work and be simple and then you could figure out how to improve it if you want.

SELECT t.IdTopics
FROM Topics t 
WHERE t.IdTopics NOT IN (SELECT m.IdTopics 
                          FROM Messages m 
                          WHERE m.IdUser = @SpecificUser
                         )