Home » SQL & PL/SQL » SQL & PL/SQL » Oracle porting a natural join (19.2)
Oracle porting a natural join [message #685407] Sun, 26 December 2021 18:43 Go to next message
Messages: 65
Registered: August 2021
I am reviewing code written by a previous colleague. It uses a "natural join", which I am unfamiliar with and never used.

I would like to change this into JOIN inner, outer, left….. whatever the correct equivalent is, which shows what is actually being joined.

Below is my test case. Any help would be greatly appreciated.

create table holidays(
          holiday_date DATE not null,
          holiday_name VARCHAR2(20),
          constraint holidays_pk primary key (holiday_date),
          constraint is_midnight check ( holiday_date = trunc ( holiday_date ) )

        WITH dts as (
          select to_date('25-NOV-2021 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'Thanksgiving 2021' from dual union all
          select to_date('29-NOV-2021 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'Hanukkah 2021' from dual
        SELECT * from dts;

SELECT constraint_name, constraint_type, column_name
from user_constraints  natural join user_cons_columns
where table_name = 'HOLIDAYS';

SYS_C0075523509    C    HOLIDAY_DATE

Re: Oracle porting a natural join [message #685408 is a reply to message #685407] Mon, 27 December 2021 00:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL Natural Join

A natural join is an inner equi join on columns with same name in both tables/views.

Re: Oracle porting a natural join [message #685409 is a reply to message #685407] Tue, 28 December 2021 02:27 Go to previous message
John Watson
Messages: 8804
Registered: January 2010
Location: Global Village
Senior Member
NATURAL JOIN is part of the standard. It facilitates lazy programming, but is prone to error. For example, SCOTT can run

select * from emp natural join dept;

and get the result he wants. But if HR runs

select * from employees natural join departments;

the result will not be so good.
Previous Topic: How to update the nested array in json clob column data using plsql..?
Next Topic: Two MAX Functions in a Single SELECT
Goto Forum:

Current Time: Sun Mar 26 20:48:08 CDT 2023