Oracle porting natural JOIN-CodePudding

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 ) )
        );

INSERT into holidays (HOLIDAY_DATE,HOLIDAY_NAME)
        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';

CONSTRAINT_NAME    CONSTRAINT_TYPE    COLUMN_NAME
SYS_C0075523509    C    HOLIDAY_DATE
IS_MIDNIGHT    C    HOLIDAY_DATE
HOLIDAYS_PK    P    HOLIDAY_DATE

CodePudding user response：

In a natural join, Oracle creates an "implicit" join clause based on common column names. If you want to switch to inner join, you'll have to do it yourself.

Natural:

SQL> SELECT constraint_name, constraint_type, column_name
  2  from user_constraints  natural join user_cons_columns
  3  where table_name = 'EMP';

CONSTRAINT_NAME                C COLUMN_NAME
------------------------------ - --------------------
SYS_C00105284                  C EMPNO
PK_EMP                         P EMPNO

Which columns are common? Let's see:

SQL> select column_name from all_tab_columns where table_name = 'USER_CONSTRAINTS'
  2  intersect
  3  select column_name from all_tab_columns where table_name = 'USER_CONS_COLUMNS';

COLUMN_NAME
--------------------
CONSTRAINT_NAME
OWNER
TABLE_NAME

SQL>

Inner:

SQL> SELECT a.constraint_name, a.constraint_type, b.column_name
  2  from user_constraints a join user_cons_columns b
  3    on a.constraint_name = b.constraint_name
  4   and a.owner = b.owner
  5   and a.table_name = b.table_name
  6  where a.table_name = 'EMP';

CONSTRAINT_NAME                C COLUMN_NAME
------------------------------ - --------------------
SYS_C00105284                  C EMPNO
PK_EMP                         P EMPNO

SQL>

OK, but - we know better. As we're querying user_cons... views, they contain data we own so we don't really need an owner. Moreover, constraint names are unique within a single schema, so we don't need table_name either. Therefore, this would do:

SQL> SELECT a.constraint_name, a.constraint_type, b.column_name
  2  from user_constraints a join user_cons_columns b on a.constraint_name = b.constraint_name
  3  where a.table_name = 'EMP';

CONSTRAINT_NAME                C COLUMN_NAME
------------------------------ - --------------------
SYS_C00105284                  C EMPNO
PK_EMP                         P EMPNO

SQL>