CODEWITHSUNDEEP
sqlpostgresqlloopsselect
Super_user_one
Super_user_one

Reputation: 77

Compare two tables with same row type PostgreSQL

I have two temporary tables of the same row type:

items
id - BIGINT
value - float

The two temp tables: called A and B have:

40 items in Table A
150 items in Table B

I want to compare every item in Table A with every item in Table B and return all items in which:

(a.value - b.value < 5)

into a third temporary table called Table C.

I can do this easily with loops, but I know loops are fairly slow, and I was wondering if there was an easy way to do it just with select statements.

Upvotes: 0

Views: 325

Answers (1)

Colin &#39;t Hart
Colin &#39;t Hart

Reputation: 7729

Something like this?

insert into c
select * from a
where exists (select 1 from b where a.value - b.value < 5);

Or do you also want all values from table B?

In that case,

insert into c
select * from a
where exists (select 1 from b where a.value - b.value < 5)
union
select * from b
where exists (select 1 from a where a.value - b.value < 5);

Upvotes: 1

Related Questions