How to Check If More Than One Column Is Not Null Per Record

Using IS NOT NULL for multiple columns

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
) 

Select rows where a column is not null if multiple values or null if 1 value

You can use a NOT EXISTS:

SELECT  DISTINCT T.Title
,       T.Value 
FROM    mytable T
WHERE   T.Value IS NOT NULL
    OR  NOT EXISTS (
                        SELECT  NULL
                        FROM    mytable T2
                        WHERE   T2.Value IS NOT NULL
                            AND T2.Title = T1.Title
                    )

Optimize way of Null checking for multiple columns

You can check the row for not NULL values in three ways:

1. COALESCE(col1, col2, col3) IS NOT NULL
2. col1 IS NOT NULL OR col2 IS NOT NULL OR col3 IS NOT NULL
3. ISNULL(col1, ISNULL(col2, ISNULL(col3, NULL))) IS NOT NULL

You can use the Microsoft SQL Server Management Studio to compare multiple querys.

result of comparison:

• COALESCE vs. IS NOT NULL: 57% to 43%
• COALESCE vs. ISNULL: 56% to 44%
• IS NOT NULL vs. ISNULL: 49% to 51%

So using IS NOT NULL is the fastest way to check if a row has a column without a NULL value. In case of readability the COALESCE can be much shorter than a IS NOT NULL or ISNULL comparison. You can decide between readability and speed.

check if all rows have column not null

Applying Tim's logic for all rows with the same zamodb_id using Windowed Aggregates:

with cte as 
 ( select z.zamodb_id,zp.zamodbpoz_id,zp.data_planowana
      ,count(*)                 over (partition by z.zamodb_id) as cntStar
      ,count(zp.data_planowana) over (partition by z.zamodb_id) as cntCol
   from zamodbpoz zp
   ....
 )
select *
from cte
where cntStar = cntCol

If a column is not null then convert into a row for multiple columns

CROSS APPLY combined with UNION ALL is very useful here:

SELECT
    t.col1, t.col2, t.col3,
    v.*
FROM table t
CROSS APPLY (
    SELECT col4, NULL, NULL
    WHERE col4 IS NOT NULL
    UNION ALL
    SELECT NULL, col5, NULL
    WHERE col5 IS NOT NULL
    UNION ALL
    SELECT NULL, NULL, col6
    WHERE col6 IS NOT NULL
) v

If you have many columns this gets tedious. Futhermore, this type of table design is generally incorrect. What you need is a straightforward UNPIVOT:

SELECT
    upvt.col1,
    upvt.col2,
    upvt.col3,
    upvt.ColName,
    upvt.Value
FROM table t
UNPIVOT ( Value FOR ColName IN
    (Col4, Col5, Col6, Col7, Col8, Col9)
) upvt

Checking whether any two of ten fields are different (not NULL or equal)

UNPIVOT the columns to rows and then GROUP BY your primary key and COUNT the DISTINCT values in the unpivoted columns to see if there is more than one unique value:

Oracle 11 Setup:

CREATE TABLE table_name ( id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 ) AS
  SELECT 1, 'A', 'A',  NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM DUAL UNION ALL
  SELECT 2, 'A', NULL, 'B',  NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM DUAL UNION ALL
  SELECT 3, 'A', NULL, 'A',  'A',  NULL, 'A',  'A',  'A',  'A',  'A'  FROM DUAL UNION ALL
  SELECT 4, 'A', NULL, 'A',  'A',  'B',  NULL, NULL, NULL, NULL, NULL FROM DUAL;

Query:

SELECT id
FROM   table_name
UNPIVOT ( value FOR name IN ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 ) )
GROUP BY id
HAVING COUNT( DISTINCT value ) > 1

Output:

| ID |
| -: |
|  2 |
|  4 |

db<>fiddle here

---

Related Topics