Postgres join where foreign table has ALL records

I have this people and tags table, like this,

CREATE TABLE people
AS
  SELECT *
  FROM ( VALUES
    (1,'Joe'),
    (2,'Jane')
  ) AS t(id,name);

CREATE TABLE tags
AS
  SELECT * FROM ( VALUES
    (1, 1, 'np'),
    (2, 1, 'yw'),
    (3, 2, 'np')
  ) AS t(id, people_id, tag);

If I want to find all people that contain both the np and yw tags in the tags table using a join, how would I do this efficiently in Postgres 9.6?

In this scenario, I should only get Joe's record from the people table.

Here are a couple of alternate approaches which don't involve using array_agg.

Use the INTERSECT operator against the the sets of people_id returned for each tag:

WITH both_tags AS (
    SELECT people_id FROM tags WHERE tag = 'np'
    INTERSECT 
    SELECT people_id FROM tags WHERE tag = 'yw')
SELECT *
  FROM people 
  WHERE id IN (SELECT people_id FROM both_tags);

Or you could use a COUNT(DISTINCT tag) = 2 to find people with both tags. (Note that the DISTINCT was added to handle the case that a person may have the same tag twice. If that is impossible, it's safe to remove.)

WITH both_tags AS (
    SELECT people_id
      FROM tags
      WHERE tag IN ('np', 'yw')
      GROUP BY people_id
      HAVING COUNT(DISTINCT tag) = 2)
SELECT *
  FROM people 
  WHERE id IN (SELECT people_id FROM both_tags);

This second approach would be easier to extend to accept an arbitrary number of tags, though the first approach would not be impossible.

StackExchange Database Administrators Q#168460, answer score: 3