-1

I have a MySQL table which has a column in which some rows have the value 1. This is because i want use a query like "where column !='1' " to select all rows which do not have a 1 (the character 1) in this column/field.

Strangely, when i query the table like:

select * from table where column !='1'

all the rows where there is NULL in this same column are also treated as if there would be a 1 (character 1) in the column.

According to my DB knowledge the character 1 and NULL are definitely not the same and should not be treated the same in a query!?

I know today PDO is recommended but this is not a internet accessible web application but just a local db in which i sort some quite unimportant data. Once when i have time i will rewrite it with PDO but not at the moment.

Thanks for any help Markus

Shadow
  • 33,525
  • 10
  • 51
  • 64
WildWilly
  • 45
  • 2
  • 7

2 Answers2

1

Just add a IS NULL to your query.

select * from table WHERE column <> '1' OR column IS NULL

SpicyPhoenix
  • 308
  • 1
  • 8
0

When the column's value is NULL then the condition is:

WHERE NULL !='1'

and since any comparison to NULL has as result the value NULL then the condition is:

WHERE NULL

which clearly is not

WHERE TRUE

so that row is not returned.
What you can do if you want NULLs in the results is to explicitly include the condition in the WHERE clause:

WHERE column !=' '1' OR column IS NULL
forpas
  • 160,666
  • 10
  • 38
  • 76