Skip to main content
improve overall
Source Link
Erwin Brandstetter
  • 182.1k
  • 28
  • 457
  • 620

If you haveBased on an arrayarray of institution_idIDs, 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:

If you have an array of institution_id, 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)

Using LEFT JOIN to the same effect.

Based on an array of IDs, you can use unnest():

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:

Source Link
Erwin Brandstetter
  • 182.1k
  • 28
  • 457
  • 620

If you have an array of institution_id, 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)

Using LEFT JOIN to the same effect.