1

Just to give a quick DDL to make my question easier to understand:

------------------ creating tables ------------------
create table if not exists users(
    user_id bigserial primary key,
    first_name text,
    last_name text,
    user_name text
);
create table if not exists managers(
    manager_id bigserial primary key,
    user_id bigint references users(user_id) not null,
    permissions text[]
);
create table if not exists transactions(
    transaction_id bigserial primary key,
    user_id bigint references users(user_id) not null,
    amount numeric,
    is_approved boolean,
    manager_id bigint references managers(manager_id) null
);
------------------ inserting data ------------------
insert into users(first_name, last_name, user_name)
values ('first_name','last_name','ohyea'),
  ('manager','manager','manager');

insert into managers(user_id)
values (2);
insert into transactions(user_id, amount, is_approved, manager_id)
values (1,10,true,null),
  (1,1000,true,1);

Now I am basically going to get manager's name using this way:

select 
  t.transaction_id,
  t.user_id,
  u.user_name,
  t.amount,
  t.is_approved,
  mu.user_name as manager_name
from transactions t
    join users u on u.user_id = t.user_id
    left join managers m
        join users mu on mu.user_id = m.user_id
    on m.manager_id = t.manager_id;

The part that I am interested in is this:

    left join managers m
        join users mu on mu.user_id = m.user_id
    on m.manager_id = t.manager_id

What is this to PostgreSQL? A suquery? Like how does it interpret this and what's the difference between doing something like this, and this:

left join (
    select m.manager_id, u.user_name
    from managers m
        join users u on u.user_id = m.user_id
) mu on mu.manager_id = t.manager_id

EDIT:

After a lot of testing, turns out both approaches are absolutely, 100%, 1:1 identical. Same execution plan, same execution times (with small millisecond variations), the only benefit you gain by using the first approach is just cleaner code and easier to follow in bigger functions with a lot of subquery joins.

  • 2
    Does this answer your question? What does the position of the ON clause actually mean? In your model nothing prevents a user from having multiple managers, or none, which sounds a bit strange. You might want to rethink it. – mustaccio Apr 14 at 23:28
  • Not really, as I am not wondering how or why this position matters or find it confusing. I just want to understand how PostgreSQL interprets this when it runs it compared to the other query I wrote at the bottom. The top answer there says this is "simple recursion" (in SQL Server) although I am not sure how a join to another table in this case can cause recursion as this isn't self-join or recursive cte. – Chessbrain Apr 14 at 23:37
  • Also this example I wrote is just dummy schema and data, it's not meant to he realistic or anything just a way to explain my question better. @mustaccio – Chessbrain Apr 14 at 23:42
  • An easy way to see how the DBMS interprets the query is to look at its execution plan, which would show you very likely that your two examples are identical. Also, by supplying "dummy schema and data" you risk misleading answerers; you probably don't know what information is relevant and what isn't, otherwise you wouldn't be asking the question. – mustaccio Apr 15 at 1:45
  • You're looking way into this... I've just used this for quite some time and the question just popped into my head wanting to understand how it actually works. It is not related to anything relevant at all, it's a simple question. But I'll try and see what explain analyze will give me, thanks :) – Chessbrain Apr 15 at 6:33
2

The documentation tells you:

A joined table is a table derived from two other (real or derived) tables according to the rules of the particular join type. Inner, outer, and cross-joins are available. The general syntax of a joined table is

T1 join_type T2 [ join_condition ]

Joins of all types can be chained together, or nested: either or both T1 and T2 can be joined tables. Parentheses can be used around JOIN clauses to control the join order. In the absence of parentheses, JOIN clauses nest left-to-right.

The only way to parse the expression

a JOIN b JOIN c ON cond2 ON cond1

that is in line with the above syntax is

a JOIN (b JOIN c ON cond2) ON cond1

so if your code doesn't throw a syntax error, that's how it must be interpreted.

| improve this answer | |
  • I also found out that both approaches with normal left join subquery and this code are identical, basically 0 difference in execution time and query plans. Thanks for the answer. – Chessbrain Apr 16 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.