What is the F301: the CORRESPONDING clause in query expression?

Question

The SQL 2011 Spec supports a CORRESPONDING clause,

Feature F301, “CORRESPONDING in query expressions”:

What is this feature? How is it used? And, is it supported by any industry RDBMs?

Answer 1

In a nutshell, CORRESPONDING was introduced into the SQL standard to make the syntax more in the spirit of the relational model (RM).

A full answer would inevitable involve a discussion about how the SQL language has strayed from the relational model specifically (e.g. nulls) and good language design generally (e.g. data types).

In essence, before 1992 some SQL syntax such as UNION relied on left-to-right ordering of columns. For example, without nitpicking about data types and their compatibility etc, the following would not work:

SELECT col1, col2 FROM Table1 WHERE col3 = 0
UNION
SELECT col2, col1 FROM Table1 WHERE col3 = 1

because the columns do not follow the same left-to-right ordering.

Of course, in the RM a relation has a set of attributes and a set by implication has no ordering. However, the original SQL UNION syntax implies that SELECT col1, col2 FROM Table1 is somehow not the same result as SELECT col2, col1 FROM Table1.

Therefore, UNION CORRESPONDING corrects the earlier design flaw i.e.

SELECT col1, col2 FROM Table1 WHERE col3 = 0
UNION CORRESPONDING
SELECT col2, col1 FROM Table1 WHERE col3 = 1

will give the correct result.

Answer 2

Syntax

From the SQL: 2011 spec (also in SQL-92), it is documented like this,

<corresponding spec> ::=
  CORRESPONDING [ BY <left paren> <corresponding column list> <right paren> ]

With the other relevant parts of the grammar being defined as,

<query expression body> ::=
  <query term>
  | <query expression body> UNION [ ALL | DISTINCT ]
  [ <corresponding spec> ] <query term>
  | <query expression body> EXCEPT [ ALL | DISTINCT ]
  [ <corresponding spec> ] <query term>

<query term> ::=
  <query primary>
  | <query term> INTERSECT [ ALL | DISTINCT ]
  [ <corresponding spec> ] <query primary>

<query primary> ::=
  <simple table>
  | <left paren> <query expression body>
  [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> 
]
  <right paren>

<simple table> ::=
  <query specification>
  | <table value constructor>
  | <explicit table>

<explicit table> ::=
  TABLE <table or query name>

Note, without the BY ( corresponding column list ) the effect is the same as a NATURAL JOIN and this form of CORRESPONDING should be avoided.

Example

It seems to be that UNION, EXCEPT and INTERSECT where supposed to accept tables as arguments, and accept a list of COLUMNs simpliar to JOIN with USING,

CREATE TABLE foo ( col1 int, col2 text );
CREATE TABLE bar ( col2 text, col1 int );

TABLE foo
UNION ALL CORRESPONDING BY (col1,col2) TABLE bar;

• TABLE is shorthand for SELECT * FROM foo,

Implementations

It seems few databases implement it though. Feel free to comment below if a database does support this and I missed it.

• HyperSQL : YES
• SASS: YES
• PostgreSQL : NO
• Microsoft SQL : NO