Natural Join with dissimilar column names in Oracle and Postgres

Question

I am using Oracle Database, but I also checked the scenario on PostgreSQL. I created two tables:

CREATE TABLE departments (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(255)
);

and

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(255),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(dept_id)
);

As you can see, the foreign key column in the department table is named department_id with a datatype of INT, whereas the referencing column in the employees table is named dept_id and has a datatype of INT. They neither share the same name nor have the same datatype.

Then I wrote this natural join statement:

SELECT * FROM employees
NATURAL JOIN departments;

Surprisingly, it gave me the result instead of an error. Considering the definition, one might naturally expect an error in this situation.

So, I thought maybe it's because of the foreign key reference, so I removed it:

ALTER TABLE employees
DROP CONSTRAINT SYS_C008253;

Then I ran the same query again, and once more, it provided the result instead of an error.

My question is, why am I getting a result and not an error? The definition says NATURAL JOIN needs columns with the same name, so there should be an error, right?

Answer 1

You're effectively joining on an empty list of conditions - natural join is based purely on the column names with no regard for any actual foreign key references in place. Here, it finds no matching column names and - similar to how omitting a list of conditions in a where works like where true - no conditions for a join works like join...on true. It's even put that way in the doc:

NATURAL is a shorthand form of USING: it forms a USING list consisting of all column names that appear in both input tables. As with USING, these columns appear only once in the output table. If there are no common column names, NATURAL JOIN behaves like JOIN ... ON TRUE, producing a cross-product join.

select* from(values(11,12)--no real tables, no constraints, no matching names
                  ,(21,22))as a(a1,a2)
natural join(values(13,14)--natural join still does its thing
                  ,(23,24))as b(b3,b4);

a1	a2	b3	b4
11	12	13	14
11	12	23	24
21	22	13	14
21	22	23	24

So you end up getting the same thing a cross join, a comma ,, a join...on true would get you, and all of these are perfectly valid expressions.

What you encountered is one of the reasons the use of natural join is discouraged.

---

Another fun example: no real tables, no constraints, no matching column names, no matching column types. Actually, no column names and types at all. In fact: no columns, period.
Just 2 empty rows from empty selects each:

select* from(select union all select)as a
natural join(select from generate_series(1,2))as b
--still joins the two and two together into four empty rows:

SELECT 4

Answer 2

Natural Join without common column name in both tables is actualy a cross join and it has nothing to do with primary or foreign keys.
There are no errors here but the result is different with common names. Here is an example of your described situation:

CREATE TABLE departments (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(255)
);
Insert Into departments 
VALUES (10, 'Dept 10'),
       (20, 'Dept 20'),
       (30, 'Dept 30'),
       (40, 'Dept 40');
Select * From departments;

--  table WITH NO common column name
CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(255),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(dept_id)
);
Insert Into employees
VALUES (1, 'John', 10), 
       (2, 'Jane', 10), 
       (3, 'Anne', 20), 
       (4, 'Mike', 30), 
       (5, 'Neal', 30), 
       (6, 'Alan', 40);

--   your SQL:
SELECT * FROM employees
NATURAL JOIN departments;

/*    R e s u l t :
emp_id  emp_name    department_id   dept_id     dept_name
------  ----------  -------------   --------    ---------
1       John                   10         10    Dept 10
1       John                   10         20    Dept 20
1       John                   10         30    Dept 30
1       John                   10         40    Dept 40
2       Jane                   10         10    Dept 10
2       Jane                   10         20    Dept 20
2       Jane                   10         30    Dept 30
2       Jane                   10         40    Dept 40
3       Anne                   20         10    Dept 10
3       Anne                   20         20    Dept 20
3       Anne                   20         30    Dept 30
3       Anne                   20         40    Dept 40
4       Mike                   30         10    Dept 10
4       Mike                   30         20    Dept 20
4       Mike                   30         30    Dept 30
4       Mike                   30         40    Dept 40
5       Neal                   30         10    Dept 10
5       Neal                   30         20    Dept 20
5       Neal                   30         30    Dept 30
5       Neal                   30         40    Dept 40
6       Alan                   40         10    Dept 10
6       Alan                   40         20    Dept 20
6       Alan                   40         30    Dept 30
6       Alan                   40         40    Dept 40   */

--  table WITH common column name
CREATE TABLE employees_2 (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(255),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
Insert Into employees_2
VALUES (1, 'John', 10), 
       (2, 'Jane', 10), 
       (3, 'Anne', 20), 
       (4, 'Mike', 30), 
       (5, 'Neal', 30), 
       (6, 'Alan', 40);

SELECT * FROM employees_2
NATURAL JOIN departments;

/*    R e s u l  t :
dept_id  emp_id      emp_name   dept_name
-------  ----------  ---------  ---------
     10           1  John       Dept 10
     10           2  Jane       Dept 10
     20           3  Anne       Dept 20
     30           4  Mike       Dept 30
     30           5  Neal       Dept 30
     40           6  Alan       Dept 40     */

Compared INNER and NATURAL Joins here.
See the fiddle here.