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

Posted on 2009-04-22
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
    LVL 9

    Expert Comment

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

    Expert Comment

    you must use All column names instead of *.
    LVL 7

    Expert Comment

    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.

    Author Comment

    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.
    LVL 7

    Accepted Solution

    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

    Thanks a lot for the help

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now