If you haveBased on an arrayarray of institution_id
IDs, you can use unnest()
:
SELECT i.institution_id, t.*
FROM (SELECT unnest('{1, 3, 4, 5, 7, 9}'::int[]) AS institution_id) i
LEFT JOIN tablename t USING (institution_id)
SELECT i.institution_id, t.*
FROM unnest('{1, 3, 4, 5, 7, 9}'::int[]) institution_id
LEFT JOIN tbl t USING (institution_id);
Using LEFT JOIN
to the same effect.
Or just:
SELECT *
FROM unnest('{1, 3, 4, 5, 7, 9}'::int[]) institution_id
LEFT JOIN tbl t USING (institution_id);
The USING
clause in the join condition only adds a single instance of institution_id
to the output columns. SELECT *
may be exactly what you want. Or not. If all other columns of tbl
can be NULL
(institution_id
being the only not-null column), you can't tell the difference now between a missing row and a row of NULL
values.
To also preserve the order of elements in the input array:
SELECT *
FROM unnest('{7, 3, 4, 1, 5, 9}'::int[]) WITH ORDINALITY AS i(institution_id, ord)
LEFT JOIN tbl t USING (institution_id);
ORDER BY i.ord;
See: