Skip to main content
IN() is an operator, not a function.
Source Link
Daniel Hutmacher
  • 9.1k
  • 1
  • 26
  • 52

The IN() functionoperator is equivalent to a series of OR, which doesn't help you at all here.

Instead, I would build the query in a manner like this.

SELECT posts.id
FROM posts 
WHERE posts.id IN (SELECT post_id FROM post_categories WHERE category_id=1) AND
      posts.id IN (SELECT post_id FROM post_categories WHERE category_id=2) AND
      posts.id IN (SELECT post_id FROM post_categories WHERE category_id=3)

Admittedly, not a pretty construct, but I wrote it for readability. The following query might perform better:

SELECT posts.id
FROM posts
-- include:
WHERE posts.id IN (
        SELECT post_id
        FROM post_categories
        WHERE category_id IN (1, 2, 3)
        GROUP BY post_id
        HAVING COUNT(*)=3)
-- exclude:
    AND posts.id NOT IN (
        SELECT post_id
        FROM post_categories
        WHERE category_id NOT IN (1, 2, 3))

The second query assumes that the primary key of post_categories is (post_id, category_id). Since you haven't specified which rdbms you're running, you may have to tweak my code a bit to make it run.

Edit: The -- exclude: part eliminates posts that have any other category_id than 1, 2 or 3. You may want to skip this part depending on if you want to return a) all posts that have categories 1, 2 and 3 or b) all posts that have exactly categories 1, 2 and 3 and no other categories.

The IN() function is equivalent to a series of OR, which doesn't help you at all here.

Instead, I would build the query in a manner like this.

SELECT posts.id
FROM posts 
WHERE posts.id IN (SELECT post_id FROM post_categories WHERE category_id=1) AND
      posts.id IN (SELECT post_id FROM post_categories WHERE category_id=2) AND
      posts.id IN (SELECT post_id FROM post_categories WHERE category_id=3)

Admittedly, not a pretty construct, but I wrote it for readability. The following query might perform better:

SELECT posts.id
FROM posts
-- include:
WHERE posts.id IN (
        SELECT post_id
        FROM post_categories
        WHERE category_id IN (1, 2, 3)
        GROUP BY post_id
        HAVING COUNT(*)=3)
-- exclude:
    AND posts.id NOT IN (
        SELECT post_id
        FROM post_categories
        WHERE category_id NOT IN (1, 2, 3))

The second query assumes that the primary key of post_categories is (post_id, category_id). Since you haven't specified which rdbms you're running, you may have to tweak my code a bit to make it run.

Edit: The -- exclude: part eliminates posts that have any other category_id than 1, 2 or 3. You may want to skip this part depending on if you want to return a) all posts that have categories 1, 2 and 3 or b) all posts that have exactly categories 1, 2 and 3 and no other categories.

The IN() operator is equivalent to a series of OR, which doesn't help you at all here.

Instead, I would build the query in a manner like this.

SELECT posts.id
FROM posts 
WHERE posts.id IN (SELECT post_id FROM post_categories WHERE category_id=1) AND
      posts.id IN (SELECT post_id FROM post_categories WHERE category_id=2) AND
      posts.id IN (SELECT post_id FROM post_categories WHERE category_id=3)

Admittedly, not a pretty construct, but I wrote it for readability. The following query might perform better:

SELECT posts.id
FROM posts
-- include:
WHERE posts.id IN (
        SELECT post_id
        FROM post_categories
        WHERE category_id IN (1, 2, 3)
        GROUP BY post_id
        HAVING COUNT(*)=3)
-- exclude:
    AND posts.id NOT IN (
        SELECT post_id
        FROM post_categories
        WHERE category_id NOT IN (1, 2, 3))

The second query assumes that the primary key of post_categories is (post_id, category_id). Since you haven't specified which rdbms you're running, you may have to tweak my code a bit to make it run.

Edit: The -- exclude: part eliminates posts that have any other category_id than 1, 2 or 3. You may want to skip this part depending on if you want to return a) all posts that have categories 1, 2 and 3 or b) all posts that have exactly categories 1, 2 and 3 and no other categories.

Clarification and typo.
Source Link
Daniel Hutmacher
  • 9.1k
  • 1
  • 26
  • 52

The IN() function is equivalent to a series of OR, which doesn't help you at all here.

Instead, I would build the query in a manner like this.

SELECT posts.id
FROM posts 
WHERE posts.id IN (SELECT post_id FROM post_categories WHERE category_id=1) AND
      posts.id IN (SELECT post_id FROM post_categories WHERE category_id=2) AND
      posts.id IN (SELECT post_id FROM post_categories WHERE category_id=3)

Admittedly, not a pretty construct, but I wrote it for readability. The following query might perform better:

SELECT posts.id
FROM posts
-- include:
WHERE posts.id IN (
        SELECT post_id
        FROM post_categories
        WHERE category_id IN (1, 2, 3)
        GROUP BY post_id
        HAVING COUNT(*)=3)
-- includeexclude:
    AND posts.id NOT IN (
        SELECT post_id
        FROM post_categories
        WHERE category_id NOT IN (1, 2, 3))

The second query assumes that the primary key of post_categories is (post_id, category_id). Since you haven't specified which rdbms you're running, you may have to tweak my code a bit to make it run.

Edit: The -- exclude: part eliminates posts that have any other category_id than 1, 2 or 3. You may want to skip this part depending on if you want to return a) all posts that have categories 1, 2 and 3 or b) all posts that have exactly categories 1, 2 and 3 and no other categories.

The IN() function is equivalent to a series of OR, which doesn't help you at all here.

Instead, I would build the query in a manner like this.

SELECT posts.id
FROM posts 
WHERE posts.id IN (SELECT post_id FROM post_categories WHERE category_id=1) AND
      posts.id IN (SELECT post_id FROM post_categories WHERE category_id=2) AND
      posts.id IN (SELECT post_id FROM post_categories WHERE category_id=3)

Admittedly, not a pretty construct, but I wrote it for readability. The following query might perform better:

SELECT posts.id
FROM posts
-- include:
WHERE posts.id IN (
        SELECT post_id
        FROM post_categories
        WHERE category_id IN (1, 2, 3)
        GROUP BY post_id
        HAVING COUNT(*)=3)
-- include:
    AND posts.id NOT IN (
        SELECT post_id
        FROM post_categories
        WHERE category_id NOT IN (1, 2, 3))

The second query assumes that the primary key of post_categories is (post_id, category_id). Since you haven't specified which rdbms you're running, you may have to tweak my code a bit to make it run.

The IN() function is equivalent to a series of OR, which doesn't help you at all here.

Instead, I would build the query in a manner like this.

SELECT posts.id
FROM posts 
WHERE posts.id IN (SELECT post_id FROM post_categories WHERE category_id=1) AND
      posts.id IN (SELECT post_id FROM post_categories WHERE category_id=2) AND
      posts.id IN (SELECT post_id FROM post_categories WHERE category_id=3)

Admittedly, not a pretty construct, but I wrote it for readability. The following query might perform better:

SELECT posts.id
FROM posts
-- include:
WHERE posts.id IN (
        SELECT post_id
        FROM post_categories
        WHERE category_id IN (1, 2, 3)
        GROUP BY post_id
        HAVING COUNT(*)=3)
-- exclude:
    AND posts.id NOT IN (
        SELECT post_id
        FROM post_categories
        WHERE category_id NOT IN (1, 2, 3))

The second query assumes that the primary key of post_categories is (post_id, category_id). Since you haven't specified which rdbms you're running, you may have to tweak my code a bit to make it run.

Edit: The -- exclude: part eliminates posts that have any other category_id than 1, 2 or 3. You may want to skip this part depending on if you want to return a) all posts that have categories 1, 2 and 3 or b) all posts that have exactly categories 1, 2 and 3 and no other categories.

Source Link
Daniel Hutmacher
  • 9.1k
  • 1
  • 26
  • 52

The IN() function is equivalent to a series of OR, which doesn't help you at all here.

Instead, I would build the query in a manner like this.

SELECT posts.id
FROM posts 
WHERE posts.id IN (SELECT post_id FROM post_categories WHERE category_id=1) AND
      posts.id IN (SELECT post_id FROM post_categories WHERE category_id=2) AND
      posts.id IN (SELECT post_id FROM post_categories WHERE category_id=3)

Admittedly, not a pretty construct, but I wrote it for readability. The following query might perform better:

SELECT posts.id
FROM posts
-- include:
WHERE posts.id IN (
        SELECT post_id
        FROM post_categories
        WHERE category_id IN (1, 2, 3)
        GROUP BY post_id
        HAVING COUNT(*)=3)
-- include:
    AND posts.id NOT IN (
        SELECT post_id
        FROM post_categories
        WHERE category_id NOT IN (1, 2, 3))

The second query assumes that the primary key of post_categories is (post_id, category_id). Since you haven't specified which rdbms you're running, you may have to tweak my code a bit to make it run.