Re: ANSI Join improvement

kvnkrkptrck_at_gmail.com wrote:
> 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....

oops... please excuse the typos. The following section:
> 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;
>

should look like this:

Thus, I must write the following:

SELECT DEPT.Dept_id "Department ID", Name "Department Name", SUM(Salary) "Total Salary"
FROM DEPT 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 JOIN EMP ON Dept_id
GROUP BY Dept_id, Name; Received on Wed Jun 21 2006 - 21:47:33 CEST