Return to Answer

The twist, I think, is this: the Venn diagram is the right tool to visualize your objective, but the SQL JOIN is the wrong approach. You need a UNION (or equivalent) at the core of your query.

SELECT organization FROM admin_organization     WHERE user = 10
UNION
SELECT organization FROM moderator_organization WHERE user = 10

SELECT *
FROM  (
   SELECT organization AS id FROM admin_organization WHERE user = 10
   UNION
   SELECT organization FROM moderator_organization WHERE user = 10
   ) x
JOIN   organization USING (id);

It's typically (substantially) cheaper to apply UNION on just the ID column, and then join. It may even be a necessity, if some of the columns have types have data types with no equality operator. More common than one might think. See:

Assuming referential integrity, enforced with FK constraints, nothing is lost in the join.

For convenience, it added a column alias in the subquery (organization AS id), so that we can use the simplified join condition with USING (id) which, in turn, allows us to use the simple (and now correct) SELECT * in the outer query to get all columns of the table organization without a duplicate ID column.

SELECT *
FROM   organization o
WHERE  EXISTS (SELECT FROM admin_organization     WHERE user = 10 AND organization = o.id)
   OR  EXISTS (SELECT FROM moderator_organization WHERE user = 10 AND organization = o.id);

A naming convention with descriptive names can avoid some of the confusion and noise. Use organization_id (or org_id if you prefer short names and there is no ambiguity) for all three: admin_organization.organization, moderator_organization.organization, and organization.id.

The twist, I think, is this: the Venn diagram is the right tool to visualize your objective, but the SQL JOIN is the wrong approach. You need a UNION (or equivalent) at the core of your query.

SELECT organization FROM admin_organization     WHERE user = 10
UNION
SELECT organization FROM moderator_organization WHERE user = 10

SELECT *
FROM  (
   SELECT organization AS id FROM admin_organization WHERE user = 10
   UNION
   SELECT organization FROM moderator_organization WHERE user = 10
   ) x
JOIN   organization USING (id);

It's typically (substantially) cheaper to apply UNION on just the ID column, and then join. It may even be a necessity, if some of the columns have data types with no equality operator. More common than one might think. See:

While referential integrity is enforced with FK constraints, nothing is lost in the join.

For convenience, I added a column alias in the subquery (organization AS id), so that we can use the simple join condition USING (id) which, in turn, allows the simple (and now correct) SELECT * in the outer query to get all columns of the table organization without duplicate columns.

SELECT *
FROM   organization o
WHERE  EXISTS (SELECT FROM admin_organization     WHERE user = 10 AND organization = o.id)
   OR  EXISTS (SELECT FROM moderator_organization WHERE user = 10 AND organization = o.id);

A naming convention with descriptive names can avoid some of the confusion and noise. Use the column name "organization_id" (or "org_id" if you prefer short names and there is no ambiguity) for all three: admin_organization.organization, moderator_organization.organization, organization.id.

Terminology, visualization, example code, and column names - all slightly off target. The combination prevented you from finding proper solutions.

Venn diagrams are misleading for the purpose. a_horse provided the perfect clue in his comment:

The example SQL is misleading (or at least unclear) on top of that. It starts with:

That gets all columns from all joined tables. Not the expressed objective:

Lennart offered a valid translation into SQL. Here's a shorter, more efficient one. The core query is:

The resulting set of unique IDs may already be all that's needed. To flesh it out with more (or all) attributes of the organization (columns of table organization), now you JOIN to the table:

Venn diagrams are not ideal to visualize join operations like a_horse commented:

The twist, I think, is this: the Venn diagram is the right tool to visualize your objective, but the SQL JOIN is the wrong approach. You need a UNION (or equivalent) at the core of your query.

Title and the example query are misleading like that. What's more, you start with:

That gets all columns from all joined tables. Also not the expressed objective:

The core query is:

The resulting set of unique IDs may already be all that's needed. To flesh it out with more (or all) attributes of the organization (columns of table organization), now we JOIN to the table:

The example SQL is misleading on top of that. It starts with:

The example SQL is misleading (or at least unclear) on top of that. It starts with: