Does the ANSI SQL Standard allow filters or parameters in JOIN conditions? 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;
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.