or clauses versus unions in joins

Adaptive Server cannot optimize join clauses that are linked with or and it may perform Cartesian products to process the query.

NoteAdaptive Server optimizes search arguments that are linked with or. This description applies only to join clauses.

For example, when Adaptive Server processes this query, it must look at every row in one of the tables for each row in the other table:

select * 
    from tab1, tab2
    where tab1.a = tab2.b
        or tab1.x = tab2.y

If you use union, each side of the union is optimized separately:

    select * 
        from tab1, tab2
        where tab1.a = tab2.b
union all
    select * 
        from tab1, tab2
        where tab1.x = tab2.y

You can use union instead of union all to eliminate duplicates, but this eliminates all duplicates. You may not get exactly the same set of duplicates from the rewritten query.

Adaptive Server can optimize selects with joins that are linked with union. The result of or is somewhat like the result of union, except for the treatment of duplicate rows and empty tables: