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
) 

No, you need to apply IFNULL to each column individually.

However, if you had a slightly different requirement, that is, to show the first non-null column from a number of columns, and show a default if they are all null, you could use the COALESCE function like so:

select coalesce(col1,col2,col3,0)
from tbl

The problem with this is that it will return a single column in the result, and not multiple columns corresponding to col1, col2 and col3. Therefore, as long as you want to have multiple columns in your result set, you need to do the null handling on a per-column basis.

An extension to @db2's answer with less (read:zero) hand-wrangling:

DECLARE @tb nvarchar(512) = N'dbo.[table]';

DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb
    + N' WHERE 1 = 0';

SELECT @sql += N' OR ' + QUOTENAME(name) + N' IS NULL'
    FROM sys.columns 
    WHERE [object_id] = OBJECT_ID(@tb)
    AND is_nullable = 1;

EXEC sys.sp_executesql @sql;

With De Morgan's law:

NOT (A OR B) = (NOT A) AND (NOT B)

you save 20 chars ;)

NOT (
weight IS NULL OR
weight_unit IS NULL OR
length IS NULL OR
width IS NULL OR
height IS NULL OR
dimensional_unit IS NULL 
)

You can use NULLIF:

SELECT NULLIF(Child0,Childx) Child0,
       NULLIF(Child6,Childx) Child6,
       ....
FROM dbo.YourTable

The screenshot of your data and output clearly shows the problem. The concatenation operator automatically takes care of NULL and you don't need to do anyhting. But you don't have NULL in your tables, you have 'NULL', a string, in your tables.

SQL> select 'A' || ' ' || null || ' ' || 'B' from dual;

'A'|
----
A  B

SQL> select 'A' || ' ' || 'NULL' || ' ' || 'B' from dual;

'A'||''|
--------
A NULL B

If you really had NULL in your table, SQL Developer would display (null) in the data grid.

Clean your data, then try again.

update ap.vendors set vendor_address2 = null where vendor_address2 = 'NULL';

Alternatively, you can turn string 'NULL' to a real NULL on the fly:

SELECT vendor_name,
       vendor_address1 || ' ' || decode(vendor_address2, 'NULL', null, vendor_address2) || ' ' || vendor_city || ', ' || vendor_state || ' ' || vendor_zip_code AS "Complete Address"
FROM ap.vendors
ORDER BY vendor_name;

But don't do that. Take care of your data.

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
                    )

One way to do it is using a common table expression and union all:

;WITH CTEInnerQuery AS
(
    SELECT [someColumn] as [id], [someColumn2] as [name]
    FROM [someInterface].[someSchema].[someTable]
    WHERE [someID] = '12345'
), CTEInnerJson AS
(
    SELECT *
    FROM CTEInnerQuery
    UNION ALL
    SELECT NULL as [id], NULL as [name]
    WHERE NOT EXISTS(SELECT 1 FROM CTEInnerQuery)
)

SELECT  RA.id
    ,AllEngagementsAndJobCodes =
    (
        SELECT 
        (
            SELECT
            (...)

            For JSON Path
        ) AS  engagements
        ,
        (
            SELECT *
            FROM CTEInnerJson
            For JSON Path, INCLUDE_NULL_VALUES
        ) as jobCodes
    For JSON Path, WITHOUT_ARRAY_WRAPPER
    )

You need the ISNULL function.

SELECT ISNULL(a, b)

b gets selected if a is null.

Also, you can use the WHEN/THEN select option, lookup in BOL. Essentially: its c switch/case block meets SQL.