CODEWITHSUNDEEP
sqlpostgresqlinner-join
adam78
adam78

Reputation: 10068

Postgres SQL inner join syntax

Can some one please explain the inner join syntax in the SQL below:

 CREATE TABLE dataset AS 
  SELECT property.id 
       , amount.band
       , amount."value"
 FROM property  
 INNER JOIN (locality INNER JOIN amount ON locality.code = amount.code) ON (property.band = amount.band) AND (property.id = locality."UniqueId")

Why is the table locality defined before the second inner join? I've never come across such strange syntax.

Is there a more clearer way to right the same query so that someone can easily understand whats going on?

Upvotes: 3

Views: 416

Answers (1)

jarlh
jarlh

Reputation: 44766

FROM property  
  INNER JOIN (locality INNER JOIN amount ON locality.code = amount.code)
     ON (property.band = amount.band) AND (property.id = amount."UniqueId")

is the same as

FROM property  
  INNER JOIN amount ON property.band = amount.band AND property.id = amount."UniqueId"
  INNER JOIN locality ON locality.code = amount.code

When INNER JOINs only, you can re-order them as you want.

(Any specific reason to JOIN locality? You don't select any of its columns. Is it some kind of EXISTS, or do you want multiple rows returned if there are several matching rows in that table?)

Upvotes: 2

Related Questions