Friday, April 12, 2019

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);


Output:
| pid |
| --- |
| 2   |
| 1   |


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


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


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


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

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

Live test: https://www.db-fiddle.com/f/vAspdD32aDzY7JsrZ65GF4/4

Execution plan: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=78628f85cfb53bad6793491f8ab2bef1

No comments:

Post a Comment