How to omit columns for specific rows in SQL?-CodePudding

As an example:

select 
    pre, sze, fdm_pre, val
from 
    form_data_stage
where 
    fdm_pre in (1,2,3,4) 
order by 
    pre;

this will return values for all the columns pre, sze, fdm_pre, val for any of the fdm_pre values listed, i.e. (1,2,3,4). However, I only care about the pre and size values when fdm_pre is 1.

I could write a query such as

select 
    case when fdm_pre = 1 then pre else null end as pre, 
    case when fdm_pre = 1 then sze else null end as sze,
    fdm_pre,
    val
from 
    form_data_stage 
where 
    fdm_pre in (1,2,3,4) 
order by 
    pre;

But, is there some standard way of dealing with this situation? Is it generally more efficient to return all the columns, even if they aren't used? Or, would it be better to do some conditional checking as in the second query? The pre and sze columns are integer values.

CodePudding user response：

It's not efficient to return all the columns when they are not used, specially if the unused columns are massive (BLOB, CLOB, TEXT, ARRAY, etc.).

In your particular example the columns "not returned" are small ones (in bytes), so it won't really matter if you produce nulls instead.

CodePudding user response：

I think keeping the query simple and clear to read is important. I would suggest you return all the relevant columns (that might be useful) and on the app logic side deal with these 2 options of column usage.

CodePudding user response：

You can use execute string instead of this kind of script. In that way your desire columns should put in string and with if statement you can decide about which column is shown and which one is not necessary. I can make a sample for you if you need.