Mysql not equal operator failing?

Question

I have the following mysql table (simplified):

CREATE TABLE IF NOT EXISTS `mytable` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`emails` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`phones` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`history` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

PRIMARY KEY (`id`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=259 ;

I have the following query :

SELECT *
FROM mytable
WHERE emails LIKE '%addr@yahoo.com%'
OR phones LIKE '%addr@yahoo.com%'
AND history !='None'

This is producing 2 records:

INSERT INTO `mytable` (`id`, `emails`, `phones`, `history`) VALUES
(118, 'PLEASE SET', NULL, 0, 'addr@yahoo.com', NULL, 0, 'None'),
(237, 'PLEASE SET', NULL, 0, 'addr@yahoo.com', NULL, 0, 'gomez');

but I was expecting the one with history = 'None' not to be included. What am I doing wrong?

I think the different operator in MySQL is `<>` and not `!=` — Marco Aurélio Deleu, Jan 12 '15 at 21:05
@MarcoAurélioDeleu: Both `!=` and `<>` are "not equals" in MySQL. — gen_Eric, Jan 12 '15 at 21:07

score 2 · Answer 1 · answered Jan 12 '15 at 21:06

2

You should explicitly set parenthesis in your WHERE clause :

WHERE (all_emails LIKE '%addr@yahoo.com%'
   OR all_phones LIKE '%addr@yahoo.com%')
  AND history !='None'

answered Jan 12 '15 at 21:06

potashin

43,297
11
81
105

Thank you very much! Why does The parentheses help? – user1592380 Jan 12 '15 at 21:23
@user61629 : You are welcome.`AND` has a precedence over `OR` as a logical operator, parenthesis helps to avoid cases like you did have, when at first was evaluated `AND` and only then `OR`, which wasn't what you've wanted to achieve in the first place. – potashin Jan 12 '15 at 21:26

score 2 · Accepted Answer · answered Jan 12 '15 at 21:06

2

Organize your OR criteria in ()

SELECT *
FROM mytable
WHERE (all_emails LIKE '%addr@yahoo.com%'
OR all_phones LIKE '%addr@yahoo.com%')
AND history !='None

Sample Demo

answered Jan 12 '15 at 21:06

M Khalid Junaid

62,293
9
87
115

Thank you very much! Why does The parentheses help? – user1592380 Jan 12 '15 at 21:23
1

@user61629 its regarding operator precedence for full overview checkout this question and answer http://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or – M Khalid Junaid Jan 12 '15 at 21:26

score 0 · Answer 3 · answered Jan 12 '15 at 21:08

0

SELECT *
FROM mytable
WHERE (all_emails LIKE '%addr@yahoo.com%'
OR all_phones LIKE '%addr@yahoo.com%')
AND history NOT IN(
SELECT history FROM mytable WHERE history LIKE 'None')

answered Jan 12 '15 at 21:08

Ali Mohammadi

1,294
1
13
28

Test Again (I Changed)! – Ali Mohammadi Jan 12 '15 at 21:12
1

You still don't need the subquery. – Michael McGriff Jan 12 '15 at 21:13

Mysql not equal operator failing?

3 Answers3