English > Support
Using NULL in Advanced Search
mgpw4me@yahoo.com:
I just checked my database and my method works correctly with imdbrating (395 not rated, 3168 total, 2773 rated)...NULL check should not be necessary. I have a funny feeling that when you edit a movie, the rating might change from NULL to '0', which may account for your situation.
You'll have to add checks for each of the other ratings that is being used.
imdbrating > 0 or rating > 0 or additional rating > 0 (any movie that has a rating)
imdbrating < 1 and rating < 1 and additional rating < 1 (any movie with no rating)
AimHere is right...as best I can tell.
AimHere:
Okay, I've done a little more testing, and I think I've figured out how the "NULL" and "NOT NULL" conditions really work.
"NULL" means that a field currently has no value (as you might expect), which is useful for searching text fields. So you could, for example, search for "Description IS NULL", get a list of all movies which have no descriptions, and fill them in.
"NOT NULL", on the other hand, seems to mean that a field has had data in it at some point, regardless of whether or not it currently has data.
As a test, I ran two advanced searches: "Tagline IS NULL", which returned 1263 records out of 1364 movies, while "Tagline IS NOT NULL" returned 107... but 1263 + 107 = 1370, not 1364! The six-movie difference can be explained by the fact that six movies preciously had values in the "Tagline" field, which were subsequently cleared out. Since they're currently blank, they are included in the "NULL" condition... but they had data in them at least once, so they are ALSO included in the "NOT NULL" condition!!! In other words, "NULL" and "NOT NULL" are NOT mutually exclusive.
If I pick a random movie which has never had a tagline and give it one, then re-run both searches, the counts change to 1262 and 108. I then re-edit the movie's record, clear out the tagline, save, and run the searches once more... 1263 and 108! So, while the "NULL" result might fluctuate up or down as fields are given data or cleared out, the "NOT NULL" results will only ever increase with normal program usage.
Given this counter-intuitive behavior, I'd recommend avoiding the use of the "NOT NULL" condition for anything. A real database guru might be able to provide a valid reason for this logic, but for most people it's totally useless.
Aimhere
AimHere:
--- Quote from: mgpw4me@yahoo.com on March 05, 2010, 08:05:46 pm ---
imdbrating > 0 or rating > 0 or additional rating > 0 (any movie that has a rating)
imdbrating < 1 and rating < 1 and additional rating < 1 (any movie with no rating)
--- End quote ---
Remember, a movie can actually have a fractional rating of 0.5, so your second line won't catch them all. You'd actually need:
imdbrating = 0 and rating = 0 and additional rating = 0
Aimhere
mgpw4me@yahoo.com:
Forgot about the floating point field. I'm not fond of assuming that '0' will always be there, so I'd still check against < .01
NULL means 'unknown value'. Given the PVD interface, I don't see anyway you can set a NULL value. Simply clearing a field won't make it null...you have to run an SQL update command to set the value to null.
Reference material for those who care:
http://www.databasedesign-resource.com/null-values-in-a-database.html
rick.ca:
I hope we got this figured out without anyone's head exploding! ;D
AimHere's observation is a bit disconcerting. It's strange to see a text field classified as NULL and NOT NULL at the same time. In the case of a text field, using "0" instead of "NULL" is not available as a means of avoiding the anomaly. :-\
Navigation
[0] Message Index
[#] Next page
[*] Previous page
Go to full version