Menu
Does the order of the results inside a mysql subquery affect the order of the actual query? I tried it but did not came to a real result cause sometimes it seemed so and sometimes it doesn't.
eg:
SELECT name FROM people WHERE pid IN (SELECT mid FROM member ORDER BY mdate)
Is the "order by"-clause going to affect the order of the results in this case?
Thanks.
580
A subquery can contain many of the keywords or clauses that an ordinary SELECT can contain: DISTINCT , GROUP BY , ORDER BY , LIMIT , joins, index hints, UNION constructs, comments, functions, and so on. Beginning with MySQL 8.0. 19, TABLE and VALUES statements can be used in subqueries.
An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY command can be used to perform the same function as the ORDER BY in a subquery. Subqueries that return more than one row can only be used with multiple value operators such as the IN operator.
A "table" (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows. Rows in a table (or in a subquery in the FROM clause) do not come in any specific order. That's why the optimizer can ignore the ORDER BY clause that you have specified.
Answer: D. The sub-query always executes before the execution of the main query. Subqueries are completed first. The result of the subquery is used as input for the outer query.
No it cant and if you want to change the order as per your need then better use a JOIN
Something like this:-
select name
from people p inner join member m on p.pid = m.mid
order by p.name
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
