Re: reasonable SQL syntax - Join

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

Jeff

I prefer the first one, because it makes it clear that you are JOINing 
files. I have a feeling that the optimizer changes the second one to a 
real JOIN, but I'm not sure. It also helps make things clearer as to 
what indexes you want - JOIN fields should precede WHERE fields, as I 
recall - or vice versa.

I don't know that either performs any better - maybe there is optimizer 
optimization with the true JOIN, as it does not have to convert it.

Lots of speculation this early in the morning!

Vern

Jeff Crosby wrote:
> I think the following 2 SQL statements should give me the same results.  Is
> there a preference on which 'style' to use?  Or a performance difference?
>
> This is a once-a-month task, so performance isn't critical.  Readability is
> more my concern and both are very readable IMO.
>
>
>         Exec SQL
>           Select :CurYYYYMM,
>                  Coalesce(Sum(s.VCSALS), 0),
>                  Coalesce(Sum(s.VCCGSL), 0),
>                  Coalesce(Sum(s.VCGMGN), 0),
>                  0,
>                  Coalesce(Sum(s.VCCASH), 0),
>                  Coalesce(Sum(s.VCUNSH), 0),
>                  Coalesce(Sum(s.VCCDAM), 0),
>                  Coalesce(Sum(s.VCNMGN), 0),
>                  0
>             Into :CYT
>             From SSVNDCHS s Join DMCUSMST c On s.VCCSNR = c.CUSNR
>             Where s.VCYYMM = :CurYYYYMM and
>                   c.SLSNR = :srslnr;
>
>
>         Exec SQL
>           Select :CurYYYYMM,
>                  Coalesce(Sum(s.VCSALS), 0),
>                  Coalesce(Sum(s.VCCGSL), 0),
>                  Coalesce(Sum(s.VCGMGN), 0),
>                  0,
>                  Coalesce(Sum(s.VCCASH), 0),
>                  Coalesce(Sum(s.VCUNSH), 0),
>                  Coalesce(Sum(s.VCCDAM), 0),
>                  Coalesce(Sum(s.VCNMGN), 0),
>                  0
>             Into :CYT
>             From SSVNDCHS s,
>                  DMCUSMST c
>             Where s.VCYYMM = :CurYYYYMM and
>                   s.VCCSNR = c.CUSNR and
>                   c.SLSNR = :srslnr;
>
>
>