7

Assuming we have two tables:

  1. contacts table having columns: id and name
  2. conversations table having columns: id and contact_id (FK to contacts.id)

The following two queries return the same data:

  • dot notation: SELECT contacts.name, contacts.id, conversations.id FROM contacts INNER JOIN conversations ON contacts.id = conversations.contact_id;

and

  • functional notation: SELECT contacts.name, contacts.id, conversations.id FROM contacts INNER JOIN conversations ON id(contacts) = contact_id(conversations);

For a purely theoretical reason, I want to learn more about the second (more functional) version. What is this syntax called and where can I learn more? Is this syntax in the SQL standard or just PostgreSQL? Is it performant? Why is it not used more widely?

srt32
  • 1,260
  • 1
  • 14
  • 27
  • http://www.postgresql.org/docs/9.3/static/queries-table-expressions.html and http://www.tutorialspoint.com/postgresql/postgresql_using_joins.htm and http://www.postgresqltutorial.com/postgresql-inner-join/ – Vivek S. Aug 20 '14 at 04:47
  • Thanks for the comment @dude but I can't see anything in those docs about the second approach `()`. Could you be more specific of where to look? – srt32 Aug 20 '14 at 04:49
  • 1
    It's not SQL-standard, it's a weird historical PostgreSQL quirk, and it should be avoided. Kind of surprised nobody's added a deprecation warning on it yet. – Craig Ringer Aug 20 '14 at 05:06
  • @CraigRinger: It's useful for computed fields / generated columns: http://stackoverflow.com/questions/11165450/store-common-query-as-column/11166268#11166268 I wouldn't expect that to be removed. – Erwin Brandstetter Aug 20 '14 at 05:26
  • 1
    @ErwinBrandstetter ANSI/SQL has different syntax for this purpose, and although it looks it can look attractive - it should not be used - it is trap for any readers with zero performance benefits against more usual solutions. – Pavel Stehule Aug 20 '14 at 06:13
  • Just curious: did this syntax dialect originate from `quel` (and did it later "leak" into the SQL-implementation) ? – wildplasser Aug 20 '14 at 10:45
  • 1
    I found this code in first Pre Community Postgres - PostgreSQL95 - named as projection methods - so 20 years ago (1994) this code was there. I have not access to older source. – Pavel Stehule Aug 20 '14 at 14:11
  • @PavelStehule I *retrieved* an old (Ingres) quel manual, and quel appears to use dotted notation. I also found some nineties "freeware" CD's which *could* contain tarballs of old sources ("open source" meant "BSD-style" licence, then) I'll check it tomorrow at work. – wildplasser Aug 20 '14 at 16:49
  • dotted notation was dropped few years ago - hidden append relation to FROM clause "add_missing_from" maybe in 9.0. I am not sure what was licence of pre community Postgres, maybe MIT instead BSD – Pavel Stehule Aug 20 '14 at 17:31
  • @PavelStehule: Agreed, I have never advertised the non-standard syntax except for "computed fields" (the answer I linked to). Not going to advertise it in the future, either. – Erwin Brandstetter Aug 21 '14 at 13:23

2 Answers2

3

"Functional notation" is an extension to the SQL standard and performance is identical to the standard "attribute notation" (a.k.a. "dot notation").

There are subtle differences how names are resolved. Like: column names take precedence over functions taking the composite type in attribute notation.

Attribute notation only works for functions taking a single parameter. So that's a limited alternative, and the canonical way is to use functional notation for functions (hence the name).

On the other hand, attribute notation is simply shorter (one dot versus two parens), more portable (complies to the standard) and generally the canonical way to table-qualify columns.

Find details in the manual.

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

Functional notation is terribly obsolete - it is an artifact from NON SQL era.

Don't use it in production projects. It has no impact on performance - the differences are solved on the parser and analyzer levels, but it does not make any sense with respect to the SQL standard.

jgritty
  • 11,660
  • 3
  • 38
  • 60
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94