PostgreSQL comparing multiple rows in two separate tables

Question

I have two tables: Table 1 and Table 2. They're virtually identifical (in fact, that's what I'm looking to verify) with the exception of a serial_id column.

I'm trying to compare the values in column E, F, G, H of Tables 1 and 2 using a join on columns A, B, C, and D. Problem is, I need to compare specific serial_ids in Table 1 to Table 2. If I know the corresponding pairs of serial_ids, how do I go about doing this for multiple serial_ids?

For example, serial_id 423 in Table 1 corresponds to serial_id 1011 in Table 2.

SELECT
  table_1.A,
  table_1.B,
  table_1.C,
  table_1.D,
  table_2.E - table_1.E,
  table_2.F - table_1.F,
  table_2.G - table_1.G,
  table_2.H - table_1.H
FROM
  table_1 join table_2 using (A, B, C, D)
WHERE
  table_1.serial_id = 423 AND table_2.serial_id = 1011

What if I had 10 more pairs I'd like compare? Multiple union joins? Seems sub-optimal for many reasons.

arop arop · Accepted Answer · 2016-06-05T18:51:19

if you know all the ids you want to compare, and you are comparing the same columns you can use OR

Like:

WHERE
  (table_1.serial_id = 423 AND table_2.serial_id = 1011) OR
  (table_1.serial_id = x AND table_2.serial_id = y) OR
  ...

PostgreSQL comparing multiple rows in two separate tables

2 Answers