1

I'm not interested in "Join" concept yet. I'm trying to figure what really happens underneath when using:

select * from table1, table2.

I've built two tables, numbers and letters:

Output of selecting numbers and letters rows

When using select * from numbers, letters, the output is:

Select every row from both tables, numbers first

I jumped to the conclusion that SQL matches every row from first table to the second table, by pointing some kind of iterator 1 from last row to first row on the first table, then first row to last row on second table 1, resetting 1s position back to the end of first table each time 2 reaches a new position.

The same happens when inverting letters first, number last:

Select every row from both tables, letters first

However I believe I'm wrong, as when I inserted more values to numbers, the follow happened: enter image description here

In that try, even though the query selects numbers, letters, numbers got "fixed" and letters got "reverse iterated", not the other way around.

Being honest, what I was really expecting first place when selecting numbers, letters was something like:

1 a
1 b 
1 c
2 a
2 b
2 c
3 a
3 b 
3 c

What really happens underneath when selecting multiple values? I found many answers concerning how to use joins to achieve x result, but not so much content talking about what SQL is generating with the original query first place.

Thanks a lot in advance!

nluizsoliveira
  • 355
  • 1
  • 9
  • "The order could even change between two identical queries just because of data that has changed between those queries. a "where" clause may be satisfied with an index scan in one query, but later inserts could make that condition less selective, and the planner could decide to perform a subsequent query using a table scan." – nluizsoliveira Jan 06 '22 at 13:57
  • 1
    That indeed does! I was not aware of the existence of that command, either that the order of the output of a query was not reliable. Thank you very much @Renat! – nluizsoliveira Jan 06 '22 at 13:57

0 Answers0