Same ID field on multiple tables

Question

So I've been given a task where i should present the following:

ID, LNAME, FNAME, MNAME, BIRTH_DATE, RELG_CODE, NAT_CODE, PT_STATUS, RM_NO, DTTM_ADM

The tables are:

HISR_CODES, PASR_NAMES, PASR_PROFILE, PAST_PATIENT_ADM

--Viewing them using DESC--

So while I viewed them, I was told that the ID on these tables are the same. So what I did so far in the coding (I'll finish the rest but I need to make sure this works first):

SELECT
    A.ID,
    A.LNAME,
    A.FNAME,
    A.MNAME,
    A.BIRTH_DATE,
    C.RELG_CODE,
    C.NAT_CODE,
    B.PT_STATUS,
    B.RM_NO,
    B.DTTM_ADM
FROM
    PASR_NAMES A,
    PASR_PROFILE B,
    PAST_PATIENT_ADM C,
    HISR_CODES D

WHERE
    A.ID = B.ID
AND
    B.ID = C.ID
AND
    C.ID = D.ID

Is there a way to tell that all of the ID's from the tables are the same? A simpler code than going on like this:

WHERE
        A.ID = B.ID
    AND
        B.ID = C.ID
    AND
        C.ID = D.ID

Or is JOIN - ON the only option for this?

further reading http://stackoverflow.com/q/11179991/1291428 – Sebas Jun 25 '14 at 04:28 — Sebas, Jun 25 '14 at 04:28

score 2 · Answer 1 · edited May 23 '17 at 10:26

You can use NATURAL JOIN as below:

SELECT
    A.ID,
    A.LNAME,
    A.FNAME,
    A.MNAME,
    A.BIRTH_DATE,
    C.RELG_CODE,
    C.NAT_CODE,
    B.PT_STATUS,
    B.RM_NO,
    B.DTTM_ADM
FROM
    PASR_NAMES A
    NATURAL JOIN PASR_PROFILE B
    NATURAL JOIN PAST_PATIENT_ADM C
    NATURAL JOIN HISR_CODES D;

From Oracle Reference, "A natural join is based on all columns in the two tables that have the same name." So, there is a chance that the joins happen based on other columns as well. Therefore, it is recommended that you still use the INNER JOIN syntax and explicitly specify the JOIN columns.

References:

NATURAL JOIN on Oracle® Database SQL Language Reference

Related SO question

score 0 · Answer 2 · answered Jun 25 '14 at 04:36

Use the proper join syntax:

FROM PASR_NAMES A JOIN
     PASR_PROFILE B
     ON A.ID = B.ID JOIN
     PAST_PATIENT_ADM C
     ON B.ID = C.ID JOIn
     HISR_CODES D
     ON C.ID = D.ID

Or:

FROM PASR_NAMES A JOIN
     PASR_PROFILE B
     USING (ID) JOIN
     PAST_PATIENT_ADM C
     USING (ID)
     HISR_CODES D
     USING (ID)

I would discourage you from using the natural join. It might seem like the right thing at first. However, the semantics of the query are highly dependent on the structure of the tables. If columns are renamed, removed, or added, the query might still work but produce highly unexpected results.

Natural Join, I thought of that too when I saw the name. – NoviceProgrammer Jun 25 '14 at 05:58 — NoviceProgrammer, Jun 25 '14 at 05:58

Same ID field on multiple tables

2 Answers2