Skip to main content
5 of 6
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/

There is no natural order in a table of an RDBMS. Tables are sets that have no logical order. So your question is logical nonsense:

is it possible to bind columns "as they are"?

.. because "as they are" does not make sense in the context of tables of an RDBMS.

Also, joining without condition is a CROSS JOIN in SQL, which produces a Cartesian product.


That said, rows have to be stored in some physical order. It's an implementation detail that cannot be relied upon and can change any time without warning with any write operation in the background. But it's there and you can even see it by looking at the system column ctid in Postgres.
If you want to capture the current physical order of rows, this normally works:

SELECT *, row_number() OVER () AS rn FROM t1

I.e.: a window function with an empty OVER clause, which normally reads rows sequentially in their physical order in simple queries.
You could do that for each table and join on the computed rn column. But since you already have an id column that serves the same purpose, this would be completely pointless.


If all involved tables are guaranteed to have exactly the same set of id values, you might as well just [INNER] JOIN:

SELECT id
     , t1.value AS t1_val
     , t2.value AS t2_val
     , ...
FROM   t1
JOIN   t2 USING (id)
JOIN   t3 USING (id)
...

If any of the tables can have missing or duplicate id values, you need to adapt accordingly, depending on what you want to achieve.

For instance, if id is unique in each table, but some id values might not be present in every table, the best query would be with FULL [OUTER] JOIN to preserve all rows:

SELECT id
     , t1.value AS t1_val
     , t2.value AS t2_val
     , ...
FROM   t1
FULL   JOIN t2 USING (id)
FULL   JOIN t3 USING (id)
...

Aside: you might be interested in the related concept of unnesting arrays in parallel:

Erwin Brandstetter
  • 182.2k
  • 28
  • 457
  • 620