How does a sub query in the from clause work?

Question

What is a valid use-case for Sub Query in the FROM clause? How does that scheme work? There are many examples of this type in SO. A link to one of those is here but I can't see how this scheme works.

P.S: If the answer is Oracle specific it is fine.

Answer 1

Here are some use cases for a subquery in the from clause. How it works has been explained in the comments to your question (SQL is mathematical closed thanks to its relational operators).

1. Pivot (SQL Server 2008)

 select P.RUN_ID
      , [2012] = sum(P.[2012])
      , [2013] = sum(P.[2013])
      , [2014] = sum(P.[2014])
      , [2015] = sum(P.[2015])
   from (select T.RUN_ID
              , Y.YEAR
              , T.MEASURE
           from SOME_TABLE T
          inner join
                YEAR Y
                  on T.SOME_ID = Y.SOME_ID
        ) T
  pivot (
          sum(MEASURE)
          for YEAR in ([2012], [2013], [2014], [2015])
        ) P
  group by
        P.RUN_ID
  order by
        P.RUN_ID

2. over clause (Oracle) based on a union

 select S.Text_ID
      , row_number() over (partition by S.Text_ID order by S.Segmentstart) as Segmentnumber
      , S.Segment_ID
      , S.Segmentstart
      , S.Segmentend
      , S.Segmentfragment
   from (select S.Text_ID as Text_ID
              , S.Satz_ID as Segment_ID
              , S.Start as Segmentstart
              , S.End as Segmentend
              , S.Fragment as Segmentfragment
           from Mainclauses S
          union all
         select X.ID as Text_ID
              , null as Segment_ID
              , coalesce(S.End, 0) as Segmentstart
              , lead(S.Start, 1, X.CONTENT_LENGTH) over (partition by X.ID order by S.Start) as Segmentend
              , 'X' as Segmentfragment
           from Texts X
           left join
                Mainclauses S
                  on X.ID = S.Text_ID
          union all
         select X.ID as Text_ID
              , null as Segment_ID
              , 0 as Segmentstart
              , min(S.Start) as Segmentend
              , 'X' as Segmentfragment
           from Texts X
          inner join
                Mainclauses S
                  on X.ID = S.Text_ID
          group by
                X.ID
        ) S

3. over clause (SQL Server 2008) with join and aggregate

 select E.X_ID
      , Z.SomeThing
      , sum(Z.OtherMeasure * E.Measure) as CombinedMeasure
      , Sorting = row_number() over
          ( partition by
                      E.X_ID
                order by
                      Z.SomeThing
          )
   from (select E.X_ID
              , E.Y_ID
              , Measure = sum(E.Measure)
           from SomeTable E
          group by
                E.X_ID
              , E.Y_ID
        ) E
  inner join
        OtherTable Z
           on E.Y_ID     = Z.Y_ID

4. Calculate ratio (SQL Server 2008)

   with SomeData
      ( Main_ID
      , Sub_ID
      , Measure
      )
   as (select Main_ID
            , Sub_ID
            , Measure = sum(Measure)
         from SomeTable P
        group by
              Main_ID
            , Sub_ID
      )
 select Main_ID
      , Sub_ID
      , Ratio = D.Measure / sum(M.Measure) over (partition by M.Main_ID)
   from SomeData D
  inner join
        (select Main_ID
              , Measure = sum(Measure)
           from SomeData
          group by
                Main_ID
         having sum(Measure) != 0
        ) M
           on M.Main_ID = D.Main_ID

5. Partial Comparision of two (or more) tables (SQL Server 2008)

select *
  from (select A, M = sum(M) from S group by A) X
  full outer join
       (select A, M = sum(M) from T group by A) Y
          on X.A = Y.A
 where X.A is null
    or Y.A is null
    or abs(X.M - Y.M) > 0.00000001

Note: These are examples only and in I thought that the subquery in the from clause have been a good solution to achieve the result.