CODEWITHSUNDEEP
mysqlsql
Daniel Viglione
Daniel Viglione

Reputation: 9407

Adding parentheses yielding different results in SQL

I created two SQL statements and the second is giving much less results. The only difference is I added additional parentheses to the second query:

SELECT COUNT(*)
FROM records
JOIN other_records ON records.other_record_id = other_records.id
WHERE other_records.practice = 'Medical' && 
records.details IS NULL || 
(records.details <> 'ERROR' && records.details <> 'ERROR BY PRACTICE');

+----------+
| COUNT(*) |
+----------+
|   342668 |
+----------+
1 row in set (3.42 sec)

SELECT COUNT(*) FROM records JOIN other_records 
ON records.other_record_id = other_records.id 
WHERE other_records.practice = 'Medical' && 
(records.details IS NULL || (records.details <> 'ERROR' && records.details <> 'ERROR BY PRACTICE'));

+----------+
| COUNT(*) |
+----------+
|   193899 |
+----------+
1 row in set (0.46 sec)

As far as I understand, first it will match the result of the JOIN with other_records.practice is Medical. Both should return the same result set at this point. Then as I understand, it will match either where details is null or where details do not match ERROR and ERROR BY PRACTICE. I am not sure why parentheses are making a difference here?

Upvotes: 2

Views: 183

Answers (1)

e_i_pi
e_i_pi

Reputation: 4820

In boolean logic the and operator && takes precedence over the or operator ||.

So, when you write this:

WHERE other_records.practice = 'Medical' && 
records.details IS NULL ||
(records.details <> 'ERROR' && records.details <> 'ERROR BY PRACTICE')

...it is equivalent to this:

WHERE (other_records.practice = 'Medical' && records.details IS NULL)
||
(records.details <> 'ERROR' && records.details <> 'ERROR BY PRACTICE')

...hence the different results to your second statement.

Upvotes: 4

Related Questions