CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Rasmi
Rasmi

Reputation: 509

Selecting Rows with one column or Columns with NULL value when even one have value

I have a table where i wanted to select a row even if any of the column 1 thought to column 4 have a NOT NULL value and the the other have NULL value in ORACLE

If my data is like below

          col1    col2    col3    col4
row1      1       null    null    null
row2      null    2       null    null
row3      null    null    3       null
row4      null    null    null    level1
row5      11      22      33      level2
row6      11      null    33      level2
row7      null    null    null    null

I am expecting the output as below

          col1    col2    col3    col4
row1      1       null    null    null
row2      null    2       null    null
row3      null    null    3       null
row4      null    null    null    level1
row6      11      null    33      level2

I have a very huge table and i don't have index on any of these column and bit worried to use a query something like below.

select * from table_name 
   where col1 <> null and col2 = null and col3 = null and col4 = null
   or col1 = null and col2 <> null and col3 = null and col4 = null
    ...

Is there any better way to write this in ORACLE sql.?

Upvotes: 0

Views: 4988

Answers (3)

MT0
MT0

Reputation: 167972

You can use:

SELECT *
FROM   table_name
WHERE  CASE WHEN col1 IS NULL THEN 1 ELSE 0 END
       + CASE WHEN col2 IS NULL THEN 1 ELSE 0 END
       + CASE WHEN col3 IS NULL THEN 1 ELSE 0 END
       + CASE WHEN col4 IS NULL THEN 1 ELSE 0 END
       > 0;

or, just:

SELECT *
FROM   table_name
WHERE  ( col1 IS NULL OR col2 IS NULL OR col3 IS NULL OR col4 IS NULL );

Or if you want to exclude rows that are entirely NULLs then:

SELECT *
FROM   table_name
WHERE  CASE WHEN col1 IS NULL THEN 1 ELSE 0 END
       + CASE WHEN col2 IS NULL THEN 1 ELSE 0 END
       + CASE WHEN col3 IS NULL THEN 1 ELSE 0 END
       + CASE WHEN col4 IS NULL THEN 1 ELSE 0 END
       BETWEEN 1 AND 3;

or

SELECT *
FROM   table_name
WHERE  ( col1 IS NULL OR col2 IS NULL OR col3 IS NULL OR col4 IS NULL )
AND    NOT ( col1 IS NULL AND col2 IS NULL AND col3 IS NULL AND col4 IS NULL );

If you create an index on the four columns:

CREATE INDEX table_name__idx ON table_name ( col1, col2, col3, col4 );

Then the bottom query can be performed as an INDEX FULL SCAN as shown by the explain plan:

| PLAN_TABLE_OUTPUT                                                                          |
| :----------------------------------------------------------------------------------------- |
| Plan hash value: 1211442363                                                                |
|                                                                                            |
| ------------------------------------------------------------------------------------       |
| | Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |       |
| ------------------------------------------------------------------------------------       |
| |   0 | SELECT STATEMENT |                 |     6 |    60 |     1   (0)| 00:00:01 |       |
| |*  1 |  INDEX FULL SCAN | TABLE_NAME__IDX |     6 |    60 |     1   (0)| 00:00:01 |       |
| ------------------------------------------------------------------------------------       |

db<>fiddle here

Using functions (such as CASE or GREATEST) would prevent Oracle from using an index.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

You could use either the GREATEST or LEAST function here:

SELECT *
FROM yourTable
WHERE GREATEST(col1, col2, col3, col4) IS NULL;

The above call to GREATEST would only be NULL if at least one of the four columns have a NULL value.

If you want to also ensure that a given row has at least one non NULL value, then add a condition for that to the WHERE clause:

SELECT *
FROM yourTable
WHERE
    GREATEST(col1, col2, col3, col4) IS NULL AND
    COALESCE(col1, col2, col3, col4) IS NOT NULL;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269763

I think you want:

select t.*
from t
where col1 is null or col2 is null or col3 is null or col4 is null;

Note that = null and <> null make no sense in SQL. Almost any comparison to null returns null -- which is treated as "false" in where clauses and case expressions. That is why SQL provides the special operators is null and is not null.

Upvotes: 0

Related Questions