Why does SQL / PostgreSQL allow joins without referencing a column from the joined table in the join-CodePudding

Consider the following case:

create table contract(contract_id bigint, client_id bigint, second_client_id bigint);

insert into contract
values(10, 1, 3),
(11, 2, 4),
(12, 3, 6)
;

create table client(client_id bigint, user_name varchar);

insert into client
values(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E'),
(6, 'F')
;

I can't see a use case for the following style of query, yet Postgres (where I've tested it) and presumably other SQL RDMS services allow the query without throwing an error / warning:

select * 
from contract c
left join client ct1 on ct1.client_id = c.client_id
left join client ct2 on ct1.client_id = c.second_client_id

Note that the second join does not reference a column from the table being joined (i.e. the second client join is aliased as ct2 but there is no ct2 column in the join clause). I can't see why one would want this outcome, but there is no warning or error thrown. And in large tables it can waste massive amounts of time attempting this join and returning null, and only then will the person writing the query become aware of their mistake.

Curious as to any use cases for this type of query and, if there are none, why the error handling has not been built.

Edit: As pointed out in comments, the join is syntactically correct so no error is thrown - the query planner does not check join functionality prior to execution.

CodePudding user response：

Since the join condition is a boolean expression that only references columns from previous tables in the FROM clause, there is nothing wrong with it. There is nothing in SQL that forbids you to write a cross join that way, and indeed it is not unusual to write something like

FROM tab
   LEFT JOIN LATERAL unnest(tab.arr)
      ON TRUE

So forbidding that seems out of the question, apart from the fact that it would violate the SQL standard.

Your wish is not unusual, it is known under the code "do what I mean" (DWIM). I for one loathe systems that think they know what I want better than I do and bother me with their helpful cautions.

CodePudding user response：

An important thing you must understand is the fact that a join is not only a predicate to compare values commings from two table but for any number of tables.

This is the case, in what it is called in the RDBMS llitterature as "triangular join", that involves 3 tables or more...

In your query, the triangular join is real, and can also be write as :

select * 
from contract c
left join client ct1 on ct1.client_id = c.client_id
left join client ct1 on ct1.client_id = c.second_client_id
CROSS JOIN ct2