RE: SQL syntax

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> From: Goodbar, Loyd
>
> I'm no SQL guru, but at first blush, the first statement looks more
> efficient since it is more like to allow the optimizer to use
> access paths.
>
> It would use an access path to find WRITE and READ records, another access
> path to find SCREEN-REC and SCREEN-FILE records, and use regular set
> processing on the subsets to arrive at your result.
>
> The second statement looks like the optimizer would need to join
> everything
> together THEN figure out is WRITE/SCREEN-REC/join true or
> WRITE/SCREEN-FILE/join true? It might be trying a cartesian join.

That doesn't explain why adding the logical by C1SOURCE fixed the second
join.  And like I said, the optimizer indicated the same access paths for
both syntaxes.

I guess, though, my problem with SQL is the phrase "looks like".  With
native DB2 access I know what my program is doing.  With SQL, black magic
starts to occur.

>
> These statements don't really do the same thing. For instance, is it
> possible to have a READ and SCREEN-REC record, or a WRITE and SCREEN-FILE
> records? It is possible you could see these in the first statement. The
> second statement is much more explicit in stating what the join conditions
> are; there will never be a WRITE and SCREEN-FILE record, for instance.

Correct, as I pointed out.