SQL style

[Truusvlugindewind]

Given a hypotetical query

select a.A1
     , a.A2
     , b.B1
     , c.C9
     , d.D8
 
from tabA a

join tabB b on b.B3 = a.A3           

join tabC c on c.C2 = b.B2
           and c.C4 in ( 1 , 6 , 8 , 9 )
           and c.C8 = :hostC8
 
join tabD d on d.D1 = c.C1
           and d.D5 < CURRENT DATE

where a.A5 = :hostA5
  and a.A6 in ( 'a' , ' ')

or you could code the same query

select a.A1
     , a.A2
     , b.B1
     , c.C9
     , d.D8 

from tabA a

join tabB b on b.B3 = a.A3
           
join tabC c on c.C2 = b.B2
 
join tabD d on d.D1 = c.C1

where a.A5 = :hostA5
  and a.A6 in ( 'a' , ' ')
  and c.C4 in ( 1 , 6 , 8 , 9 )
  and c.C8 = :hostC8
  and d.D5 < CURRENT DATE

Notice the diff.? In the 1st example I code in the "JOIN ON" clause all the conditions to join the table in the select.
In the second I only code in the "JOIN ON" all the referential contraints on which the table is joined.
In the "WHERE" clause I code all the "external" conditions which must be met to include the rows.

DB2 should not give a damn. It will generate a perfect access plan for both situations. Fine.

But we humble humans? What is better/easier to write/maintain? I have not figured it out for myself yet. Please share your opinion.

 

[fredericofonseca]

Personally I use the first one. (and I align the "ON" below the join, not in front).

Also note that having the clauses on the WHERE is not the same as having on the ON, specially if joins other than "INNER" are used.

There was a thread with very clear examples about that situation on the forums. don't remember which one, and it was sometime ago.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[MarcLodge]

Hi All,
My own personal preference is to only use JOIN where necessary i.e. if it's a left outer, right inner etc. In the example given, I would not code a JOIN, but would code:

select a.A1
     , a.A2
     , b.B1
     , c.C9
     , d.D8
 
from tabA a
    ,tabB b
    ,tabC c
    ,tabD d

where a.A5 = :hostA5
  and a.A6 in ( 'a' , ' ') 
  
  and b.B3 = a.A3           
  
  and c.C2 = b.B2
  and c.C4 in ( 1 , 6 , 8 , 9 )
  and c.C8 = :hostC8
 
  and d.D1 = c.C1
  and d.D5 < CURRENT DATE

This is down to personal preference as I think there is no difference in this case between the two and I still consider JOINs to be 'new'!

If an INNER/OUTER JOIN was coded, I would probably code the first one, as I like to see all the clauses kept together as I find that way eaiser to understand and maintain.

Marc

 

[Truusvlugindewind]

Hi Marc

Yes, your version is still propagaged and used a lot. But the "where clause" is always a mixture between
- this is how I tie my tables together
- these are my search conditions

Another alternative could be a CTE but I do not know how that impacts performance

with x as 
( 
  select a.A1
       , a.A2
       , b.B1
       , c.C9
       , d.D8 
  
   from tabA a
   join tabB b on b.B3 = a.A3
   join tabC c on c.C2 = b.B2
   join tabD d on d.D1 = c.C1
)
select * from x 
where a.A5 = :hostA5
  and a.A6 in ( 'a' , ' ')
  and c.C4 in ( 1 , 6 , 8 , 9 )
  and c.C8 = :hostC8
  and d.D5 < CURRENT DATE

[lazy]
I know:
- not enough columns defined in the CTE itself
- do not 'select *' but only the columns required
[/lazy]

We've just finishid the installation of V8 so we can code CTE's now. Now determine "whether/where/when/how" to use them....
I've proposed Linus' slogan "just for fun" but that was not accepted. Can you believe it?

 

[Truusvlugindewind]

** Darn, you cannot edit you posts in this forum **
Please concider above SQL as pseudo-code. Too many syntax errors. Sorry.

 

[blom0344]

Limiting expressions belong in the where clause , not mixed with joins. JOIN is ISO/ANSI standard and much easier to maintain in a mixed environment. I wouldn't consider it 'new'.
(in any case, in Oracle it is possible to use NATURAL JOIN which is rather nice when joins exists between meaningless keys that always match in name)

Ties Blom

 

[MarcLodge]

I think it depends, to a certain extent, how long you've been working with SQL. When I first learnt DB2 there was no JOIN statement and we were taught to group WHERE clauses together for readability. I still hold to that way of coding, so tend to code JOINs and WHEREs together so that you can simply see what and where and how.

That said, I can fully understand Ties distinction between clauses which limit and those which link. Both ways are equally good in my mind, but others may have different opinions.

 

[sathyarams]

Grahame Birchall's SQL Cookbook has a very good explanation on the differences between having predicates in the ON part and the WHERE part. Of course, this matters only for outer joins.



For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp