1

The PostgreSQL operation INTERSECT ALL does not seem to work. What am I missing?

The following query returns just one row containing the value two, but I am expecting two with the value as I am using intersect all.

(
   (select 1 as z) 
   union all 
   (select 2 as z) 
   union all 
   (select 2 as z)
) 
intersect all 
(select 2 as z)

Does anyone have a guess?

Gerardo Navarro Suarez
  • 393
  • 1
  • 3
  • 13
  • 1
    Unrelated, but: there is no need to put each query of a UNION between parentheses. –  Dec 18 '19 at 14:11

2 Answers2

2

There is only one row (with a value of 2 for the column z) in the second operand to INTERSECT ALL so only that one can be used to find a matching partner in the other operand.

Add a second row to the second operand of the INTERSECT ALL and you'll have two rows in the result.

(SELECT 1 z
 UNION ALL
 SELECT 2 z
 UNION ALL
 SELECT 2 z)
INTERSECT ALL
(SELECT 2 z
 UNION ALL
 SELECT 2 z);

Or you might instead want a join.

SELECT *
       FROM (SELECT 1 z
             UNION ALL
             SELECT 2 z
             UNION ALL
             SELECT 2 z) x1
            INNER JOIN (SELECT 2 z) x2
                       ON x2.z = x1.z;
sticky bit
  • 36,626
  • 12
  • 31
  • 42
1

This is how it works in all version, not just 11.

The single value of '2' on the right side of the INTERSECT ALL is consumed upon matching, and can't match multiple times.

Do you really want WHERE EXISTS (..) instead?

jjanes
  • 37,812
  • 5
  • 27
  • 34