How do I select all not null values in a MySQL query

Posted on 2009-04-22
Medium Priority
Last Modified: 2012-05-06
I am trying to write a MySQL query which will find all values in a table that aren't null.

I won't know in advance what the names of the columns are so can't specifically name them when using NOT NULL.

What I want is something like this which would actually work:
Question by:intechfs

Expert Comment

by:Sreedhar Vengala
ID: 24203339
Does that mean if a column in row is null => should return that row or not ?
LVL 21

Expert Comment

by:K V
ID: 24203797
you must use All column names instead of *.

Expert Comment

ID: 24204120
If you do not have the column names in advance, it is more difficult and I think not possible just with SQL. A solution is to write a little function that gives you all rows (or just the fields) with Null-Values in one of the fields. There are two ways:
1) creates a new (dummy-)table and writes all rows to this table.
2) writes just the unique index (primary key) of your table to a dummy-table. It assumes that your table has a primary key. If not you can simply add a field autonumber, so all rows have a key. With a join between you table and the key-table you get all rows containing a null-value.

For 2) I can provide you a little script, if this is a way you might go.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 24204429
sree_ven I am wanting to have those not returned.

coffeeshop - could you please show me the script you would use for 2.I'm taking this out in Perl and will probably just loop through the results and ignore anything null but if what your suggesting looks more efficient I may do that

Thanks a lot for the replies.

Accepted Solution

coffeeshop earned 2000 total points
ID: 24205291
OK, my script needs some precautions:
1) A table "tblRows" with one field "RowID", holding all row-indexes with Null-values
2) Your table should have an unique key as first field (autonumber would be sufficent)
3) Start it with: FindAllNullFields "YourTableName"
4) Add a query joining your table and tblRows

All this can be done automatic, but this needs more scripting. Just take it as suggestion for your solution.
Public Sub FindAllNullFields(ByVal sTableSearch As String)
  Dim rsTemp As ADODB.Recordset
  Dim sSQL As String
  Dim i As Integer
  Dim sTableResults As String
  sTableResults = "tblRows"
  sSQL = "DELETE * FROM " & sTableResults
  CurrentDb.Execute sSQL
  sSQL = "SELECT * FROM " & sTableSearch
  Set rsTemp = New ADODB.Recordset
  rsTemp.Open sSQL, CurrentProject.Connection, adOpenKeyset
  While Not (rsTemp.EOF)
    'Is there a Null-Value in one of the column?'
    For i = 1 To rsTemp.Fields.Count - 1
      If IsNull(rsTemp.Fields(i)) Then
        'Write the index to a Dummy-Table'
        sSQL = "INSERT INTO " & sTableResults & " ( RowID ) VALUES ( " & CLng(rsTemp.Fields(0)) & " )"
        CurrentDb.Execute sSQL
        Exit For
      End If
    Next i
  Set rsTemp = Nothing
End Sub

Open in new window


Author Closing Comment

ID: 31573209
Thanks a lot for the help

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question