PostgreSQL: multiple exists return incorrect result

Question

Please, see the following pastebin for a complete example. Why does Query 2 return different results than Query 1? I think Query 2 have correlated subqueries, so they should be executed once per row, but it works like subqueries is joined with OR not AND.

-- Query 1

SELECT id
FROM parent p
WHERE EXISTS(
    SELECT 1
    FROM child
    WHERE child.parent_id = p.id AND child.field_1 = 1 AND child.field_2 = 1
)

-- Query 2

SELECT id
FROM parent p
WHERE EXISTS(
    SELECT 1
    FROM child
    WHERE child.parent_id = p.id AND child.field_1 = 1
) AND EXISTS(
    SELECT 1
    FROM child
    WHERE child.parent_id = p.id AND child.field_2 = 1
)

Actual results for Query 1:

id
--
2
3
4

Actual results for Query 2:

id
--
2
3
4
5

jjanes · Accepted Answer · 2014-06-12 15:48:13Z

1

You get different results because you ran different queries.

In the first one, there needs to be a single child row which matches all 3 conditions.

In the second one, there needs to be a child row for each set of conditions, but it doesn't have to be the same child row both times.

answered Jun 12, 2014 at 15:48

jjanes

45k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: multiple exists return incorrect result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related