SQL Intersect

create table test (
  id integer,
  pid integer,
  name varchar(2),
  val integer
);

insert into test
  (id, pid, name, val)
values
  ('1', '1', 'aa', '10'),
  ('2', '1', 'bb', '20'),
  ('3', '1', 'cc', '30'),
  ('4', '2', 'aa', '10'),
  ('5', '2', 'bb', '20'),
  ('6', '2', 'cc', '30'),
  ('7', '3', 'aa', '10'),
  ('8', '3', 'bb', '20'),
  ('9', '3', 'cc', '999');

select distinct pid from test 
where
pid in (select pid from test where (name,val) = ('aa',10))     
and pid in (select pid from test where (name,val) = ('bb',20))
and pid in (select pid from test where (name,val) = ('cc',30));



-- works on all RDBMS
select pid from test where (name,val) = ('aa',10)
and pid in (
    select pid from test where (name,val) = ('bb',20)
    and pid in (
        select pid from test where (name,val) = ('cc',30)
    )
);


-- works on most RDBMS, MySQL has no INTERSECT
select pid from test where (name,val) = ('aa',10)
intersect
select pid from test where (name,val) = ('bb',20)
intersect
select pid from test where (name,val) = ('cc',30);


-- works on all RDBMS
select a.pid
from   test a, test b, test c
where  (a.name,a.val) = ('aa',10) 
and    (b.name,b.val) = ('bb',20) 
and    (c.name,c.val) = ('cc',30) 
and    (a.pid = b.pid and b.pid = c.pid);

-- same as above. for JOIN purists
select a.pid
from   test a 
cross join test b
cross join test c
where  (a.name,a.val) = ('aa',10) 
and    (b.name,b.val) = ('bb',20) 
and    (c.name,c.val) = ('cc',30) 
and    (a.pid = b.pid and b.pid = c.pid);


-- just count
select t.pid
from test t
where (t.name, t.val) in ( ('aa', '10'), ('bb', '20'), ('cc', '30') )
group by t.pid
having count(*) = 3;                                                     


-- just sum
select t.pid
from test t
group by t.pid
having sum( ((t.name, t.val) in ( ('aa', '10'), ('bb', '20'), ('cc', '30') ))::int ) = 3                                                   

Output:

distinct and IN derived table
| pid |
| --- |
| 2   |
| 1   |


nested IN derived table
| pid |
| --- |
| 1   |
| 2   |


intersect
| pid |
| --- |
| 1   |
| 2   |


cross join using old syntax, table comma table
| pid |
| --- |
| 1   |
| 2   |

cross join
| pid |
| --- |
| 1   |
| 2   |

count
| pid |
| --- |
| 1   |
| 2   |


sum
| pid |
| --- |
| 1   |
| 2   |

All have same result, the first query's result is not ordered though.

Live test: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=3311663df90fd62f1194e50699767b0e

Execution plan: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=925dc71c30c9ef0f27bbc58f28b3bc33