mysql (5.1) > select * from database.table where not foo (on all fields)

I want to select everything in a table where the cell's value is not foo. I thought it would be something similar to WHERE NOT NULL, like SELECT * FROM database.table WHERE NOT 'foo';, but that just returned the column headers with no data.

edit: i want to do this without specifying a particular column (NOT 'foo' for all 开发者_运维知识库fields).

There's no way to do this without specifying the fields and conditions, as in

select * from table where
    col1 != value and
    col2 != value and
    ...

Depending on what you mean by

the cell's value is not foo

you may need to change and (none of the columns in a row match the condition) to or (at least one column does not match the condition).

As @Jim Garrison answers, you must specify the columns to compare your 'foo' value against. Otherwise you're testing a constant value itself, and that can be either false (zero) or true (nonzero).

SELECT * FROM database.table WHERE NOT 'foo' returns no data because 'foo' is a non-false constant value. Therefore NOT 'foo' is false for every row in the table, and so no rows match and the result is an empty set.

Jim gives one syntax for testing each column in turn. Here's another alternative syntax:

SELECT * FROM database.table WHERE 'foo' NOT IN (column1, column2, column3, ...)

However, I agree with the comment from @Mosty Mostacho. In a relational database, it's weird to test for a single value over many columns. Each column should be a different logical type of attribute, so it's uncommon to look for a similar value among many columns (not impossible, but uncommon).

It usually means you're using repeating groups of columns, which violates First Normal Form. Instead, you may need to create a child table, so that all the values you are searching are in a single column, over multiple rows.

It seems like MySQL is not sophisticated enough to do this. Instead I'll have to add an if-test in my php loop to check for foo and break if foo is present. I didn't want to do this as I have to get down into several nested loops before testing a cell's value…