Re: Outer Join not working

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 02/25/05 

Date: Fri, 25 Feb 2005 13:33:07 +0100

On Thu, 24 Feb 2005 23:49:52 -0800, tshad wrote:

(snip)
>I am still not sure why I had to move the JobID test into the "on" clause,
>but it worked. I will have to study it more to understand it.

Hi Tom,

That's because of the order in which a SELECT statement is evaluated.
The order of evaluation whould, in theory (most DBMS's will reshuffle to
optimize, as long as it doesn't change the end result), be as follows:

1. Evaluate FROM clause. Make a temporary result set that holds all
columns from all tables in the FROM clause, with all the rows that
satisfy the ON conditions.
Rows that don't match the ON condition are not included if it's an INNER
JOIN; for OUTER JOINS, the row from the outer table will be retained
with NULL values substituted for the columns from the inner table.

2. Evaluate WHERE clause. Check each row in the temporary result set
from the previous step against the WHERE condition. The row is retained
only if the WHERE condition evaluates to TRUE. If it evaluates to FALSE
or UNKNOWN, the row is removed from the result set.

3. After this, the GROUP BY, HAVING, SELECT and ORDER BY are processed
(in that order) - but these are not relevant for your question.

In your original question, you had a WHERE clause
        where JobID = 3720
where the JobID column is from the inner table of an outer join. For all
rows retained because of the outer join, JobID is NULL. By definition,
the expression NULL = 3270 evaluates to FALSE. Therefor, all rows
retained by the outer join are subsequently removed by the where clause,
effectively changing your query to an inner join.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: CROSS JOIN
    ... > was that depreciating the original FROM .. ... WHERE syntax would never ... > The problem is that the WHERE clause is done after the FROM clause. ... but thats just outer join stuff. ...
    (comp.databases)
  • Re: Performance degradation with view and "left outer join" vs. "from x, outer y"
    ... Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. ... That means that your filter, c.custid = "AB1234", is applied after all rows of the customer table have been LEFT OUTER JOINed to the my_view VIEW with the results stored into a temp table. ... Now if I use the informix extended outer join syntax: ...
    (comp.databases.informix)
  • [Info-ingres] RES: [Info-ingres] Wrong results returned from outer join
    ... Wrong results returned from outer join ... I get the expected zero rows when using the following versions ... " Outer joins specified in the from clause are not the same as joins ... the from clause specifies ...
    (comp.databases.ingres)
  • Re: Better "Join" vs "Where" clause?
    ... running the SQL directly, in the SQL Server tools, rather than ... WHERE clause has been deprecated, ... AFAIK one cannot perform an outer join in Access without using the explicit ...
    (microsoft.public.access.queries)
  • Re: exclude lines after outer join, like left joins in 7.24
    ... an EXISTS clause for the NOT EXISTS clause if you don't need any ... OUTER join using ANSI syntax and join in the ON clause and filter out ...
    (comp.databases.informix)