Posted by Ed Murphy on 05/03/07 22:33

Jane T wrote:

> I have an 'extract' table which has 1853 rows when I ask for all rows where
> period_ = 3. The allocation table for info has 210 rows.
>
> I have two scripts below. The first script where I specify a period on a
> join, brings back 1853 lines and works. The second script where I specify
> the period in the where clause only brings back 1844 rows. I have located
> the missing 9 rows and they don't look any different to the other 1844 rows.
>
> Can someone educate me as to the difference between specifying a condition
> on a join and a condition in a where clause.

For equal joins, there's no difference. For left outer joins,
specifying a condition on the join simply causes it to return
rows with data from the left side and NULLs from the right side,
whereas specifying a condition on the WHERE causes it to filter
the output of the JOIN.

Basically, the query is processed in this order:

* FROM/JOIN
* SELECT
* WHERE
* GROUP BY
* HAVING
* ORDER BY

[Back to original message]