Q
Problem solve Get help with specific problems with your technologies, process and projects.

Select only non-null columns

Is there a way to pull only non-null fields from a query?

Is there a way to pull only non-null fields from a query? For example:

select (only non-null fields) 
from table where field = (some value)

Thanks.

The only columns which are guaranteed to be non-null are those declared with NOT NULL when the table is created. So you will first have to obtain the table definition to see. In some databases you can say SHOW CREATE tablename or DESCRIBE tablename, or you could query the INFORMATION_SCHEMA tables.

If a column is nullable, i.e., declared with NULL when the table is created, then a particular occurrence of the column might be NULL—or it might not—in any one or more rows, or in no rows at all. The only way to determine if a nullable column actually contains any NULLs is to query the column. For example, you could run a query like this:

select count(*) as column37_nulls
  from yourtable
 where column37 is null
   and somefield = (some value)

This query will tell you whether there are any NULLs in column37, for the rows identified by your stated criterion, somefield = (some value). You would need to do this for each column that you determined was nullable from the table layout.

Of course, once you finish querying all the nullable columns to find out which ones have no NULLs, you will have queried the table as many times as there are nullable columns.

Then take the results of all those queries, which identified which columns have no NULLs in them, and combine those with the non-nullable columns (which you determined from the table definition), to put together your final query, "select (only non-null fields)".

Now, maybe I didn't understand what you're doing or why you can't return a column if it has some NULLs in it, but the above procedure—manual, labour intensive, and inefficient as it is—will give you what you asked for.

This was last published in November 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close