SQL CORRESPONDING

What is SQL CORRESPONDING?

Aligns columns by name in UNION, INTERSECT, or EXCEPT set operations instead of by ordinal position.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CORRESPONDING:

SQL CORRESPONDING Full Explanation

CORRESPONDING is an optional clause defined by the SQL standard that changes how set operators (UNION, INTERSECT, EXCEPT) pair columns from their left and right subqueries. Without the clause, columns are matched purely by position and both subqueries must return the same number of columns with compatible data types. When CORRESPONDING is specified, only columns that share identical names in both subqueries participate in the set operation. All other columns are ignored. An extended form, CORRESPONDING BY (column1, column2 …), restricts the match to an explicit subset of common column names and discards the rest. Key behaviors:- Available with UNION [ALL], INTERSECT, and EXCEPT.- Result columns appear once for each matched name, in the order they are listed in BY, or, if BY is omitted, in the order they appear in the first subquery.- Duplicate column names that resolve to different data types raise an error.- Cannot be used as a standalone statement; it is part of a set operator.- Not widely implemented outside of a few standards-compliant engines (for example IBM Db2, Teradata, DuckDB, and Firebird).

SQL CORRESPONDING Syntax

<subquery1>
UNION [ALL] CORRESPONDING [BY (column_list)]
<subquery2>;

-- interchangeable with INTERSECT / EXCEPT

SQL CORRESPONDING Parameters

BY (column_list) - list - Optional. Explicit list of common column names to include in the operation. All names must exist in both subqueries.

Example Queries Using SQL CORRESPONDING

-- Different column orders in each subquery
SELECT id, first_name, last_name, email
FROM   employees_north
UNION CORRESPONDING
SELECT id, email, last_name, first_name
FROM   employees_south;

-- Restrict to a subset of columns
SELECT product_id, country_code, sales_q1, sales_q2
FROM   sales_2023
INTERSECT CORRESPONDING BY (product_id, country_code)
SELECT product_id, country_code, sales_q1, sales_q3
FROM   sales_2024;

Expected Output Using SQL CORRESPONDING

First query: a combined result set of rows from both regions, containing the columns id, first_name, last_name, email, even though the original column orders differ
Second query: rows having matching product_id and country_code that exist in both years; only the two specified columns are returned

Use Cases with SQL CORRESPONDING

Merging data from two SELECT statements where column orders differ but names are consistent.
Performing set comparisons when additional, non-overlapping columns exist in either subquery.
Simplifying maintenance of long SELECT lists by relying on column names rather than manual position matching.

Common Mistakes with SQL CORRESPONDING

Assuming it is supported in all databases; many engines ignore or reject the clause.
Forgetting that unmatched or misspelled column names are silently dropped, leading to unexpected output.
Mixing incompatible data types under the same column name, triggering type errors.
Using CORRESPONDING without a set operator or inside a JOIN clause (not allowed).

First Introduced In

SQL:1999

Frequently Asked Questions

What is the purpose of CORRESPONDING in a UNION?

It aligns columns by identical names, letting you combine result sets even when column orders differ or when extra columns exist in either subquery.

How is CORRESPONDING BY different from plain CORRESPONDING?

Plain CORRESPONDING matches all column names that appear in both subqueries. CORRESPONDING BY lets you specify exactly which common columns to include, ignoring the rest.

Can I use CORRESPONDING with JOIN clauses?

No. CORRESPONDING is only defined for set operators (UNION, INTERSECT, EXCEPT) in the SQL standard.

What happens if the matched columns have different data types?

The database attempts to find a common supertype. If it cannot, the statement raises a type mismatch error.