Is there a way to pull only non-null fields from a query? For example:
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
select (only non-null fields) from table where field = (some value)
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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.