-1

I wanted to know if the row order returned by a query mattered? I'm not using a SQL service yet, just working with plain tables and Excel. For example if I do a left join on two tables, my take is that all the rows from the left or first table to be mentioned will be the first ones in my resulting table, whether there are coincidences on the right one or not. But a classmate ordered the results so he placed the rows with coincidences first and the ones without, with null values, at the end.

philipxy
  • 14,867
  • 6
  • 39
  • 83
TheLegend27
  • 3
  • 2
  • 3
    The implementation may result in a certain order, but unless there's an `order by` the row order cannot be relied upon. – Schwern Oct 06 '20 at 03:57
  • The row order matters if it matters...in other words, that is up to you or the specific requirements for the desired output to determine if the order matters. – Jim Oct 06 '20 at 03:59
  • 2
    If the ORDER BY clause is missing in a SELECT statement then the data is fetched in random order on each run for non indexed tables, but if there is a clustered index on a table then data will be returned in ASC order of that column in each run. – Singh Oct 06 '20 at 04:21
  • Honestly, a DBMS stores data wherever it find some free space. And it retrieves data wherever it finds a match. So, there is no gurantee on the order of returned data. if you do select dbms just sends data as it gets from engine. If you add `order by` dbms fetched the data first then order it before sending it to you. – Koushik Roy Oct 06 '20 at 04:47
  • 1
    There is no such thing as a "row order", unless you include an `order by` in your query –  Oct 06 '20 at 06:01
  • This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. PS This isn't very clear. What does "matters" mean? PS SQL is not arelational algebra. (And there are many of those.) And other products act per their documentation. – philipxy Oct 06 '20 at 07:23
  • Does this answer your question? [Default row ordering for select query in oracle](https://stackoverflow.com/questions/899514/default-row-ordering-for-select-query-in-oracle) – philipxy Oct 06 '20 at 07:24

1 Answers1

0

SQL tables represent unordered sets. SQL results sets are unordered unless you explicitly have an ORDER BY for the outermost SELECT.

This is always true and is a fundamental part of the language. Your class should have covered this on day 1.

The results from a query without an ORDER BY may look like they are in a particular order. However, you should not depend on that -- or, you depend on that at your peril. The rule is simple: without an ORDER BY, you do not know the ordering of the result set.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786