IN is like writing
= ANY, e.g.
regress=> SELECT 1 = ANY (ARRAY[1, 2, 3]);
A simplistic interpretation of your question would be to say that you're asking for
= ALL, but that rarely makes sense:
regress=> SELECT 1 = ALL (ARRAY[1, 2, 3]);
What you really want is to find all
posts that have a corresponding
post_categories entry for each of a listed set of
As is usual in relational databases, as soon as you clearly frame the question, the solution starts writing its self.
You could do a multiple join, like Daniel suggests, but that's a pain because it requires dynamic SQL. Or you could use relational set operations.
Instead, I'd possibly use PostgreSQL's array features. This would be easier if you'd provided sample data, but I think you want something like the following, which finds each category that matches the post then does a check to see if all the categories match (the
INNER JOIN post_categories ON posts.id = post_categories.post_id
WHERE post_categories.category_id IN (1,2,3)
GROUP BY posts.id
HAVING array_agg(post_categories.category_id) @> ARRAY[1,2,3]
Untested, since you didn't provide sample data in
CREATE TABLE and
INSERTs form, but that's the general idea.
@> means "array-contains"; I suspect it'll be more efficient than using
array_agg(category_id ORDER BY category_id) and an equality test against a sorted
ARRAY literal, because you avoid the sort on aggregation.
You could leave out the
WHERE clause and this would still work, but it might be quite slow, as it'd fail to filter out posts in which none of the categories matched before doing aggregation.
ORDER BY is usually a bug. The database can return any set of 10 matching results it feels like, in any order.