Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, tarek.

Asked: January 10, 2018 - 8:25 am UTC

Last updated: January 13, 2018 - 4:03 am UTC

Version: oracle 11g

Viewed 1000+ times

You Asked

we face some issue as

select * from t where a is null or b is null or c is null


the question is if the query find the first where (a is null) then retrieve it only and not retrieve other where statment(b is null or c is null)
can you do?

and Chris said...

You have ors between your clauses. So the query will fetch rows where any of those columns are null:

create table t (
  x int, y int, z int
);

insert into t values (null, 1, 1);
insert into t values (2, null, 2);
insert into t values (3, 3, null);

set null <null>
select * from t
where  x is null or
       y is null or
       z is null;

X        Y        Z        
  <null>        1        1 
       2   <null>        2 
       3        3   <null>


If you want to exclude the rows where Y or Z are null, remove those tests from your where clause!

If you want the rows where only X is null and not Y or Z, then check the others are not null:

insert into t values (null, null, 4);
insert into t values (null, 5, null);

select * from t
where  x is null 
and    y is not null
and    z is not null;

X        Y   Z   
  <null>   1   1 

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alternative interpretation

Racer I., January 10, 2018 - 12:17 pm UTC

Hi,

Assuming a more complicated interpretation of the requirement :
X (here 3) filters with different priorities :
3 : a=null
2 : b=null
1 : c=null
return all and only the rows that match the filter with the highest prio among all rows.
i.e. if there are prio 3 rows return all those
if there are some prio 2, but no prio 3, then return all prio 2 rows,
if there are some prio 1, but no prio 2 or 3, then return all prio 1 rows,
otherwise return no rows
WITH
TestData AS (
  select null a, 1 b, 1 c FROM DUAL union all
  select null a, 1 b, 2 c FROM DUAL union all
  select 2 a, null b, 1 c FROM DUAL union all
  select 2 a, null b, 2 c FROM DUAL union all
  select 3 a, 1 b, null c FROM DUAL union all
  select 3 a, 2 b, null c FROM DUAL),
FilterData AS (
  select '3' Prio, a, b, c FROM TestData WHERE a IS NULL union all
  select '2' Prio, a, b, c FROM TestData WHERE b IS NULL union all
  select '1' Prio, a, b, c FROM TestData WHERE c IS NULL),    
PrioData AS (
  select Prio, a, b, c, MAX(Prio) OVER (ORDER BY Prio ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) MaxPrio
  from FilterData)
SELECT *
FROM  PrioData
WHERE Prio = MaxPrio


challenge : optimize the SQL so oracle gets as close to a good (best?) full scan algorithm :
1. Start collecting rows for all filters/prios
2. For the first row for prio Y : discard all collected rows for lower prios and stop filtering/collecting them
3. return what remains after scanning the whole table once


Chris Saxon
January 10, 2018 - 4:16 pm UTC

I'm not convinced that's what the OP is looking for. But the question is rather ambiguous...

better SQL

Racer I., January 10, 2018 - 12:20 pm UTC

Hi,

FilterData AS (
  select CASE WHEN a IS NULL THEN '3'
    WHEN b IS NULL THEN '2'
    WHEN c IS NULL THEN '1'
    ELSE '0' END Prio, a, b, c
  FROM TestData),    


Yet another SQL

Racer I., January 10, 2018 - 12:26 pm UTC

Hi,

This may be slightly better/equal/worse depending on how Oracle does it internally :
PrioData AS (
  select Prio, a, b, c, MAX(Prio) OVER () MaxPrio
  from FilterData)


regards,

more detail from med

tarek taha, January 12, 2018 - 8:24 pm UTC

may be not clear the question from me but the detail is
if we have query includeing meny clause as
where a is null
or b is null
or c is null
i just need one this ..if the query find first statment for exampe(a is null)
then retreive it only and not retreive the records form other statmnet which is (b is null or c is null) ?
is it done?
very thanks for rapid response
Connor McDonald
January 13, 2018 - 4:03 am UTC

For a condition such as:

where [condition1] or [condition2]

you *cannot* rely on the condition1 being evaluated "first". It might be, it might not be.




My understanding of requirement

Ghassan, January 13, 2018 - 8:52 am UTC

Med
Are you wanting this?

Select * from t where a is null
Union
Select * from t where b is null
Union
Select * from t where c is null

So that way, you got not intersection resulting from common met conditions.

If so you got the answer to your requirement.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.