how to filter in sql script to not include any column null

imagine there are 50 columns. I dont wan't any row that includes a null value. Are there any tri开发者_运维百科cky way?

SQL 2005 server

Sorry, not really. All 50 columns have to be checked in one form or another.

Column1 IS NOT NULL AND ... AND Column50 IS NOT NULL

Of course, under these conditions why not disallow NULLs in the first place by having NOT NULL in the table definition

If it's SQL Server 2005+ you can do something like:

SELECT fields
FROM MyTable
WHERE stuff
EXCEPT -- This excludes the below results
SELECT fields
FROM MyTable
WHERE (Col1 + Col2 + Col3....) IS NULL

Adding a null to a value results in a null, so the sum of all your columns will be NULL.
This may need to change based on your data types, but adding NULL to either a char/varchar or a number will result in another NULL.

If you are looking at the values not being null, you can do this in the select statement.

SELECT ISNULL(firstname,''), ISNULL(lastname,'') FROM TABLE WHERE SOMETHING=1

This will replace nulls with string blanks. If you want another value use: ISNULL(firstname,'empty') for example. You can use anything where the word empty is.

I prefer this query

select *
from table
where column1>''
and column2>''
and (column3>'' or column3<'')

Allows sql server to use an index seek if the proper index/es exist. you would have to do the syntext for column 3 for any numeric values that could be negative.