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