I am wondering how or/and works?
For example if I want to get all rows where display = 1
I can just do WHERE tablename.display = 1
and if I want all rows where display = 1 or 2
I can just do WHERE tablename.display = 1 or tablename.display = 2
But what if I want to get all rows where display = 1 or 2 and where any of the content, tags, or title contains hello world
How would the logic play out for that?
Select * from tablename
where display = 1 or display = 2 and content like "%hello world%" or tags like "%hello world%" or title = "%hello world%"
Would be my guess. but then I can read that in several ways.
Does it read out as:
(display = 1 or display = 2) and (content like "%hello world%" or tags like "%hello world%" or title = "%hello world%")
or as
((display = 1 or display = 2) and (content like "%hello world%")) or (tags like "%hello world%" or title = "%hello world%")
etc.
You need to use brackets for your multiple
OR
conditions. And fordisplay = 1 OR display = 2
you can usedisplay IN(1,2)
. Try this:For more info look at MySQL: Operator Precedence
Run this query:
If it comes out as
1
, then that means the precedence is:if it comes out
0
, then the precedence is:Spoiler: it comes out
1
That is to say,
AND
s are evaluated beforeOR
s, or as I like to say, ANDs are stickier.The MySQL documentation has a good page with information on which operators take precedence.
From that page,
So your original query
would be interpreted as
When in doubt, use parenthesis to make your intent clear. While the information on the MySQL page is helpful, it may not be immediately obvious if the query is ever revisited.
You might consider something like the following. Note that I've changed the
title = "%hello world%"
totitle like "%hello world%"
, since that fits better with the goal you've described.in all SQL servers,
AND
takes precedence overOR
, so just remember to put brackets around yourOR
s:btw
(display = 1 or display = 2)
is equivalent todisplay in (1, 2)
.