Will this be more efficient? 1

[Truusvlugindewind]

Suppose the functional requitement:
Read a, b, c from tab01.
When b = 2 read k from tab02 as well (using c as foreign key). The tab02 row does not have to exist.

You can code

select         t1.a
     ,         t1.b
     ,         t1.c
     ,coalesce(t2.k,space)
  from tab01 t1 
  left outer 
  join tab02 t2
    on       t2.c = t1.c
where  t1.a = :a

in the host language you code
if b in tab01 = 2 and k in tab02 not = space then display "yes"

But when I look at this I think to myself: "when B is not 2 then I do not have to do a table-looup for tab02. Would it not be better to include this condition in the "ON" clause of my join? So:

select         t1.a
     ,         t1.b
     ,         t1.c
     ,coalesce(t2.k,space)
  from tab01 t1 
  left outer
  join tab02 t2
    on [COLOR=red]t1.b = 2 and[/color]
       t2.c = t1.c
where  t1.a = :a

What would you do:
include the condition in the "ON" clause: probabley more efficient but not so nice readable, or just leave it out and who cares about the superfluous table-lookups?

 

[Mercury2]

I'm pretty sure that it would be better to include this condition in the "ON" clause of the join.

 

[BrianTyler]

This is another variant to be considered

select t1.a
, t1.b
, t1.c
,case
when t1.b = 2 then
(select coalesce(t2.k,' ') from tab02 t2
where t2.c = t1.c)
else ' '
end
from tab01 t1

Regards

Brian

 

[fredericofonseca]

having
select t1.a
, t1.b
, t1.c
,coalesce(t2.k,space)
from tab01 t1
left outer
join tab02 t2
on t2.c = t1.c

where t1.a = :a

or
select t1.a
, t1.b
, t1.c
,coalesce(t2.k,space)
from tab01 t1
left outer
join tab02 t2
on t1.b = 2 and
t2.c = t1.cwhere t1.a = :a

is NOT the same.
example.

assume host variable "a" = 1
tab01
a b c
1 2 1
1 1 3


tab02
1 val1
3 val3

first query will return
a b c k
1 2 1 val1
1 1 3 val3

second query will return
first query will return
a b c k
1 2 1 val1
1 1 3 "space"

So yes you do need to place the validation of tab01.b ='2' on the join clause.


The variant BrianTyler has given will also work if you have V8 or higher.

His option might have a higher CPU cost though. Have a look at the explain plan, making sure you have a statement table created so you can see the statement cost.

Note that if you are dealing with HUGE tables, then BrianTyler option will definitely have lower performance.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Truusvlugindewind]

Thanks for the reactions. Brain, I am not concered about the selected-columns. My concern is confusion. The code is correct and efficient but can be a piftall for someone who "thinks" he/she understands the code.
Normally, when you choose between the comma or the join syntax the conditions in the "ON" and the "WHERE" clause have the save meaning and syntax and can be exchanged. In this case, however, when you move the

t1.b = 2

from the "ON" to the "WHERE" clause, it is a complete different ball-game! Then you select only those rows from tab01.

So, should I code like this risking that maintainance-collegues will mis-interpred this in the future? Or, omit the extra condion in favour of plain-and-simple-SQL and take the extra table-lookup's for granded?

 

[fredericofonseca]

Truusvlugindewind,


Having the clauses on the ON are perfectly readable, a lot more than having them on the where, specially if joining several tables. look at thread178-1430335 for two distinct ways of writing the exact same query.

Also from the point of performance they should be on the ON clause, as they will be evaluated before data is accessed on the joined table, or will even prevent other tables from being accessed on a multi join (2+).

On your specific case, tbl02 would not be accessed on the second case for all records of tbl01 where t1.b = 2, but it would be accessed for any record otherwise.

This may be OK for small tables. but no SQL on my shop goes up to production if it has any predicate missing from the SQL, and then has it on the COBOL program. Furthermore the order of the predicates on the join/where make a difference from a performance point of view, so even those are revised before going to production. I make sure of that.

From the point of view of maintenance, I find it a lot easier if I have everything on the SQL, than having it spread around the program, as then I have to go through all the program to identify what is being done extra. Specially if you need to do a quick SQL without a program to identify the records that are supposed to be processed.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Truusvlugindewind]

Agree, thanks (my program is in production now).

Frederico's stars = Frederico's stars + 1