Unable to get results using AND condition

Question

When I execute the following query then I don't get any results:

SELECT * FROM contacts_cstm WHERE id = '123' AND ftrere!='test'

And when I run the following query then it returns the result:

SELECT * FROM  contacts_cstm  WHERE ( id = '123')

Following is the table definition along with sample insert statement:

CREATE TABLE IF NOT EXISTS `contacts_cstm` (
  `id` char(36) NOT NULL,
  `ftrere` varchar(100) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `contacts_cstm` (`id`, `ftrere`) VALUES
('123', NULL);

I am not sure what I am missing. Any help will be greatly appreciated!

Answer 1

NULL fails <> just like it fails =. Use a null-safe comparison:

SELECT *
FROM contacts_cstm
WHERE id = 123 AND NOT (ftrere <=> 'test')

Note that <=> is MySQL's bespoke NULL-safe comparison operator. This is equivalent to the standard SQL operator IS NOT DISTINCT FROM.

Or be explicit about the NULL comparison:

SELECT *
FROM contacts_cstm
WHERE id = 123 AND (ftrere <> 'test' OR ftrere IS NULL);

Note: id looks like a number so I dropped the single quotes for the comparison.