Why does FROM multiple tables default to cartesian product? What was the idea behind doing a cartesian product (cross join) when i did a query like -
SELECT * FROM agents, orders

I would think that they would concatenate (like pandas). It feels more natural to add tables instead of multiplying them.
Just curious and did not find on the internet the rationale behind defaulting to cartesian product. I am assuming that syntax FROM table1, table2 according to SQL would probably be more correct with cross join but why?
The syntax of comma-style joins (SQL-89) versus using the JOIN keyword (SQL-92) is not the point. You should use the more modern syntax, but it doesn't address the question of why the default would be a Cartesian product if you do not specify a condition.
The answer to that is that it makes relational algebra work.
A Cartesian product is the set of all combinations of elements of two sets. Each element of the first set is paired with every element of the second set.
A relation is a subset of a Cartesian product. It's a Cartesian product, plus a condition which tests if any given pair of elements belongs to the relation.
But the default condition is a fixed true value, so every pairing passes the test. So the default relation ends up being the Cartesian product.
The alternative, I suppose would have been to make the default condition a fixed false value, so the default relation would be an empty relation.
That would have made some scenarios easier, for example if you run a DELETE but accidentally forget the WHERE clause, it would save you from deleting your whole table.
But then we would be getting questions on Stack Overflow from different people who ask why the default join is an empty set, because that makes their heart skip a beat when they forget their WHERE clause for a SELECT query and it appears that their database is empty.