A nuanced SQL question

Per SQL Cookbook and practice, the follow code produces the following Cartesian product result:
Table "emp" contains an ename="KING" and Table T10 has been filled with increasing numerics 1..10.

SELECT ename, iter.pos
FROM (SELECT ename FROM emp WHERE ename='KING") e
, (SELECT id AS pos FROM T10) iter ;

Open in new window


ENAME   POS
----------
KING    1
KING    2
...
KING    10

Open in new window


WHY does this 'necessarily' come out in numerical order?
I haven't tried it, but wouldn't a different ordering of Table10's IDs produce diff results?

Of course in this subset of a solution, different number ordering doesn't matter, but what I'm after is assurance that they will always be in order.
Looking below you'll see why ordering is important.
My concern is ordering correlated subquery's results doesn't always seem to preserve that ordering upon the final answer.

For those interested, and to understand why I want to know this, the entire solution is below.  The purpose will be obvious.

SELECT substr(e.ename, iter.pos) AS "lopping from front"
      , substr(e.ename, length(e.ename)-iter.pos+1) AS "appending from end"
FROM (SELECT ename from emp where ename = 'KING') e
,    (SELECT id AS pos from t10) iter
WHERE iter.pos <= length(e.ename) ;

Open in new window


Thanks!
RalphAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
None of the databases will guarantee the ordering without an ORDER BY.
If you have a document that says otherwise it's not reliable.

Now, having said that, it is likely on a new table with just the sample data inserted in order,  the results will be as shown, but again, no guarantee of ordering unless you specify an ordering.
0
sdstuberCommented:
I just tried it, I even populated t10 in numerical order

This is on Oracle 12.1.0.2
I have no special parameters set nor did I create any indexes.
Both tables are just plain, vanilla tables with standard integer and varchar2 data type columns.

SELECT ename, iter.pos
FROM (SELECT ename FROM emp WHERE ename='KING') e
, (SELECT id AS pos FROM T10) iter ;



ENAME             POS
---------- ----------
KING                2
KING                1
KING                6
KING                3
KING                4
KING                5
KING                8
KING                9
KING               10
KING                7

Open in new window


and, using your query

SELECT substr(e.ename, iter.pos) AS "lopping from front"
      , substr(e.ename, length(e.ename)-iter.pos+1) AS "appending from end"
FROM (SELECT ename from emp where ename = 'KING') e
,    (SELECT id AS pos from t10) iter
WHERE iter.pos <= length(e.ename) ;


lopping from front appending from end
------------------ ------------------
ING                NG                
KING               G                 
NG                 ING               
G                  KING      

Open in new window



The only thing special I did was I added additional columns to the t10 table.
This ensured the data would be spread over more than one data block.

If it was all in one block, then all of the data would be read in a single io, and because it was inserted in order, it would be read from that one block in order.  Still no guarantee, but unlikely the system would go out of its way to read through the rows of a single block in non-sequential order.

My alteration might seem contrived, but in actuality it more accurately mirrors real data where you can't be sure of the physical arrangement of any particular rows on a live system.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RalphAuthor Commented:
Don't know what happened to my comment.

Thanks sdstuber!
Your solution reads like a professor's, organized and well written.
I like how you avoided a sequence of mucking up the T10 table.
I gather you appended a column after the 1 column approach.  I think I'll try the same approach with PostgreSQL and MySQL.  Might try starting with two cols and inserting into one, just to discover empirically.
Again, thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.