The SQL 2011 Spec supports a
Feature F301, “
CORRESPONDINGin query expressions”:
What is this feature? How is it used? And, is it supported by any industry RDBMs?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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.
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.
It seems to be that
INTERSECT where supposed to accept tables as arguments, and accept a list of
COLUMNs simpliar to
CREATE TABLE foo ( col1 int, col2 text ); CREATE TABLE bar ( col2 text, col1 int ); TABLE foo UNION ALL CORRESPONDING BY (col1,col2) TABLE bar;
TABLEis shorthand for
SELECT * FROM foo,
It seems few databases implement it though. Feel free to comment below if a database does support this and I missed it.