自己参照テーブルと階層

階層データは、組織図、カテゴリツリー、ファイルシステムなど、現実世界のさまざまな場面で一般的に見られます。SQLでは、自己参照テーブル（同じテーブル内の外部キーを持つテーブル）を使用して、これらの階層構造を表現できます。この手法により、従業員が他の従業員に報告するなどの複雑な関係を、データベース構造内で直接モデル化できます。

-- Create an 'employees' table with a self-referencing 'manager_id' column
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL,
    department_id INTEGER REFERENCES departments(department_id),
    manager_id INTEGER REFERENCES employees(employee_id),
    employee_type VARCHAR(20) NOT NULL CHECK (employee_type IN ('FullTime', 'PartTime'))
);

manager_idテーブルのemployees列は、同じテーブル内のemployee_idを参照する外部キーです。この構成により、各従業員がマネージャー（同じく従業員）を持ち、マネージャーもさらに上位のマネージャーを持つといった、ツリー状の階層構造を構築できます。マネージャーがいない従業員（部門長など）の場合、manager_idはNULLとなり、階層の最上位を示します。

-- Retrieve all direct subordinates of a given manager (e.g., Alice, employee_id = 1)
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.manager_id,
    m.first_name AS manager_first_name,
    m.last_name AS manager_last_name
FROM
    employees e
    LEFT JOIN employees m ON e.manager_id = m.employee_id
WHERE
    e.manager_id = 1;

テーブルを自身と結合することで、特定のマネージャーに直属する従業員を検索するなど、階層的な関係を簡単にクエリできます。このパターンは、リレーショナルデータベース内で組織構造を表現・検索する際に強力です。