Using IS NOT NULL for multiple columns

Question

I want to check for the is not null constraint for multiple columns in a single SQL statement in the WHERE clause, is there a way to do so? Also I don't want want to enforce the NOT NULL type constraint on the column definition.

SELECT * FROM AB_DS_TRANSACTIONS 
WHERE FK_VIOLATION IS NULL 
AND TRANSACTION_ID NOT IN(
    SELECT distinct TRANSACTION_ID FROM AB_TRANSACTIONS) 
AND COUNTRY_ID IS NOT NULL 
AND GEO_CUST_COUNTRY_ID IS NOT NULL 
AND INVOICE_DATE IS NOT NULL 
AND ABB_GLOBALID IS NOT NULL 
AND SALES_ORG_ID IS NOT NULL 
AND DIST_ID IS NOT NULL 
AND CUSTOMER_ID IS NOT NULL 
AND REPORT_UNIT_ID IS NOT NULL 
AND CURR_INVOICE IS NOT NULL 
AND DIVISION_CODE IS NOT NULL

So instead of using IS NOT NULL again and again I want to simplify things

There is nothing to simplify. except you in with distinct. — IVNSTN
– IVNSTN, Commented Mar 1, 2016 at 8:09

Raju · Accepted Answer · 2016-03-01 01:48:33Z

22

You can use

SELECT * FROM table1 
WHERE NOT (Column1 IS NULL OR 
Column2 IS NULL OR
 Column3 IS NULL OR
 Column4 IS NULL
    IS NOT NULL)

As per OP comment, Updating answer

Inserting Rows by Using INSERT and SELECT Subqueries

INSERT INTO Table_A
    SELECT column1, column2, column3,column4 
    FROM Table_B 
    WHERE NOT (Column1 IS NULL OR 
    Column2 IS NULL OR
    Column3 IS NULL OR
    Column4 IS NULL
    IS NOT NULL);

Your query

I am able to reduce 50 chars approx

SELECT * FROM AB_DS_TRANSACTIONS 
WHERE 
FK_VIOLATION IS NULL 
AND TRANSACTION_ID NOT 
IN(SELECT distinct TRANSACTION_ID FROM AB_TRANSACTIONS) 
AND 
NOT (
COUNTRY_ID IS NULL  
OR GEO_CUST_COUNTRY_ID IS NULL 
OR INVOICE_DATE IS NULL 
OR ABB_GLOBALID IS NULL 
OR SALES_ORG_ID IS NULL 
OR DIST_ID IS NULL 
OR CUSTOMER_ID IS NULL 
OR REPORT_UNIT_ID IS NULL 
OR CURR_INVOICE IS NULL 
OR DIVISION_CODE IS NULL
)

edited Mar 1, 2016 at 1:48

answered Feb 29, 2016 at 23:36

Raju

3,0028 gold badges37 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Newbie Over a year ago

Thanks Raju. I actually want to use this with an insert statement like this INSERT INTO A SELECT * FROM B WHERE ID IS NOT NULL AND NAME IS NOT NULL AND AGE IS NOT NULL ........ Basically i dont want to insert any null values into the table B

Newbie Over a year ago

Hi Raju I am getting an error using the syntax you suggested - an expression of non boolean type specified in a context where a condition is expected, near AND

Newbie Over a year ago

I think I cant :(You must have 20 reputation on Stack Overflow to talk here. See the faq.

cameront Over a year ago

COALESCE does not work in the way described here. It is actually an OR statement. The way it works is - Once it finds the first non-null value it stops looking and substitutes in that non-null value. try this query to see for yourself. SELECT COALESCE(COLUMN1, COLUMN2, COLUMN3), COLUMN1, COLUMN2, COLUMN3 from table_B. The query is finding if one of those columns is non-null, not all of them.

Raju Over a year ago

@cameront, That's a valid point. Thanks for correcting. I have updated my answer

|

Adrian Mole · Accepted Answer · 2021-08-03 14:01:59Z

17

SELECT * FROM AB_DS_TRANSACTIONS 
WHERE COALESCE(COUNTRY_ID,GEO_CUST_COUNTRY_ID,INVOICE_DATE,ABB_GLOBALID,SALES_ORG_ID,DIST_ID,CUSTOMER_ID,REPORT_UNIT_ID,CURR_INVOICE,DIVISION_CODE) IS NOT NULL

edited Aug 3, 2021 at 14:01

Adrian Mole

52.1k193 gold badges61 silver badges101 bronze badges

answered Aug 3, 2021 at 14:00

user3414230

3272 silver badges3 bronze badges

3 Comments

Alan Over a year ago

This should be the #1 answer

Tiago Marmitt Over a year ago

This is wrong, the ideia is check if ALL columns that were informed are not null. Coalesce(null, null, null, not null) is not null returns true because the last value is not null but the ideia is check all of the values.

Mishalb Over a year ago

Are you saying that the COALESCE function would work if OP wanted OR statements?

Undo · Accepted Answer · 2016-03-12 14:55:05Z

9

I think the syntax that you are looking for to show only those rows where all are not null is this

SELECT * from table_B
where COLUMN1 is not null and COLUMN2 is not null and COLUMN3 is not null

edited Mar 12, 2016 at 14:55

Undo♦

25.7k38 gold badges114 silver badges132 bronze badges

answered Mar 1, 2016 at 1:15

cameront

7106 silver badges10 bronze badges

5 Comments

Newbie Over a year ago

yes i just edited my post and have copied the query there

cameront Over a year ago

Why did you vote down the answer, it is useful and correct. Please vote it back up.

Raju Over a year ago

@cameront, OP already has a working query. Your suggestion is not simplifying the query. I am able to simplify 50 chars & it is working. Please cross check my answer

cameront Over a year ago

Yes, but that is not what voting down is for. If you think your answer is better, you cant simply vote down some one else's answer. leave it neutral and let others vote it up or down. If the answer is plan incorrect or wrong then fare enough, but not if you think your answer is better. Leave it neutral. Also the question has been updated multiple times now, with more clarification which might make some unusual use of demorgan's law slightly shorter but definitely not simpler or better.

Raju Over a year ago

@cameront, you are correct. Question updated multiple times. Obviously, There will be better answer than mine. Like concatenate all cols, check the length of the result etc.. Due to some issue with SSMS, I could not check it. May be you can do that. If you can update your answer, I can cast my vote. :)

i_am_cris · Accepted Answer · 2020-06-23 12:28:27Z

2

In BigQuery (might work in other db)- I would use the concat-function. Make sure to have strings or cast fields to strings where needed. Concat returns null if one of the fields is null.

SELECT * FROM AB_DS_TRANSACTIONS 
WHERE FK_VIOLATION IS NULL 
AND TRANSACTION_ID NOT IN(
    SELECT distinct TRANSACTION_ID FROM AB_TRANSACTIONS) 
AND concat(COUNTRY_ID,GEO_CUST_COUNTRY_ID,INVOICE_DATE,ABB_GLOBALID,SALES_ORG_ID,DIST_ID,CUSTOMER_ID,REPORT_UNIT_ID,CURR_INVOICE,DIVISION_CODE) IS NOT NULL

answered Jun 23, 2020 at 12:28

i_am_cris

6372 gold badges10 silver badges21 bronze badges

Comments

JazzTheFlute · Accepted Answer · 2023-02-02 19:42:38Z

1

I think one strategy can be using least function?

The limitation here would be that all the arguments must be of the same type, so looks like OP's columns may need to be converted to a str or something.

If we can get past that limitation, I think the below should work to check if any of the columns are null:

SELECT  * 
FROM AB_DS_TRANSACTIONS 
WHERE FK_VIOLATION IS NULL 
AND TRANSACTION_ID NOT IN( SELECT distinct TRANSACTION_ID FROM AB_TRANSACTIONS) 
AND least(COUNTRY_ID
          ,GEO_CUST_COUNTRY_ID
          ,INVOICE_DATE
          ,ABB_GLOBALID
          ,SALES_ORG_ID
          ,DIST_ID
          ,CUSTOMER_ID
          ,REPORT_UNIT_ID
          ,CURR_INVOICE
          ,DIVISION_CODE
          ) IS NOT NULL 
;

ps - using snowflake.

edited Feb 2, 2023 at 19:42

answered Feb 2, 2023 at 19:42

JazzTheFlute

112 bronze badges

Comments

Z. Bt · Accepted Answer · 2023-02-19 17:25:51Z

1

It seems like this should be the elegant one: (at least for newer versions of MSSQL)

SELECT * FROM tbl WHERE NOT COL1+COL2+COL3+COL4 IS NULL

edited Feb 19, 2023 at 17:25

answered Feb 19, 2023 at 17:23

Z. Bt

112 bronze badges

Comments

abolfazl sadeghi · Accepted Answer · 2023-12-03 23:00:28Z

0

You can build coalesce() function like this:

    select *
      from AB_DS_TRANSACTIONS
     where FK_VIOLATION is null
       and TRANSACTION_ID not in (select distinct
                                         TRANSACTION_ID
                                    from AB_TRANSACTIONS)
       and coalesce(COUNTRY_ID, 
                    GEO_CUST_COUNTRY_ID, 
                    INVOICE_DATE, 
                    ABB_GLOBALID, 
                    SALES_ORG_ID, 
                    DIST_ID, 
                    CUSTOMER_ID, 
                    REPORT_UNIT_ID, 
                    CURR_INVOICE, 
                    DIVISION_CODE, 
                    1) <> 1

As the result, if one of your fields is NOT NULL, then the coalesce() expression will return 'True'

edited Dec 3, 2023 at 23:00

abolfazl sadeghi

2,4182 gold badges15 silver badges23 bronze badges

answered Nov 29, 2023 at 11:51

Eugeniusz Chimoroda

1

3 Comments

BenderBoy Over a year ago

I don't think this answers the question, because OP needs to know whether all columns are not null. Your code will give results in which ANY column is not null. Even if that was what you needed, it may give false negatives if, for example, COUNTRY_ID were 1.

Eugeniusz Chimoroda Over a year ago

@BenderBoy, coalesce() : Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL. For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that isn't null. The last comparing value can be any other, distinct from checked columns, even cast(getdate() as date)

BenderBoy Over a year ago

yes, that is why it doesn’t work for the question at hand ("find rows in which NO column is null") and also not for the question "find rows with at least one filled column". Also, this question is 7 years old and several other answers have already proposed coalesce()…

Jeremy Fortune · Accepted Answer · 2023-06-24 20:13:50Z

-1

We checked with multiple time with comma, So its works for me.

select TOTAL_ST_ABCD_AC_USD_AMT, TOTAL_ST_ABCD_AC_AMT  from ABCD_SRC_ST_ABCD_OPS_WKLY where TOTAL_ST_ABCD_AC_USD_AMT is not null and TOTAL_ST_ABCD_AC_USD_AMT is not null;

edited Jun 24, 2023 at 20:13

Jeremy Fortune

2,5191 gold badge20 silver badges21 bronze badges

answered Jun 16, 2023 at 10:09

Raj Kumar Ram

112 bronze badges

Collectives™ on Stack Overflow

Using IS NOT NULL for multiple columns

8 Answers 8

Inserting Rows by Using INSERT and SELECT Subqueries

Your query

7 Comments

3 Comments

5 Comments

Comments

Comments

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

Inserting Rows by Using INSERT and SELECT Subqueries

Your query

7 Comments

3 Comments

5 Comments

Comments

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related