これまた Oracle 独特というわけではないですが。先の「Order by 句に Select 文」で相関サブクエリーが出てきたのでちょっと整理がてら。

　相関サブクエリーとは、サブクエリー(子クエリー)に親クエリーの列名が出てくるタイプのサブクエリーです。

　たとえばよくあるハマりどころとして。
　Group by でグループごとの最大値を得ようとしたとします。

SQL> select JOB, max(SAL)
  2    from EMP
  3   group by JOB;

JOB                  MAX(SAL)
------------------ ----------
CLERK                    1300
SALESMAN                 1600
PRESIDENT                5000
MANAGER                  2975
ANALYST                  3000

　ここまではいいのですが、「じゃあ誰が(どのレコードが)最大なんだ？」とか思って以下のように書いてもエラーです。

SQL> select JOB, max(SAL), ENAME
  2    from EMP
  3   group by JOB;
select JOB, max(SAL), ENAME
                      *
行1でエラーが発生しました。:
ORA-00979: GROUP BYの式ではありません。

　ここで登場するのが相関サブクエリーです。以下のように書くと目的が達成できます。

SQL> select JOB, SAL, ENAME
  2    from EMP a
  3   where SAL = (select max(SAL)
  4                  from EMP b
  5                 where a.JOB = b.JOB);

JOB                       SAL ENAME
------------------ ---------- --------------
SALESMAN                 1600 ALLEN
MANAGER                  2975 JONES
ANALYST                  3000 SCOTT
PRESIDENT                5000 KING
ANALYST                  3000 FORD
CLERK                    1300 MILLER

　サブクエリー側の条件(where 句)に親クエリーの列名が出てきています。こういうのを相関サブクエリーと呼ぶらしいです。
　また、今回の例だと、この where 句が Group by 句と同じ役割を担っています。
　(ANALYST が複数行取得されているのは、同額の人が複数いるからですね)

　ところでこの相関サブクエリー。手続き型言語(C#)で等価コードを書くとこんな感じ。

// 親クエリーのループ(from 句)
foreach (var a in EMP)
{
    // 子クエリーの select の結果 (子クエリーの戻り値)
    int maxSal = int.MinValue;

    // 子クエリーのループ(from 句)
    foreach (var b in EMP)
    {
        // 子クエリーの Where 句
        if (a.JOB == b.JOB) //← a.JOB と同じ内容のものが集計対象になる
        {
            // max集計関数 (子クエリーの select)
            if (maxSal < b.SAL) maxSal = b.SAL;
        }
    }

    //-------------------------------------------

    // 親クエリーの Where 句
    if (maxSal == a.SAL)
    {
        // 親クエリーの select 
        Console.WriteLine("{0},{1},{2}", a.JOB, a.SAL, a.ENAME);
    }
}

　わりと手続き型そんまんまです。スコープも含めて。(上記コードでは null とか無視ですが)
　なので、相関サブクエリーにぶち当たってわからなかったら、手続き型的に読み直して理解してみる、というのも手かもしれません。