I am having the following sql

update tableB
set columnb='a'
where columna in(select columna from tableA)

tableA contains 2 Million rows and tableB contains 2000 rows. By executing the sql does the inner query fetches all 2 Million rows? Is there any way I can verify?

Thanks
Shri

Yes it does.

You may want this:

update tableB B
set columnb= 'a'
where columna in
(select columna from tableA A where A. columna = B.columna)

or maybe this:

update tableB
set columnb= 'a'
where columna in (select columna from tableA fetch first 1 rows only)

or maybe this (but it will read all rows in tableA):

update tableB
set columnb= 'a'
where columna in (select distinct(columna) from tableA)

Thanks Marcus

This is logically equivalent to the original query, but will in general be less performant.
(Correct me if I'm wrong.)

If tableA contains an index on columna, most likely the inner query will use an index-only lookup; i.e., the table will not be scanned at all.
You may verify this by using EXPLAIN.

The original query makes little sense without the DISTINCT clause, and even then it may not be what was intended. A DB2 query with a very long "IN" list will not perform well, and in the past there have been limits on how may values can there can be in such a clause.

I would think a good optimizer would be able to find out by itself whether it reorganizes a long "IN" list or not ...

Anybody did some experiments regarding performance of long IN lists?
Any differences between DB2 7 and 8 in this respect?

I would writeWould the performance be better or worse than using the IN clause ?

As compared to the logically equivalentthe "EXISTS" solution requires running the subquery for every of the 1000 rows of the outer tableA, but on the other hand these 1000 subquery runs each return just 1 value (true or false), while the single subquery run in the "IN" solution may return 1000000 values.
Unless the optimizer is clever enough, I would vote for the "EXISTS" query as being the fastest one. (Roughly 1000 times faster?? Anybody did the experiment?)
All will of course also depend on the presence of indices.

The problem is that the second query is not functionally the same as the first. The second query says that any value in the tableA is valid for any row in tableB, whereas the first query correlates the query to the equivlent row in tableA which have the same value for columna (WHERE A.columna = B.columna).

The two queries certainly are equivalent!
The first one will change those rows of TableB for which that row's columna value exists in columna of TableA, while the second one will change those rows of TableB for which that row's columna is in the list of columna values of TableA ...

Isn't that like saying "take two steps forward, one step back" is logically equivalent to walking one step at a time? Afterall, the net gain is one step forward!

In this case, not necessarily:
The "IN" version might by better when the inner table (A) is small and the outer table (B) is large, while the "EXISTS" version will be likely to be more performant in the opposite situation.

So it is more like saying "which is better: two small steps forward, or one big step?" where the answer depends on whether you are climbing a mountain trail or not.

You are probably right that the two are equivilent. It is difficult to understand what the business purpose of the queries are because they use completely fake table names and columns. It would be easier to understand if the DB2 Sample database was used to make up such queries.