Trying to create cleaner sql syntax

Question

Here is what I am trying to do:

select * from table
where in ('completedDate', 'completedBy', 'cancelDate', 'cancelBy') is not null

If the four columns above are not null I need to display the records. I know I would do this with several where/and clauses but trying to learn and make my new stuff cleaner.

Is what I am trying to do above possible in a cleaner way?

Answer 1

If I understand correctly I guess you want to do that:

select * 
from table
where completedDate is not null
  and completedBy is not null
  and cancelDate is not null 
  and cancelBy is not null

Regarding clarity of code I don't see a better way to write it, that's what I would code anyway.

EDIT: I wouldn't really do that in this case, but if this is a very common condition you can add a computed column in the table (stored or not), or create a view on top of table, and do:

select * from view where importantFieldsAreNotNull = 1

Answer 2

If I understand correctly, you want to return records where all four columns are not null?

The standard and (in my opinion) most readable way to do this would be:

Select
    *
From
    YourTable
Where
    Column1 IS NOT NULL AND
    Column2 IS NOT NULL AND
    Column3 IS NOT NULL AND
    Column4 IS NOT NULL;