Avatar of lisha
lisha
 asked on

find null and all the null column in one select query

Hi ,
I have a table table A having column col1,col2,col3,col4

I want to select the data in the table which has null values but with a statement which shows that what are the column having null values as below example:

INPUT:

col1 col2 col3
A               B
         X      B
A              

OUTPUT:

col1 col2 col3  statement
A               B      NULL IS COL2
         X               NULL IS COL1,COL3
A                        NULL IS COL2,COL3
DatabasesOracle Database* InformixSQL

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
noci

So you have to write out some queries....
(for 3 binary options: 8 queries:  2 ^ 3)

select * from A where NOT col1 IS NULL  and NOT col2  IS NULL and NOT col3  IS NULL;
select * from A where          col1 IS NULL  and NOT col2 IS NULL and NOT col3 IS NULL ;
select * from A where NOT col1 IS NULL  and          col2  IS NULL and NOT col3  IS NULL;
select * from A where          col1 IS NULL  and          col2 IS NULL and NOT col3 IS NULL ;
select * from A where NOT col1 IS NULL  and NOT col2  IS NULL and         col3  IS NULL;
select * from A where          col1 IS NULL  and NOT col2 IS NULL and          col3 IS NULL ;
select * from A where NOT col1 IS NULL  and          col2  IS NULL and        col3  IS NULL;
select * from A where          col1 IS NULL  and          col2 IS NULL and         col3 IS NULL ;

each of these queries will show their specific values.
Scott Pletcher

SELECT *,
      STUFF('NULL IS' +
            CASE WHEN col1 IS NULL THEN ',COL1' ELSE '' END +
            CASE WHEN col2 IS NULL THEN ',COL2' ELSE '' END +
            CASE WHEN col3 IS NULL THEN ',COL3' ELSE '' END, 8, 1, ' ') AS statement
FROM /*replace (...) with your table name*/ (
    VALUES('A', NULL, 'B'),
              (NULL, 'X', NULL),
              ('A', NULL, NULL),
              ('SHOULD', 'NOT', 'SELECT')
) AS test_data(col1, col2, col3)
WHERE col1 IS NULL OR col2 IS NULL OR col3 IS NULL
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes