PostgreSQL - Simple column binding

Question

I have many tables of the same length (number of rows) that were previously ordered by some columns as they were being written on disk.

My SELECT is of the form:

SELECT t1.id, t1.value, t2.value, t3.value, ...
FROM t1 LEFT JOIN t2 ON t1.id=t2.id
        LEFT JOIN t3 ON t1.id=t3.id
        ...

I know by how these tables were populated that this result corresponds exactly to simple column binding, so that ON t1.id=t2.id and ON t1.id=t3.id can be omitted. The problem is, as far as I know, RDBMSs can operate only projections and row binding. Column binding isn't a typical operation.

So the question is the following: is it possible to bind columns "as they are" without specifying any join criteria? For example, the following query, which is syntactically wrong, should explain what I mean:

SELECT t1.id, t1.value, t2.value, t3.value, ...
FROM t1 COLUMN_BIND t2 COLUMN_BIND t3 ...

I'm using PostgreSQL 9.4.5.

Data Example

Input tables:

t1.id | t1.value
----------------
    1 |       a
    2 |       a
    3 |       b
    4 |       a
    5 |       c
    6 |       a

t2.id | t2.value
----------------
    1 |       g
    2 |       g
    3 |       h
    4 |       g
    5 |       o
    6 |       l

t3.id | t3.value
----------------
    1 |       e
    2 |       e
    3 |       e
    4 |       e
    5 |       q
    6 |       e

Expected result:

t1.id | t1.value | t2.value | t3.value
--------------------------------------
    1 |       a  |        g |       e
    2 |       a  |        g |       e
    3 |       b  |        h |       e
    4 |       a  |        g |       e
    5 |       c  |        o |       q
    6 |       a  |        l |       e

Answer 1

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:

• Unnest multiple arrays in parallel

Answer 2

To me this looks like a quite typical join:

select t1.id,t1.value,t2.value,t3.value
from t1,t2,t3
where t1.id=t2.id and t1.id=t3.id

Answer 3

Not sure I understand what you are asking, but NATURAL JOIN might be what you are looking for:

select t1.id, t1.value, t2.value, t3.value
from t1
natural join t2
natural join t3

The name of the attributes is used to determine the join condition. Personally I consider natural join dangerous because certain attributes may be named the same without having anything to do with each other.

Instead of NATURAL JOIN I would prefer a REFERENTIAL JOIN that used foreign keys to determine the relationship between tables. As far as I know, this join type is not mentioned in any standard document, and I don't think it is implemented in any DBMS.