Mysql – Does the ANSI SQL Standard allow filters or parameters in JOIN conditions

join;MySQLsql-standardsyntax

Does the ANSI Standard find the following SQL sinppet syntactically correct? I am interested specifically in the last line:

SELECT name, dept_name
FROM employee JOIN department
     ON employee.dept_id = department.dept_id
     JOIN payroll ON payroll_type = 1;

Best Answer

Yes, it is syntactically correct.

The ON clause can be any boolean expression - i.e. as long as it results in a boolean value (TRUE, FALSE, UNKNOWN).

Note that you have 3 tables that are visible in that ON clause: employee, department and payroll. You'll get an error if payroll_type is a column in more than one of them.

Related Solutions

Peculiar Oracle outer join syntax case

I tried to perform the mechanical process. I hope I remember it right.

This leads to:

SELECT ...
FROM A
         join B on A.A_ID = B.A_ID
    left join C on B.B_ID = C.A_ID and B.B_KEY = C.B_KEY and 'CONSTANT' = C.X_ID
    left join D on C.C_ID = D.C_ID
    left join E on B.A_ID = E.A_ID and B.B_KEY = E.B_KEY

In short I think Leigh Riffel's answer is correct.

Note

in old days the rule to memorize was: oracle where A.a = B.b (+) becomes A.a *= B.b in SQL-Server old syntax the plus goes to the opposite side and becomes a star, which means A left join B on A.a = B.b

SQL Standard – Why ANSI SQL Defines SUM(no rows) as NULL

I'm afraid that the reason is simply that the rules were set in an adhoc fashion (like quite many other "features" of the ISO SQL standard) at a time when SQL aggregations and their connection with mathematics were less understood than they are now (*).

It's just one of the extremely many inconsistencies in the SQL language. They make the language harder to teach, harder to learn, harder to understand, harder to use, harder to whatever you want, but that's just the way things are. The rules cannot be changed "cold" and "just like that", for obvious reasons of backward compatibility (If the ISO committee publishes a final version of the standard, and vendors then set out to implement that standard, then those vendors will not appreciate it very much if in a subsequent version, the rules are changed such that existing (compliant) implementations of the former version of the standard "automatically fail to comply" the new version ...)

(*) It is now better understood that aggregations over an empty set behave more consistently if they systematically return the identity value (= what you call the 'neutral element') of the underlying binary operator at hand. That underlying binary operator for COUNT and SUM is addition, and its identity value is zero. For MIN and MAX, that identity value is the highest and lowest value of the type at hand, respectively, if the concerned types are finite. Cases like averaging, harmonic means, medians, etc. are extremely intricate and exotic in this respect, though.

Best Answer

Related Solutions

Peculiar Oracle outer join syntax case

SQL Standard – Why ANSI SQL Defines SUM(no rows) as NULL

Related Question