Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> ANSI Join improvement

ANSI Join improvement

From: <kvnkrkptrck_at_gmail.com>
Date: 21 Jun 2006 12:33:44 -0700
Message-ID: <1150918423.942687.159750@p79g2000cwp.googlegroups.com>


At the risk of being labeled a crank...

I'm looking for someone to tell me whether my idea for improving the ANSI Join syntax has any merit. One thing about the current syntax that often strikes me is I usually join tables on identically named columns, and NATURAL joins are perfect for this situation:

EMP = {Dept_id, Emp_id, FName, LName, Salary} DEPT = {Dept_id, Name}

SELECT Dept_id "Department ID", Name "Department Name", SUM(Salary)
"Total Salary"

FROM DEPT NATURAL INNER JOIN EMP
GROUP BY Dept_id, Name;

I don't need to "pick one" on the projected or grouped Dept_id, and I don't need to write out the redundant EMP.Dept_id = DEPT.Dept_id condition.

However, I almost always run into the following problem, where the relations are defined as follows:

EMP = {Dept_id, Emp_id, FName, LName, Salary, Last_modified_date} DEPT = {Dept_id, Name, Last_modified_date}

I can no longer use the NATURAL Join syntax because of namespace conflicts on unrelated attributes (here, Last_modified_date).

Thus, I must write the following:

SELECT DEPT.Dept_id "Department ID", Name "Department Name", SUM(Salary) "Total Salary"
FROM DEPT NATURAL INNER JOIN EMP
ON EMP.Dept_id = DEPT.Dept_id
GROUP BY DEPT.Dept_id, Name;

I would love to see a middle ground:
SELECT Dept_id "Department ID", Name "Department Name", SUM(Salary)
"Total Salary"

FROM DEPT NATURAL INNER JOIN EMP ON Dept_id GROUP BY Dept_id, Name;

Or, alternatively, a KEY JOIN, which always joins 2 tables based on their foreign key relationship (if one exists).

Any thoughts on this? I have the ANSI committee on hold right now, and I don't know if they will put up with the elevator music for much longer.... Received on Wed Jun 21 2006 - 14:33:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US