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;
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;
You should list out all the columns as per JNK's comment.
WHERE c1 IS NULL OR c2 IS NULL OR c3 IS NULL
A somewhat less efficient approach that avoids this is below though.
;WITH xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' AS ns)
SELECT *
FROM YourTable AS T1
WHERE (
SELECT T1.*
FOR XML PATH('row'), ELEMENTS XSINIL, TYPE
).exist('//*/@ns:nil') = 1
(Based on this SO answer)
I'd rather not write a bunch of AND clauses, so is there a quick, efficient way to do this?
I'm importing some data with 10 fields into a SQL Server from a CSV file. Occasionally this file has null/empty values across all the cells/columns.
What I'd like to do is just write one relatively short sql statement to simply count (at first) all these rows. I'd rather do it without doing something like:
...and (column1 is null or column1 = '')
...and (column2 is null or column2 = '')
etc...
Is there a good way to do this, or am I stuck with the above?
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 * 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
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
)
As far as I know, there isn't such a syntax.
But if all of them are numeric you can use this trick:
weight + weight_unit + length + width + height + dimensional_unit is not null
No. There are ways to code it quicker, but there are no shortcuts like you imply. Taken from an answer I gave on dba.stackexchange:
DECLARE @tb NVARCHAR(255), @sql NVARCHAR(MAX);
SET @tb = N'dbo.[table]';
SET @sql = N'SELECT * FROM ' + @tb + ' WHERE 1 = 0';
SELECT @sql = @sql + N' OR ' + QUOTENAME(name) + ' IS NULL'
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@tb);
EXEC sp_executesql @sql;
You can find the column names using something like this:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = <table_name>
Then, I would write a procedure using this, and that would loop through the entries in your table and the column names.
Source: http://codesnippets.joyent.com/posts/show/337
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.
The WHERE clause will only exclude full rows, not individual columns. For dealing with individual columns, you need to use something like NVL or Case expressions.
I don't have an Oracle instance to test against right now, but something strikes me as odd in your data. Nulls in Oracle are supposed to concatenate to empty strings. Is it possible that your vendor_address2 column actually contains the string 'NULL' instead of a null value?
Two Solutions (Column is All NULLs, Column Contains Some NULLs)
I have slightly altered your original example in order to provide two solutions:
Column_1 Column_2 Column_3
-------- -------- --------
1 2 NULL
1 NULL NULL
5 6 NULL
First, test for NULLs and count them:
select
sum(case when Column_1 is null then 1 else 0 end) as Column_1,
sum(case when Column_2 is null then 1 else 0 end) as Column_2,
sum(case when Column_3 is null then 1 else 0 end) as Column_3,
from TestTable
Yields a count of NULLs:
Column_1 Column_2 Column_3
0 1 3
Where the result is 0, there are no NULLs.
Second, let's count the non-NULLs:
select
sum(case when Column_1 is null then 0 else 1 end) as Column_1,
sum(case when Column_2 is null then 0 else 1 end) as Column_2,
sum(case when Column_3 is null then 0 else 1 end) as Column_3,
from TestTable
...But because we're counting non-NULLs here, this can be simplified to:
select
count(Column_1) as Column_1,
count(Column_2) as Column_2,
count(Column_3) as Column_3,
from TestTable
Either one yields:
Column_1 Column_2 Column_3
3 2 0
Where the result is 0, the column is entirely made up of NULLs.
If you only need to check a given column, then TOP 1 is quicker because it should stop at the first hit:
select count(*) from (select top 1 'There is at least one NULL' AS note from TestTable where Column_3 is NULL) a
0 = There are no NULLs, 1 = There is at least one NULL
SELECT COUNT(*) FROM (SELECT TOP 1 'There is at least one non-NULL' AS note FROM sat_data_active_season_group WHERE season_group IS NOT NULL) a
0 = They are all NULL, 1 = There is at least one non-NULL
I hope this helps.
we can check with the help of IN like
...WHERE NULL IN (Column_2, Column_3)
from your comment Well the multiple column will be Column_3, Column_2 in format
might be this is helpful for you
select * from (select Column_3, Column_2 from @temp where null in (Column_3, Column_2)) as Result
How about:
SELECT TOP 3 *
FROM (SELECT DISTINCT
CASE WHEN B IS NULL THEN NULL ELSE 'foo' END AS B
, CASE WHEN C IS NULL THEN NULL ELSE 'bar' END AS C
FROM T
WHERE
(B IS NULL AND C IS NOT NULL)
OR (B IS NOT NULL AND C IS NULL)
OR (B IS NULL AND C IS NULL)
) AS DT
As I understand the question, you want to know whether a null exists in any of the columns values as opposed to actually returning the rows in which either B or C is null. If that is the case, then why not:
Select Top 1 'B as nulls' As Col
From T
Where T.B Is Null
Union All
Select Top 1 'C as nulls'
From T
Where T.C Is Null
On my test rig with SQL 2008 R2 and one million rows, I got the following results in ms from the Client Statistics tab:
Kejser 2907,2875,2829,3576,3103
ypercube 2454,1738,1743,1765,2305
OP single aggregate solution (stopped after 120,000 ms) Wouldn't even finish
My solution 1619,1564,1665,1675,1674
If you add the nolock hint, the results are even faster:
Select Top 1 'B as nulls' As Col
From T With(Nolock)
Where T.B Is Null
Union All
Select Top 1 'C as nulls'
From T With(Nolock)
Where T.C Is Null
My solution (with nolock) 42,70,94,138,120
For reference I used Red-gate's SQL Generator to generate the data. Out of my one million rows, 9,886 rows had a null B value and 10,019 had a null C value.
In this series of tests, every row in column B has a value:
Kejser 245200 Scan count 1, logical reads 367259, physical reads 858, read-ahead reads 367278
250540 Scan count 1, logical reads 367259, physical reads 860, read-ahead reads 367280
ypercube(1) 249137 Scan count 2, logical reads 367276, physical reads 850, read-ahead reads 367278
248276 Scan count 2, logical reads 367276, physical reads 869, read-ahead reads 368765
My solution 250348 Scan count 2, logical reads 367276, physical reads 858, read-ahead reads 367278
250327 Scan count 2, logical reads 367276, physical reads 854, read-ahead reads 367278
Before each test (both sets) I ran CHECKPOINT and DBCC DROPCLEANBUFFERS.
Here are the results when there are no nulls in the table. Note that the 2 exists solution provided by ypercube are nearly identical to mine in terms of reads and execution time. I (we) believe this is due to advantages of Enterprise/Developer edition having use of Advanced Scanning. If you were using only the Standard edition or lower, Kejser's solution may very well be the fastest solution.
Kejser 248875 Scan count 1, logical reads 367259, physical reads 860, read-ahead reads 367290
ypercube(1) 243349 Scan count 2, logical reads 367265, physical reads 851, read-ahead reads 367278
242729 Scan count 2, logical reads 367265, physical reads 858, read-ahead reads 367276
242531 Scan count 2, logical reads 367265, physical reads 855, read-ahead reads 367278
My solution 243094 Scan count 2, logical reads 367265, physical reads 857, read-ahead reads 367278
243444 Scan count 2, logical reads 367265, physical reads 857, read-ahead reads 367278