WHERE all IS NOT NULL

How can I run a MySQL query that selects everything that is not null? It would be something like

SELECT * FROM schedule WHERE ((all)) IS NOT NULL

Do I just remove the all and go..?

SELECT * FROM schedule WHERE IS NOT NUL开发者_如何学运维L

---

You'll have to explicitly state the condition on each column, so e.g.

SELECT *
FROM schedule 
WHERE id IS NOT NULL
  AND foo IS NOT NULL
  AND bar IS NOT NULL; -- and so on..

---

You need to get a list of the columns of your table, by looking at the information_schema database.

Let's suppose that your database is called mydata and your table in question is named mytable. You can get the list of the table's nullable columns by issuing the following statement:

SELECT `COLUMN_NAME`
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'mydata'
    AND `TABLE_NAME` = 'mytable'
    AND `IS_NULLABLE` = 'YES'

Our final query will look like this:

SELECT * FROM `mydata`.`mytable`
WHERE CONCAT(<list of columns>) IS NOT NULL

All we are missing now is the list of nullable columns, comma-separated. We're going to use the GROUP_CONCAT function to produce the final statement, which we will execute like this:

SET @query = CONCAT(
    'SELECT * FROM `mydata`.`mytable` WHERE CONCAT(',
    (SELECT GROUP_CONCAT(COLUMN_NAME)
        FROM `information_schema`.`COLUMNS`
        WHERE `TABLE_SCHEMA` = 'mydata' AND
        `TABLE_NAME` = 'mytable'
        AND `IS_NULLABLE` = 'YES'),
    ') IS NOT NULL');

PREPARE stmt_name FROM @query;

EXECUTE stmt_name;

References:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html

---

It depend on what you mean exactly by "everything that is not null":

• all columns must not be null

  select * from schedule where col1 is not null AND col2 is not null AND ..

• at least one column should be non-null

  select * from schedule where col1 is not null OR col 2 is not null OR ..

At least this is how you would do it in "general sql". I don't know if MySql has special syntax for this.

---

I've just looked at your linked question and agree with Guffa's answer that you should normalise your database.

The above looks more like a spreadsheet then something that belongs in an RDBMS.

To answer your concerns about this being the way you want it displayed. You could write a pivot query and put it in a view to mimic your current table structure and use this for your display query.

This will avoid the need for some horrific 24 column WHERE clause whenever you want to search for data or find out if it is NULL as in this question.

---

this works only with char and varchar type. If you are having other datatypes it throws error.

SELECT *
FROM SCHEDULE
WHERE coalesce(ID, FOO, BAR) IS NOT NULL;

inconsistent datatypes: expected CHAR got DATE Note: here FOO is of date type.

---

If you are using another programming language combined with sql, you could speed up this process by looping through a list of column names and using a parameter to do the if not null check rather than have to type them all in individually e.g

**code SQL**

Select *
FROM Schedule
WHERE @ColumnName IS NOT NULL

**code c#**

for(int i =0; i<parameterArray[].length; i++)
{
command.Parameters.Add(new SqlParameter("ColumnName", parameterArray[i]));
command.Execute();
}

I would suggest using transactions so you can do the above in one batch after the for loop has run through.

---

Use XML so you don't have to iterate through all your columns:

--Your Source Table
CREATE TABLE [dbo].[schedule](
    [id] [nchar](10) NULL,
    [col1] [nchar](10) NULL,
    [col2] [nchar](10) NULL,
    [col3] [nchar](10) NULL
) ON [PRIMARY]

--I know my result should show items NOT NULL, like id:2,col1,val:'a'
  INSERT INTO [schedule](id,col1,col2,col3)
  values 
  (1,null,null,null),
  (2,'a',null,null),
  (3,null,'b',null),
  (4,null,null,null),
  (5,null,null,'c')

--temp placeholder table 
CREATE TABLE #tempsch (id VARCHAR(max) not null, schColumns XML) 

--Load temp table with XML version of your table
INSERT INTO #tempsch (id, schColumns)
        SELECT  s.id,
            (   SELECT * 
                FROM    [schedule] AS x
                WHERE   x.id = s.id
                FOR XML PATH('bar'), TYPE, ROOT('foo')
            ) AS xml_format_string
        FROM [schedule] AS s


--This select should return all values Where all is NOT NULL
SELECT id,
sd.c.value('local-name(.)', 'VARCHAR(MAX)') AS elementName, 
sd.c.value('.', 'VARCHAR(MAX)') AS elementValue
FROM #tempsch s
CROSS APPLY schColumns.nodes('/foo/bar/*') sd(c)
WHERE 
sd.c.value('local-name(.)', 'VARCHAR(MAX)') <> 'id' 

---

This command will help you to check all the column_values is not null

SELECT * FROM table_name WHERE CONCAT(column_name,column_name....) IS NOT NULL; 

---

I would not do this, but to replace someone else's horrible idea., especially do not do this -- this is what they suggested:

SELECT *
  FROM SCHEDULE
 WHERE ID || FOO || BAR IS NOT NULL;

Don't do this either, but at least it isn't as bad...

SELECT *
  FROM SCHEDULE
WHERE coalesce(ID, FOO, BAR) IS NOT NULL;

This at least works on other versions of SQL, and some compilers will expand it to a series of IS NOT NULL.

Just do what the accepted answer says