In one of my previous tip I have shortly mentioned a use of EXISTS in SQL statements. You can use EXISTS for many different purposes, but one thing is certain: EXISTS cannot return any values, so why bother about the select list. In my previous tip I write SELECT 0 FROM in the EXISTS, but why do I use this meaningless select list?

Since DB2 was born many of us has learned that we should avoid using SELECT * FROM in our static SQL. This is a very good advise for reasons I will not tell you about here. But does it make any difference in the SELECT that is supposed to check the existance of a row in an EXISTS? For some years ago I decided to investigate the matter (I do not remember which DB2 version it was) and found out that it made a difference to use SELECT 0 instead of SELECT *.

The idea behind using SELECT 0 is that the EXISTS SELECT only has to return a constant value that will be immediately discarded anyway. The difference between the two statement types became apparent when I checked the access path for an EXISTS SELECT where the WHERE clause only reffered to columns in an index. With SELECT 0 I got index only access while SELECT * also accessed the data part and that is definitely waste of time. After this experience I started to use SELECT 0.

Sometimes I talk to other dedicated DB2 people and then we start to discuss the optimizer. I was once told that SELECT * and SELECT 0 results in the same access path in the recent versions of DB2 and that the access path is the most reasonable (INDEX ONLY if possible). Whether it was some IBM person or a more unbiased individual I do not remember. So I started to use SELECT * again. Then after a year or two I discovered that DB2 may use data access for EXISTS SELECT * again even though it may be redundant. Maybe the error was never removed. I do not know because I never bothered to check the validity of the statement from the colleague.

I will advise you to always use SELECT 0 in EXISTS because it eliminates the risk of unwanted data access. As you might have discovered the optimizer works in mysterious ways so in some versions of DB2 it might treat SELECT * in EXISTS differently that in other versions. Therefore SELECT 0 is the best choice because the optimizer must treat it as returning a constant and therefore the select list cannot result in data access.