Hey guys,

This may be a stupid question, but I can't figure out what the SELECT 1 is actually used for. For example, the query statement shows it here:


AND NOT EXISTS

(SELECT 1 from MBR_HIS where HIST_CD = '10')


I tried running the statement by itself and the display result is a bunch of 1's. Why not use SELECT * instead?

What difference does it make? The subselect doesn't return anything and is only used for an existence check. Using "SELECT 1" is the typical convention for that. You can also use "SELECT *" (but that is usually frowned upon because you should always specify exactly what you want). DB2 will optimize the "*" away because executing it would be more expensive than just returning the constant 1.

It is a bad idea to use "select *" in any application program, even if the DB2 optimizer converts it to "select 1" or something similar.

In the old days, the DB2 optimizer did not convert "select *" to "select 1" in a "where exists" or similar query, so you ended up having DB2 actually process all those columns in the sub-query, which was not very efficient. In fact, there were serious debates at the time about whether "select '1' " was more efficient than "select 1" or "select <primary-key-column>". That was awhile ago.

instead of SELECT 1, i will often use either SELECT 937 or SELECT NULL

Dogs and consultants like to leave to leave their mark so everyone knows they were there.

not everyone -- just those who, in the immortal words of mike wilbon, can "slurp the love"