CODEWITHSUNDEEP
sqlmysql
Hugo Mota
Hugo Mota

Reputation: 11547

does parentheses influence in this mysql results?

i was trying to run this query:

(
    (SELECT * FROM (SELECT * FROM `users` WHERE `access_level` > 0) AS `search_subject` WHERE (BINARY `username` = ?))

    UNION

    (SELECT * FROM (SELECT * FROM `users` WHERE `access_level` > 0) AS `search_subject` WHERE (BINARY `username` = ?))
)
LIMIT 5

but got an error because of the surrounding parenthesis, just before LIMIT 5. obs: the query is all in a row, i endented it here for better reading. is it allowed in sql?

the following works fine:

(SELECT * FROM (SELECT * FROM `users` WHERE `access_level` > 0) AS `search_subject` WHERE (BINARY `username` = ?))

UNION

(SELECT * FROM (SELECT * FROM `users` WHERE `access_level` > 0) AS `search_subject` WHERE (BINARY `username` = ?))

LIMIT 5

my question is: this second version is equivalent to the first one or does the LIMIT in the second version only applies in the second SELECT? if so, what can i do to work around this? thanks (:

ps: don't worry about the interrogation marks. that's just because of the prepared statements

Upvotes: 0

Views: 1296

Answers (2)

Jason McCreary
Jason McCreary

Reputation: 72961

I am assuming these are sample queries, as your SELECT statements are not only redundant but poorly formed.

However, to answer your original question, adding parenthesis creates an expression that changes the order of operations. Just as it would in mathematics. So the result of the expression in your first example does not support the LIMIT clause. However, the second, with the UNION statement, does.

Long story short, the parentheses make it a different statement and invalidates the LIMIT. You don't need them. It's subtle, but that's the problem.

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332521

Actually, you don't need the the brackets:

SELECT * 
  FROM `users` 
 WHERE `access_level` > 0
   AND BINARY `username` = ?
UNION
SELECT * 
 FROM `users` 
WHERE `access_level` > 0
  AND BINARY `username` = ?
LIMIT 5

There's no difference in either side of the UNION to begin with, so there's no need for it.

No, the LIMIT applies to the result of all the UNION'd queries, as does the ORDER BY clause.

Upvotes: 2

Related Questions