notes on search in mysql

sql search notes.sql

/*
 * searching some values in mysql is not as easy as you think
 * some search may not work way you think
 * for example, it's virtually impossible to search NULL values
 * without `IS NULL`
 *
 * you can test this queries using your local mysql or online services like sqlfiddle
 *
 */

--build schema

CREATE TABLE test
  (`val` varchar(42))
;

-- insert test values    

INSERT INTO test
  (`val`)
VALUES
    -- searching for null is trickiest
    (NULL),
    -- empty string
    (''),
    -- in mysql any non-numeric string == 0
    (0),
    -- number example
    (1),
    -- non-numeric string
    ('x'),
    -- not all numbers are >= 0
    (-1)
;

-- examples


-- show all values
SELECT val, 'all' as comment FROM test WHERE true;
-- show truthy values
-- note that non-numeric strings are not here
SELECT val, 'true' as comment FROM test WHERE val;
-- show falsy values
-- note that null is neither here nor in truthy
SELECT val, 'false' as comment FROM test WHERE NOT val;
-- x or not x is always true you say? null is still not here
SELECT val, 'x or  not x can be false' as comment FROM test WHERE val OR NOT val;
-- but this trick doesn't work
SELECT val, 'null or not null isnt false too' as comment FROM test WHERE NOT(val OR NOT val);

-- proper way to search null
SELECT val, 'null' as comment FROM test WHERE val IS NULL;
-- proper way to search not null
SELECT val, 'not null' as comment FROM test WHERE val IS NOT NULL;

-- mysql thinks non-numeric strings are zeroes
SELECT val, 'not zero' FROM test WHERE val != 0;
-- but this works
SELECT val, 'not "zero"' FROM test WHERE val != '0';