Postgres SQL inner join syntax

Question

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?

This syntax is not specific to Postgres. It is allowed by the SQL standard and I have seen this with SQL Server as well - although I never understood the benefit of doing a join this way. — user330315
– user330315, Commented Dec 9, 2015 at 9:02
That syntax was removed from the ANSI SQL standard in SQL-2003 or SQL-2008. BTW, value is a reserved word, it needs to be delimited as "value". — jarlh
– jarlh, Commented Dec 9, 2015 at 9:03

jarlh · Accepted Answer · 2015-12-09 09:09:19Z

2

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?)

answered Dec 9, 2015 at 9:09

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

adam78 Over a year ago

I've updated the original sql - should have been (property.id = locality."UniqueId"). does that mean I need to move the AND to the locality join?

jarlh Over a year ago

@ozzii, yes, move that condition. (You are not allowed to have ON conditions on tables not yet specified in the join-chain. Read from left to right.)

Collectives™ on Stack Overflow

Postgres SQL inner join syntax

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related