Make IN clause behave like AND clause I've asked the same question on stackoverlow, but got no answer, so that's why I'm asking it here (I've read that simple sql questions should be asked at SO, but I have no choice).

Suppose I have 3 tables: posts, post_categories and categories. I'm implementing some kind of page with filters where I can filter posts by categories. User can select multiple categories. When he selects more than one, they should be summed. I can't get it working. What I have now is simple IN() SQL clause:

SELECT posts.id, post_categories.category_id FROM posts 
JOIN post_categories ON posts.id = post_categories.post_id 
WHERE post_categories.category_id IN (1,2,3) LIMIT 10;


But it is not matching all ids, it is OR and I need AND. What I need here is to find all posts that have categories with id=1 AND id=2 AND id=3, but instead this query returns posts that have at least one category from the IN() list.
IN is like writing = ANY, e.g.

regress=&gt; SELECT 1 = ANY (ARRAY[1, 2, 3]);
 ?column? 
----------
 t
(1 row)


A simplistic interpretation of your question would be to say that you're asking for = ALL, but that rarely makes sense:

regress=&gt; SELECT 1 = ALL (ARRAY[1, 2, 3]);
 ?column? 
----------
 f
(1 row)


What you really want is to find all posts that have a corresponding post_categories entry for each of a listed set of category_id.

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 HAVING clause):

SELECT
   posts.id
FROM posts 
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) @&gt; ARRAY[1,2,3]
LIMIT 10


Untested, since you didn't provide sample data in CREATE TABLE and INSERTs form, but that's the general idea. @&gt; 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.

BTW, LIMIT without ORDER BY is usually a bug. The database can return any set of 10 matching results it feels like, in any order.