I have a table called table1
It has 100 columns: {col1, col2, ...,col100}
I understand how to SELECT rows not containing null values in a specific column for instance col1:
SELECT *
FROM table1
WHERE col1 IS NOT NULL
How do I
SELECTall rows that do not contain null values in any column
SELECT *
FROM table1
WHERE * IS NOT NULL
but this returns an error in MySQL (which I am using)
You need to explicitly list each column. I would recommend:
select t.*
from t
where col1 is not null and col2 is not null and . . .
Some people might prefer a more concise (but slower) method such as:
where concat(col1, col2, col3, . . . ) is not null
This is not actually a simple way to express this, although you can construct the query using metadata table or a spreadsheet.
While I would recommend writing out each column name and refactoring your table as suggested, here's an option using dynamic sql:
SET @sql = NULL;
SELECT CONCAT('SELECT * FROM table1 WHERE ',
GROUP_CONCAT(c.COLUMN_NAME SEPARATOR ' IS NOT NULL AND '),
' IS NOT NULL') INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'table1'
ORDER BY c.ORDINAL_POSITION;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
To return rows with no nulls in any columns: (in PostgresSQL)
SELECT *
FROM *tablename*
WHERE *tablename*.* IS NOT NULL
Get the length of each column and multiply them. If the result is not null then all columns are non null values. Try following sample code. Since I am not using multiple AND clause, it is a better approach.
CREATE TABLE #Temp (OdSal INT,NewSal INT,EmpName CHAR(20),IsCurrentEmp BIT)
INSERT INTO #Temp VALUES
(100,150,'Vikas',1), -- No null records
(NULL,NULL,NULL,NULL), -- all records are null
(NULL,NULL,'Nayanthara',1), -- more than 1 column is null
(NULL,150,'Priyamani',1) -- only one column is null
--Will return only one row
SELECT * FROM #Temp
WHERE LEN(OdSal) * LEN(NewSal) * LEN(EmpName) * LEN(IsCurrentEmp) IS NOT NULL
WHERE col1 IS NOT NULL AND col2 IS NOT NULL AND .....and colx is not nullWHERE col1 + col2 + ... + col100 IS NOT NULL.