SQL query at least one of something

Question

I have a bunch of Users, each of whom has many Posts. Schema:

Users: id
Posts: user_id, rating

How do I find all Users who have at least one post with a rating above, say, 10?

I'm not sure if I should use a subQuery for this, or if there's an easier way.

Thanks!

Answer 1

To find all users with at least one post with a rating above 10, use:

SELECT u.*
  FROM USERS u
 WHERE EXISTS(SELECT NULL
                FROM POSTS p
               WHERE p.user_id = u.id
                 AND p.rating > 10)

EXISTS doesn't care about the SELECT statement within it - you could replace NULL with 1/0, which should result in a math error for dividing by zero... But it won't, because EXISTS is only concerned with the filteration in the WHERE clause.

The correlation (the WHERE p.user_id = u.id) is why this is called a correlated subquery, and will only return rows from the USERS table where the id values match, in addition to the rating comparison.

EXISTS is also faster, depending on the situation, because it returns true as soon as the criteria is met - duplicates don't matter.