Unlock SQL’s CORRESPONDING Clause to Simplify UNION, INTERSECT, and EXCEPT in HSQLDB

1. Using CORRESPONDING

The CORRESPONDING keyword lets you perform set operations (UNION, INTERSECT, EXCEPT) on tables while automatically intersecting only the columns that exist in all participating tables. In HSQLDB you can replace a manual UNION ALL of three tables with a single query that projects the shared columns.

SELECT * FROM actor
UNION ALL CORRESPONDING SELECT * FROM customer
UNION ALL CORRESPONDING SELECT * FROM staff
ORDER BY first_name, last_name;

The result contains the columns first_name, last_name and last_update, which are common to actor, customer and staff tables.

2. Using CORRESPONDING BY

When you want to limit the intersection to specific columns, you can add a BY clause. This prevents unwanted columns (e.g., last_update) from being included.

SELECT * FROM actor
UNION ALL CORRESPONDING BY (first_name, last_name) SELECT * FROM customer
UNION ALL CORRESPONDING BY (first_name, last_name) SELECT * FROM staff
ORDER BY first_name, last_name;

The output now contains only first_name and last_name columns.

3. Discovering the Shared Columns

You can query the INFORMATION_SCHEMA to see which columns are common across the tables:

SELECT column_name FROM information_schema.columns WHERE table_name = 'ACTOR'
INTERSECT SELECT column_name FROM information_schema.columns WHERE table_name = 'CUSTOMER'
INTERSECT SELECT column_name FROM information_schema.columns WHERE table_name = 'STAFF';

This returns FIRST_NAME, LAST_NAME and LAST_UPDATE.

4. Other Dialects

The CORRESPONDING syntax is rarely found in other database systems. It may appear in future PostgreSQL releases; a community member, Vik Fearing, has a Git branch experimenting with it, and the jOOQ library is expected to add support soon.