Query Hierarki Struktur Organisasi dengan SQL

Query hierarki struktur organisasi di SQL nelusurin kolom id_atasan dari satu karyawan sampai ke pucuk pimpinan, pakai recursive CTE. Satu pola query buat jawab pertanyaan HR yang JOIN biasa nggak sanggup.

Struktur organisasi biasanya kesimpan sederhana: satu tabel karyawan, tiap baris nyimpan id dirinya dan id atasannya. Kelihatan simpel, tapi begitu bos nanya "total anak buah si Budi berapa, termasuk anak buah dari anak buahnya?", satu JOIN nggak cukup.

Yang bikin susah, kedalaman tiap orang beda. Ada staf 3 tingkat di bawah direktur, ada yang 5 tingkat. Recursive CTE ngeberesin ini tanpa kamu perlu tau ada berapa lapisan.

Gimana struktur data organisasi disimpan di SQL?

Struktur organisasi paling sering disimpan pakai pola adjacency list: satu tabel, tiap baris nyimpan id karyawan dan id atasan langsungnya. Atasan tertinggi punya id_atasan NULL. Model ini hemat tempat dan gampang diupdate, tapi butuh recursive CTE buat dibaca sebagai pohon.

Contoh tabel karyawan:

id_karyawan	nama	jabatan	id_atasan
1	Rani	Direktur	NULL
2	Bagas	Manajer Ops	1
3	Sinta	Manajer Sales	1
4	Dimas	Supervisor Sales	3
5	Nia	Staf Sales	4

Nia ada 4 tingkat di bawah Rani, tapi kamu nggak bisa tau itu dari satu baris. Hubungannya kesimpan lewat rantai id_atasan yang harus ditelusuri.

Gimana cara tandai level tiap karyawan?

Mulai dari puncak (id_atasan NULL) sebagai level 1, lalu tambah 1 tiap turun satu tingkat. Recursive CTE bawa angka level ini turun ke tiap baris baru. Hasilnya, kamu tau persis siapa di lapisan berapa tanpa nebak.

WITH RECURSIVE bagan AS (
    SELECT id_karyawan, nama, jabatan, id_atasan, 1 AS level
    FROM karyawan
    WHERE id_atasan IS NULL

    UNION ALL

    SELECT k.id_karyawan, k.nama, k.jabatan, k.id_atasan, b.level + 1
    FROM karyawan k
    JOIN bagan b ON k.id_atasan = b.id_karyawan
    WHERE b.level < 20
)
SELECT level, nama, jabatan
FROM bagan
ORDER BY level, nama;

WHERE b.level < 20 itu pengaman. Kalau ada data ngaco yang bikin lingkaran, query berhenti di level 20, bukan muter selamanya. Kalau konsep CTE masih baru, mampir dulu ke definisi CTE di glossary.

Gimana cara narik rantai jabatan sampai pimpinan puncak?

Buat pertanyaan "Nia lapor ke siapa aja sampai atas?", balik arah telusurnya. Anchor-nya orang yang ditanya, lalu tiap putaran naik ke atasannya. Ini berguna buat approval flow dan audit tanggung jawab.

WITH RECURSIVE rantai AS (
    SELECT id_karyawan, nama, jabatan, id_atasan, 0 AS naik
    FROM karyawan
    WHERE id_karyawan = 5   -- Nia

    UNION ALL

    SELECT k.id_karyawan, k.nama, k.jabatan, k.id_atasan, r.naik + 1
    FROM karyawan k
    JOIN rantai r ON k.id_karyawan = r.id_atasan
)
SELECT naik, nama, jabatan
FROM rantai
ORDER BY naik;

Hasilnya rantai dari bawah ke atas: Nia (0), Dimas (1), Sinta (2), Rani (3). Kolom naik ngasih tau berapa tingkat jarak tiap atasan dari Nia.

Gimana cara hitung total bawahan tiap manajer?

Ini pertanyaan yang paling sering bikin analis mumet. "Total bawahan" bisa berarti dua hal: bawahan langsung saja, atau termasuk bawahan tidak langsung. Recursive CTE ngasih dua-duanya.

Buat total termasuk tidak langsung, telusurin semua turunan tiap manajer, lalu hitung pakai COUNT:

WITH RECURSIVE bawahan AS (
    SELECT id_karyawan, id_atasan, id_karyawan AS id_manajer
    FROM karyawan
    WHERE id_atasan IS NOT NULL

    UNION ALL

    SELECT k.id_karyawan, k.id_atasan, b.id_manajer
    FROM karyawan k
    JOIN bawahan b ON k.id_atasan = b.id_karyawan
)
SELECT m.nama AS manajer, COUNT(*) AS total_bawahan
FROM bawahan b
JOIN karyawan m ON m.id_karyawan = b.id_manajer
GROUP BY m.nama
ORDER BY total_bawahan DESC;

Buat bawahan langsung doang, kamu nggak butuh recursive sama sekali. Cukup self join atau GROUP BY dari id_atasan:

SELECT id_atasan, COUNT(*) AS bawahan_langsung
FROM karyawan
WHERE id_atasan IS NOT NULL
GROUP BY id_atasan;

Angka bawahan langsung inilah yang disebut span of control.

Contoh kasus: audit struktur di UMKM Toko Berkah

Toko Berkah dari dataset latihan ngulikdata udah berkembang jadi 3 cabang dengan 58 karyawan tercatat. Waktu pemiliknya mau nambah gudang, dia butuh tau struktur pengawasannya sehat atau nggak.

Dari query span of control, ketahuan satu supervisor cabang Bekasi megang 14 bawahan langsung, sementara rata-rata supervisor lain cuma 5. Span 14 itu hampir 3 kali lipat rata-rata, dan cabang Bekasi memang paling sering telat setor laporan stok.

Query level juga nunjukin struktur toko punya 5 lapisan, padahal cuma 58 orang. Dari 58 karyawan, 19 di antaranya (33%) punya jabatan yang mengandung kata "koordinator" atau "supervisor" tapi cuma megang 1-2 bawahan. Lapisan manajer yang terlalu tipis kayak gini bikin keputusan kecil harus lewat 4 orang dulu.

Berdasarkan angka ini, pemilik toko ngerampingin jadi 3 lapisan dan gabungin beberapa peran koordinator. Semua kelihatan dari dua query di atas, tanpa buka satu per satu file kepegawaian.

Kesalahan umum saat query hierarki organisasi

Lupa arah telusur. Mau turun (cari bawahan)? Anchor-nya akar, JOIN k.id_atasan = h.id_karyawan. Mau naik (cari atasan)? Anchor-nya orangnya, JOIN dibalik.

Nggak pasang pembatas level. Satu data melingkar bikin query jalan sampai database nyerah. Selalu ada WHERE level < N.

Pakai UNION, bukan UNION ALL. UNION ngecek duplikat tiap putaran dan bikin query lambat tanpa manfaat buat hierarki.

Kolom id_atasan nggak diindex. Kolom ini dipakai di JOIN tiap putaran. Tanpa index, di tabel karyawan besar query-nya bisa berkali lipat lebih lambat. Buat detail perilaku recursive query, cek dokumentasi WITH di PostgreSQL.

FAQ

Kenapa struktur organisasi susah di-query pakai JOIN biasa?

JOIN biasa cuma bisa gabungin sejumlah tingkat yang kamu tulis manual. Struktur organisasi kedalamannya beda-beda: ada staf 3 level di bawah direktur, ada yang 5 level. Kalau kamu JOIN 3 kali, karyawan di level 4 ke bawah kelewat. Recursive CTE nelusurin sampai habis tanpa kamu perlu tau ada berapa tingkat, jadi nggak ada baris yang bocor.

Gimana cara hitung total bawahan termasuk yang tidak langsung?

Pakai recursive CTE yang mulai dari satu manajer, lalu turun ke semua bawahannya sampai level terbawah. Setelah semua bawahan kekumpul, bungkus dengan COUNT. Ini ngitung anak buah langsung plus anak buah dari anak buah. Bedanya sama COUNT dari kolom id_atasan biasa yang cuma dapet bawahan langsung satu tingkat.

Apa itu span of control dalam analisa organisasi?

Span of control adalah jumlah bawahan langsung yang dipegang satu manajer. Angka ini kepakai buat lihat beban koordinasi. Span terlalu lebar, misalnya satu manajer megang 15 orang, sering bikin pengawasan tipis. Span terlalu sempit bikin struktur kebanyakan lapisan. Di SQL, span of control dihitung dari COUNT bawahan langsung per id_atasan.

Database apa saja yang mendukung recursive CTE untuk hierarki?

PostgreSQL, MySQL 8.0 ke atas, SQL Server, SQLite, dan BigQuery semua dukung. SQL Server nggak butuh keyword RECURSIVE, cukup WITH. MySQL di bawah 8.0 nggak bisa sama sekali, jadi kamu perlu nyimpan kolom jalur atau ngulang JOIN manual. Cek versi database kamu dulu.

Gimana cara mencegah query hierarki jalan tanpa henti?

Tambahin pembatas level di recursive member, misalnya WHERE level kurang dari 20. Ini pengaman kalau ada data ngaco, misalnya A atasannya B dan B atasannya A. Tanpa pembatas, query bakal muter selamanya. PostgreSQL nggak punya batas otomatis, sedangkan MySQL punya cte_max_recursion_depth dengan default 1000.

Latihan sekarang

• Simpan struktur organisasi sebagai adjacency list: id karyawan plus id atasan langsung.
• Recursive CTE bisa turun (cari bawahan) atau naik (cari rantai atasan), tinggal balik arah JOIN-nya.
• Span of control cukup pakai GROUP BY id_atasan, sedangkan total bawahan tidak langsung butuh recursive CTE.

Kalau kamu masih ngulik pola recursive-nya, baca dulu panduan recursive CTE lengkap yang bahas anchor dan recursive member step by step. Habis itu, coba tulis query span of control buat data tim kamu sendiri di NgulikSQL.