How to get rows whose columns values are not null

问题:

I want to get rows of a table such that no column value is null. No hardcoding of column values. I have hundreds of column names so.

Output should be only row 2 since all that row has the values for all the columns. I do not want to specify all the column names for is not null. It should take it programmatically. Even if i add a new column it should work without changing the query. That is my vision.

回答1:

I found something, but that means using CURSOR

DECLARE @ColumnName VARCHAR(200)
DECLARE @ColumnCount INT
DECLARE @sql VARCHAR(400)

CREATE TABLE #tempTable (Id INT)

DECLARE GetNonNullRows CURSOR 
FOR 
    SELECT c.NAME, (SELECT COUNT(*) FROM sys.columns col WHERE col.object_id = c.OBJECT_ID)  FROM sys.tables AS t
    JOIN sys.columns AS c ON t.object_id = c.object_id
    WHERE t.name = 'SomeTable' AND t.type = 'U'

OPEN GetNonNullRows
FETCH NEXT FROM GetNonNullRows INTO @ColumnName, @ColumnCount
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'SELECT st.UniqueId FROM SomeTable AS st WHERE ' + CONVERT(varchar, @ColumnName) + ' IS NOT NULL'    
    INSERT INTO #tempTable
    EXEC (@sql)

FETCH NEXT FROM GetNonNullRows INTO @ColumnName, @ColumnCount
END 

CLOSE GetNonNullRows
DEALLOCATE GetNonNullRows

SELECT * FROM SomeTable AS st1
WHERE st1.UniqueId IN (SELECT Id FROM #tempTable AS tt
GROUP BY Id
HAVING COUNT(Id) = @ColumnCount)


DROP TABLE #tempTable

Let me to explain this a little.

First i create cursor which iterate through all the columns of one table. For each column, I've create sql script to search in table for not null values for selected column. For those rows that satisfies criteria, I take its unique ID and put in temp table, and this job I am using for all columns.

At the end only ID's which count is like columns count are your result set, because only rows that have identical number of appearances like number of columns in table may be rows with all non null values in all columns.

回答2:

Try this ::

SELECT * FROM mytable WHERE column IS NOT NULL

回答3:

try using IS NOT NULL

SELECT * FROM table WHERE field_name IS NOT NULL

For more information, check out the mysql manual on working with null values.

回答4:

try this

SELECT * 
FROM your_table_name
where coalesce(column_1, column_2, column_3, ...., column_n) is not null 

回答5:

SQL alone cannot express such a concept.

You have to dinamically build the SQL query according to the table definition using some procedural language. In Oracle you can use the dictionay view USER_TAB_COLUMNS to build the column list.