Which of these two statements are more efficient

select col1 from table where col2 in (1, 2)

or

select col1 from table where col2 = 1
union all
select col1 from table where col2 = 2;

And lets say for the sake of argument that there are appropriate indexes on this table.

Which is better and why?

Thanks

I'd say the first select with IN is more efficient becouse DB2 does not have to parse second query (it would not take long but still) like with UNION clause.

Basically: with first database runs only one query but with second database runs two queries. Running a query costs time.

Optimizer might notice this and change second query into more efficient version though.

Vesku

As ever, results are dependent on the particular data.
However, I just tried this on a reasonably large analyzed table (around 80,000 rows in 2000 blocks), and the IN version was significantly better (noticeably faster). The autotrace outputs were:

1) For IN:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=2 Bytes=14)
1 0 INDEX (FAST FULL SCAN) OF 'BTR_PK' (UNIQUE) (Cost=50 Card=
2 Bytes=14)

Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
325 consistent gets
0 physical reads
0 redo size
339 bytes sent via SQL*Net to client
434 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed

2) For UNION ALL:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=100 Card=2 Bytes=14)
1 0 UNION-ALL
2 1 INDEX (FAST FULL SCAN) OF 'BTR_PK' (UNIQUE) (Cost=50 Car
d=1 Bytes=7)

3 1 INDEX (FAST FULL SCAN) OF 'BTR_PK' (UNIQUE) (Cost=50 Car
d=1 Bytes=7)

Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
650 consistent gets
0 physical reads
0 redo size
339 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed

The UNION ALL does twice the work of the IN. The optimizer doesn't manage to combine the separate UNION ALL queries into a single INDEX (FAST FULL SCAN), it does it twice.

On a small table, there might be no difference.