CODEWITHSUNDEEP
sqlpostgresql
Kostia R
Kostia R

Reputation: 2565

Understanding the semantics of subquery in FROM clause in PostgreSQL

I have an rttmm table with conversation_id and duration fields. There's a query using two sub-queries in a FROM-clause, one of them is not used. I would expect it to be semantically equivalent to the one where you would remove the unused subquery, but it behaves very differently. Here's the query in question:

select
  sum(subq2.dur) as res
    from (
      select sum(rttmm.duration) as dur, rttmm.conversation_id as conv_id
      from rttmm
      group by rttmm.conversation_id
      ) as subq1,
    (
      select sum(rttmm.duration) as dur, rttmm.conversation_id as conv_id
      from rttmm
      group by rttmm.conversation_id
    ) as subq2

and here's what I would expect it to be equivalent to (just removing the subq1):

select
  sum(subq2.dur) as res
    from 
    (
      select sum(rttmm.duration) as dur, rttmm.conversation_id as conv_id
      from rttmm
      group by rttmm.conversation_id
    ) as subq2

Turns out it's not the same at all. What is the proper understanding of the first query here?

Upvotes: 0

Views: 52

Answers (1)

Pavel Smirnov
Pavel Smirnov

Reputation: 4799

The first query uses the ancient SQL-89 join syntax and cross-joins two subqueries, whereas the second query does a simple select from the first subquery.

In simple words, the difference is:

select * from table1, table2 vs select * from table1

which is equivalent for

select * from table1 cross join table2 vs select * from table1

Upvotes: 3

Related Questions