1

For a given list of primary keys (with a single id column as primary key), I want to retrieve the rows pertaining to those ids in order from the database. If the id doesn't exist in the database, then that specific row should be all nulls.

I have two such lists of equal length, for two different tables in the database. I want to do a side-by-side join of the two tables (on the server side if possible).

We have two id lists: [1, 2, 3, 4] and [3, 2, 4, 1].
Query using first list on first table retrieves:

 id    c1
----  ----
 1     a
 2     b
 3     c
 4     d

Query using second list on second table retrieves:

 id    c2
----  ----
 3     g
 2     h
 4     i
 1     j

Final result should be:

 c1    c2
----  ----
 a     g
 b     h
 c     i
 d     j
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
N M
  • 596
  • 4
  • 18

2 Answers2

1

Provide the lists as arrays and unnest in parallel:

SELECT t1.c1, t2.c2
FROM   unnest ('{1, 2, 3, 4}'::int[]
             , '{3, 2, 4, 1}'::int[]) AS i(id1, id2)
LEFT   JOIN tbl1 t1 ON t1.id = i.id1
LEFT   JOIN tbl2 t2 ON t2.id = i.id2;

Using 2x LEFT JOIN guarantees that every index is represented in the output, even if no row is found in one or both tables - then you get NULL values instead.

Assuming the id columns of tbl1 & tbl2 to be UNIQUE, or this can create a "cross join by proxy". See:

If you also want preserve the sort order, add WITH ORDINALITY:

SELECT t1.c1, t2.c2
FROM   unnest ('{1, 2, 3, 4}'::int[]
             , '{3, 2, 4, 1}'::int[]) WITH ORDINALITY i(id1, id2, ord)
LEFT   JOIN tbl1 t1 ON t1.id = i.id1
LEFT   JOIN tbl2 t2 ON t2.id = i.id2
ORDER  BY ord;

See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

Please try this 

SELECT AA.C1,BB.C2 FROM (
(SELECT ROW_NUMBER() OVER(ORDER BY a.id) As row_num, * FROM @tbl1 A ) AS AA
INNER JOIN
(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) As row_num, * FROM @tbl2 A) AS BB
ON AA.row_num =BB.row_num)
Hemang A
  • 1,012
  • 1
  • 5
  • 16