SQL Standard – Understanding the F301 CORRESPONDING Clause

exceptionselectsql-standardunion

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?

Best Answer

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.

Related Question