Re: Idiomatic SQL empty set?

On 8/4/2017 2:45 PM, John Yeung wrote:

Is there an idiomatic, or at least common, expression that returns the
empty set in DB2 for i? (Or is that Db2 for i now?)

The empty set is any set containing zero rows.

For example, maybe something like
select * from sysibm.sysdummy1 where 1 > 2

That's a Db2 idiom for 'zero rows' although WHERE 1=0 tends to be the
examples IBM use.

In particular, I'm interested in an expression that can be used with IN, as in
select myfield1 from mylib.myfile where myfield2 in (emptyset)

The first instinct is to go for NULL, but NULL is not a value. NULL is
an attribute. To set that in place (hee hee) I mentally re-wrote
queries like the example in a way that used attributes:

WHERE NAME = CHAR(30)
WHERE NAME = CCSID(1252)
WHERE NAME = NULL

Db2 syntax forbids WHERE...IN (NULL) in the same way it forbids IN
(CCSID(37)) - it makes no sense (unless they decide to overload NULL
some day!)

So in the end, we can't see if a result set is empty by comparing some
column to NULL, we really need to return an empty result set - zero rows
- when we want the empty set.

I was really hoping that it would simply be (), but that doesn't work.

Yeah, that would be nice; perhaps they never thought of it? I might
consider creating a table named EMPTYSET with no rows, then the
resultant SQL might be clearer to those who follow me:
select myfield1 from mylib.myfile
where myfield2 in (select * from emptyset)