union versus or

Question

let's say I've got two queries:

select top 20 idField, field1, field2 from table1
where idField in
    (
    select idField from table1 where field3 like '...'
    union
    select idField from table2 where field4 like '...'
    )
order by sortfield1

select top 20 idField, field1, field2 from table1
where field3 like '...' 
or idfield in 
(select idField from table2 where field4 like '...')
order by sortfield1

let's say that both tables are rather large (table2 larger than table1), average range of results number is 0-400, but I always want max 20 results.

Is it possible to say with big probability which statement would be more performant and/or what would the choice of statement depend on? Thank you

Answer 1

It most probably depends on the dbms you are using. I had bad experiences with union using Oracle (8), it was very slow, probably because it had to put the results of two queries into a list in memory.

I'm not sure if these two queries have the same result. The second query only returns values from table1, while the first also returns values from table2.

Answer 2

Check the explain plan to see what the efficiency is like. It would also give you insight to indexes, if they don't currently exist.

Answer 3

Just by looking at your answer, I would guess that the second query would be faster although without knowing more about the schema, indexes, and data it can only be a guess. Since you are using LIKE, the search string can also make a big difference, especially if you are using a leading wildcard.

Using a LEFT OUTER JOIN or EXISTS will probably be faster than either of the solutions in the question that use "IN". But again I couldn't say for certain without more information.

Answer 4

In the perfect world this should not make a difference, but I would still try out this approach too. Could be faster:

select top 20 idField, field1, field2 from table1
where idField in
    (
    select top 20 idField from table1 where field3 like '...'
    union
    select top 20 idField from table2 where field4 like '...'
    )