SQL Reference - CORRESPONDING

CORRESPONDING

CORRESPONDING is a set operator in SQL that is used to combine tables with similar column names, and perform operations only on the matching columns. This operator is typically used with UNION, EXCEPT, or INTERSECT operations during the merging of tables. It ensures only matching fields from each table are returned, ignoring columns that are present in one table but not in the other.

Example

SELECT *
FROM employees
CORRESPONDING BY (job_id)
NATURAL JOIN jobs;

Output

EMPLOYEE_ID  FIRST_NAME  LAST_NAME  EMAIL  JOB_ID  SALARY
-----------------------------------------------------------
100          Steven      King       SKING  AD_PRES 24000
101          Neena       Kochhar    NKOCH  AC_MGR  17000
102          Lex         De Haan    LDEHA  AC_MGR  17000
. . .

Explanation

The CORRESPONDING BY clause is used when performing a UNION, INTERSECT, or MINUS operation in oracle. This clause is used to specify the matching columns from the tables using their column names explicitly. It’s a way of letting Oracle take a specific column from one table and match it with the corresponding column in another table for comparison in the set operation. This example shows how CORRESPONDING BY can be used to evenly match the job_id column while joining employees and jobs table.

Example

CREATE TEMP TABLE orders(id SERIAL PRIMARY KEY, order_number INT);
CREATE TEMP TABLE returns(id SERIAL PRIMARY KEY, order_number INT, return_reason TEXT);
INSERT INTO orders(order_number) VALUES(1),(2),(3);
INSERT INTO returns(order_number, return_reason) VALUES(1, 'Damaged item'),(3, 'Wrong item');

SELECT DISTINCT * FROM orders
CORRESPONDING
WITHOUT INTERSECT
SELECT DISTINCT * FROM returns;

Output

 id | order_number
----+-------------
  2 |           2

Explanation

In the code, two temporary tables are created, named orders and returns, separately. Records are then inserted into both tables. The CORRESPONDING WITHOUT INTERSECT clause in the SELECT statement then compares the two tables and returns values from the orders table that do not exist in the returns table. Thus, the record where order_number equals 2 is not in the returns table leading it to be presented as the output.