CODEWITHSUNDEEP
sqlpostgresqljoin
Bruno Peres
Bruno Peres

Reputation: 16355

Alternative to join statement in Postgresql

I have this scenario:

table_a
color   |   height  | size
'Blue'  |   10      |   1
'Red'   |   15      |   2
'Green' |   10      |   1
'White' |   5       |   null

table_b
name        |   age |   color       |   height
'Paul'      |   27  |   'Red'       |   15
'Joseph'    |   19  |   'Green'     |   10
'John'      |   22  |   'Purple'    |   5
'Eric'      |   34  |   'Blue'      |   21

Basically i trying to get all registers in table_b that height and color matches with respective values in table_a only if size in table_a is not null. I achieved this goal with a inner join and a where clause. Like this:

select * from table_b b
    inner join table_a a on a.height = b.height and a.color = b.color 
where
    a.size is not null

This works, but i'm not sure that this approach is a good (or the best) solution. The join statement is the best for this case or is there a best alternative?

I'm using Postgresql 9.4.

Thanks.

Upvotes: 1

Views: 1999

Answers (1)

Vedran Kopanja
Vedran Kopanja

Reputation: 1299

I don't see a problem with using an inner join, that's fine. Maybe just add a SELECT DISTINCT * to avoid duplicate entries as @Nick.McDermaid said.

But if you prefer the "old" way of joining, you can do it like this as well:

SELECT b.*
FROM table_b b, table_a a
WHERE a.size IS NOT NULL
      AND (b.height = a.height AND b.color = a.color);

Upvotes: 2

Related Questions