Select columns that are not null in any record

Question

I need to analyze a MySQL table and want to determine all columns that never contain NULL, 0 or an empty string in any record in that table. I do not have a clue how to do that, since MySQL expects me to select the columns at the beginning of the statement. I thought I could maybe rotate the table by 90° and then do something like

SELECT column_header FROM rotated_table WHERE record_1 <> NULL AND record_2 <> NULL AND [...]

But this seems to be a lot of work. Is there an easier way to get the information i require?

Update example:

Table1:

name        street      zip
MyName      1st Ave.    1000
OtherName   2nd Street  NULL

My statement now should show something like:

name        street
MyName      1st Ave.
OtherName   2nd Street

Because the column zip contains a NULL value. If there was an additional row like

name        street      zip
MyName      1st Ave.    1000
OtherName   2nd Street  NULL
NULL        Foo blvd.   3453

It should return

street
1st Ave.
2nd Street
Foo blvd.

Because name and zip contain at least one NULL value.

Answer 1

You can use the behavior of COUNT ignoring NULL values to your advantage.

Subtract the count of the column you're examining from the count of the number of rows. Any column that returns a value of 0 does not contain a NULL value. You'll need to use a CASE statement to convert any values you consider to be "empty" into NULL.

This approach also eliminates the need copying the entire table in order to "rotate" it.

I whipped up an example here in SQLFiddle which should work for you.

Here's the content of my SQLFiddle example in case the link becomes unusable:

CREATE TABLE address
(
    address int auto_increment primary key, 
    street1 varchar(20),
    street2 varchar(20),
    city varchar(20),
    state varchar(20),
    zip int,
    comment varchar(20)
);

INSERT INTO address
(street1, street2, city, state, zip, comment)
VALUES
('123 Main St.', null, 'Cleveland', 'OH', 44123, ''),
('1313 Mockingbird Ln.', null, 'Cleveland', 'OH', 0, 'Unknown zip'),
('321 Main St.', 'Apt #1', 'Cleveland', 'OH', 44123, ''),
('321 Main St.', 'Apt #2', 'Cleveland', 'OH', 44123, '');

SELECT
COUNT(*) rows, -- not really needed, you can remove this
COUNT(*) - COUNT(CASE ad.street1 WHEN '' THEN NULL ELSE ad.street1 END) empty_street1,
COUNT(*) - COUNT(CASE ad.street2 WHEN '' THEN NULL ELSE ad.street2 END) empty_street2,
COUNT(*) - COUNT(CASE ad.city WHEN '' THEN NULL ELSE ad.city END) empty_city,
COUNT(*) - COUNT(CASE ad.state WHEN '' THEN NULL ELSE ad.state END) empty_state,
-- Change the value being compared based on the column type.  Strings '', numbers 0, etc.
COUNT(*) - COUNT(CASE ad.zip WHEN 0 THEN NULL ELSE ad.zip END) empty_zip,
COUNT(*) - COUNT(CASE ad.comment WHEN '' THEN NULL ELSE ad.comment END) empty_comment
FROM address ad;

Which results in:

ROWS    EMPTY_STREET1   EMPTY_STREET2   EMPTY_CITY  EMPTY_STATE     EMPTY_ZIP   EMPTY_COMMENT
4       0               2               0           0               1           3