Re: ANSI Join improvement

kvnkrkptrck_at_gmail.com wrote:

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

You almost got it...
It's already there since SQL92, the syntax is sligthly different :-)

<named columns join> ::=
USING <left paren> <join column list> <right paren>

SELECT Dept_id "Department ID", Name "Department Name", SUM(Salary) "Total Salary"
FROM DEPT JOIN EMP USING (Dept_id)
GROUP BY Dept_id, Name;

Some DBMSes support it, e.g. Oracle, PostgreSQL, mysql

Dieter Received on Wed Jun 21 2006 - 22:11:22 CEST