getting one row of nulls when there are no rows: outer join using dual

This may be an old trick I've just discovered, but I've not found it in a search of Google groups:

The desire is to obtain all the rows (in my app, just one) when there are any, nulls for the requested columns when there are none. Yes, a group function such as MAX will return nulls when none, but (1) that can be slow when there are many columns being selected, since you must put a MAX on each, and (2) it gives you the max of each column, each from a different row, when there are multiple rows.

I just "discovered" the following:

Table t (idcol number, datacol1 varchar(10), datacol2 . . . etc)

The desired row, if present, has the idcol=21 (of course, in a PL procedure or a program, that will be a variable).

Try

select idcol, datacol1, datacol2, . . . from t, dual where idcol(+)=decode(dummy,'X',21,21);

The index on idcol is used, so the query is as fast as it can get. The repeat of the desired value as the default in the decode isn't needed right now, but if Oracle ever decides the value of "dummy" isn't important and changes it . . .

And if this is an old trick, point me at the source, so I can avoid re-inventing other old tricks. Received on Wed Feb 13 2002 - 20:03:36 CST