Hello everyone,

I am looking for an explanation on the behaviour of search conditions
in OUTER JOIN clauses as compared to WHERE clauses.

As far as I am aware, search conditions in INNER JOINs are equivalent
to search conditions in WHERE clauses. For OUTER JOINs this is
apparently not so.

In the employee database, a list of all jobs with or without employees
holding those jobs, is produced as follows:

SELECT j.job_title, j.job_code, j.job_grade, j.job_country,
e.emp_no, e.phone_ext
FROM job j LEFT OUTER JOIN employee e
ON e.job_code = j.job_code
AND e.job_grade = j.job_grade
AND e.job_country = j.job_country ;

An additional WHERE clause reduces this list to only view the vacant
job positions.

WHERE e.emp_no IS NULL;

This is a nice way to avoid an NOT EXISTS subquery. However, it shows
that the search condition applies to an intermediate result table,
viz. the JOIN result. Obviously, it does not match values in the
Employee base table. This shows more clearly when the "e.emp_no IS
NULL" predicate is added to the OUTER JOIN's search condition:

FROM job j LEFT OUTER JOIN employee e
ON e.job_code = j.job_code
AND e.job_grade = j.job_grade
AND e.job_country = j.job_country
AND e.emp_no IS NULL ;

The query result table suggests that in this case e.emp_no values are
read from the base table.

* Is this behaviour documented anywhere?
* Does the SQL standards say anything on this subject?
* How can I best explain this to students in my Ib/Fb courses?
* Is this a feature or a bug?
* Or is it a case for the Firebird documentation project?

Kind regards,
Marco