TheBakker TheBakker - 5 months ago 7
SQL Question

How to check if all rows validate a predicate

I've a table in my database for which I need to check if all rows have one field not null.

If there are no row or if there is at least 1 row with the field null => true

If there are rows and they are all with the field not null => False

Is there a way to do this in on simple query? Or I need to check if my table is empty first then if it's not check if I've a row with the field value empty ?

Answer

Use count(*) and count(field) and compare the two:

select 
  case when count(*) > 0 and count(*) = count(field) then 1 -- not empty and no nulls
  else 0 end as isgood
from mytable;

Oracle SQL has no boolean data type , so I use 1 for true and 0 for false. You can replace this with whatever you like (e.g. 'true' instead of 1 and 'false' instead of 0).

As to turning this into a predicate (correlated to a main query), you'd use something along the lines of:

select ...
from main
where exists
(
  select 1
  from mytable
  where mytable.colx = main.coly
  having count(*) > 0 and count(*) = count(field)
);
Comments