create table links(id1, id2, id3, id4, content) as
values (1,2,3,4,'Text')
, (1,2,5,4,'Other thing')
, (8,4,3,2,'More stuff');
explain
select *
from links l
inner join (
values (1,2,3,4),(1,2,5,4),(8,4,3,2)
) v(id1, id2, id3, id4) using(id1, id2, id3, id4);
| QUERY PLAN |
|---|
| Hash Join (cost=0.10..36.86 rows=1 width=48) |
| Hash Cond: ((l.id1 = "*VALUES*".column1) AND (l.id2 = "*VALUES*".column2) AND (l.id3 = "*VALUES*".column3) AND (l.id4 = "*VALUES*".column4)) |
| -> Seq Scan on links l (cost=0.00..20.70 rows=1070 width=48) |
| -> Hash (cost=0.04..0.04 rows=3 width=16) |
| -> Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=16) |
| … |
-- DANGER! With non-unique values, you get more rows than an IN clause would return
select *
from links l
inner join (
values (1,2,3,4),(1,2,5,4),(1,2,5,4)
) v(id1, id2, id3, id4) using(id1, id2, id3, id4);
| id1 | id2 | id3 | id4 | content |
|---|---|---|---|---|
| 1 | 2 | 3 | 4 | Text |
| 1 | 2 | 5 | 4 | Other thing |
| 1 | 2 | 5 | 4 | Other thing |
| … | ||||
-- If you aren't certain, select distinct
select *
from links l
inner join (
select distinct *
from (values (1,2,3,4),(1,2,5,4),(1,2,5,4)) vi(id1, id2, id3, id4)
) v using(id1, id2, id3, id4);
| id1 | id2 | id3 | id4 | content |
|---|---|---|---|---|
| 1 | 2 | 3 | 4 | Text |
| 1 | 2 | 5 | 4 | Other thing |
| … | ||||