sql server – Why does FROM multiple tables default to cartesian product?

I’m not familiar with how concatenate works in Pandas, but I think CROSS JOIN is the only clause that makes sense with nothing else specified in this case.

You certainly couldn’t vertically concatenate (UNION in SQL) the tables because they could vary in columns (by number of columns and their data types) and how would one horizontally concatenate them, i.e. on what conditions could you align the rows from each table to relate them together as a single row when nothing is specified? I think the simplest answer in the context of relational logic is CROSS JOIN.

Furthermore concatenate in Pandas seems to be meant to operate on a different type of objects than a relational database. While yes technically those objects can be considered a set of values, the criteria that describes them and the constraints those values live by are not the same as a relational table of records with columns that observe different data types, and are potentially bound by database constraints, etc.

To achieve a similar looking outcome of what the single concatenate operation does in SQL, you would need to apply a series of operations potentially including PIVOT, UNION, and CAST, for example.