ANSI Join improvement
Date: 21 Jun 2006 12:33:44 -0700
Message-ID: <1150918423.942687.159750_at_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"
I would love to see a middle ground:
FROM DEPT NATURAL INNER JOIN EMP
ON EMP.Dept_id = DEPT.Dept_id
GROUP BY DEPT.Dept_id, Name;
"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 - 21:33:44 CEST