Clash Royale CLAN TAG#URR8PPP

Why query shows result, however most of parameter values are wrong?

I would like to ask very humbly that my two queries are showing different results. In both queries I have pass two wrong values (city id and state it), but still query shows return a row when I am executing. Following is the query which shows row and I am expecting it should not

select * from `companies` where `city_id` = 3 and `state_id` = 4 and
`company_name` LIKE '%en%'
or `company_email` LIKE '%en%'
or `company_profile` LIKE '%en%'
order by `company_name` asc


And the following query does not show any row, and it is expected result because of passing wrong values

select * from `companies` where `city_id` = 3 and `state_id` = 4 and
`company_name` LIKE '%en%'
or `company_email` LIKE '%en%'
order by `company_name` asc


The only difference in both of queries is like clause

or `company_email` LIKE '%en%'


Can someone kindly guide me that why it is happening. I would like to appreciate. I am so sorry if I made any mistake.

companies

city_id

state_id

company_name

company_email

company_profile

company_name

2 Answers
2

The way you mix and and or suggests you aren't fully aware of their precedence. AND has higher precedence than OR thus your queries are equivalent to these:

and

or

AND

OR

where (
`city_id` = 3
and `state_id` = 4
and `company_name` LIKE '%en%'
)
or `company_email` LIKE '%en%'
or `company_profile` LIKE '%en%'


where (
`city_id` = 3
and `state_id` = 4
and `company_name` LIKE '%en%'
)
or `company_email` LIKE '%en%'


I presume your only match is due to company_profile.

company_profile

Let me rephrase myself. As it was pointed out AND have a bigger precedence to OR, but, if the AND conditions did not return results, and any of the OR condition return, the OR will take over everything else you wrote.

As of, if the AND conditions you set return results, the OR conditions will be ignored.

For it to work like it (apparently) should, you would need to do this:

select
*
from
`companies`
where
`city_id` = 3
and
`state_id` = 4
and (
`company_name` LIKE '%en%'
or `company_email` LIKE '%en%'
or `company_profile` LIKE '%en%'
)
order by `company_name` asc


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.