NULL and single quotes ' '

[robert_gsfame]

I have query like this SELECT * FROM table1 WHERE folder=''

the problem is no result appear when using that query, then i change to folder is NULL and it works..

 

What is the different between those two ??? if i want to have any records where folder is empty then which one should i use NULL or ''???

[andrewgauger]

NULL means no value assigned.  '' is an assignment.  You probably want:

 

 

WHERE folder IS NULL OR folder = ''

 

Use NULL where nothing has been assigned to it.  Don't assign '' (although most php I've seen assigns '' when the variable is NULL).  If you want to clear a value use SET folder=NULL.

 

I actually tried to make that coherent.  Essentially what I am trying to say:

 

$sql="UPDATE folder_table SET (folder='$folder')"

 

If $folder=NULL in PHP then SQL is going to set the field as '' because the variable is surrounded by ticks. 

[robert_gsfame]

I try folder=NULL OR folder=''

but still i found wrong records shown up....

[DavidAM]

WHERE folder = NULL

will never be true.  Because NULL means unknown, nothing is equal to it including another NULL.  You have to use

WHERE folder IS NULL

to find rows that have a NULL value in the folder column.

[andrewgauger]

WHERE folder IS NULL OR folder = '' 

[DavidAM]

or even

WHERE IFNULL(folder, '') = ''

[andrewgauger]

but not

WHERE folder IN(NULL, '')

[fenway]

but not

WHERE folder IN(NULL, '')

I've always HATED that.

[robert_gsfame]

i rather set everything to blank ''

[andrewgauger]

but not

WHERE folder IN(NULL, '')

I've always HATED that.

 

WHERE / IN in general?

[fenway]

No, the fact that the optimizer is dumb about explicit nulls.