Contoh Penggunaan LEFT JOIN dan COALESCE

output.txt

mysql> create table tbl_inti as
    -> select '001' nik, 'Aria' nama union all
    -> select '002' nik, 'Budi' nama union all
    -> select '003' nik, 'Cici' nama union all
    -> select '004' nik, 'Dina' nama union all
    -> select '005' nik, 'Elia' nama union all
    -> select '006' nik, 'Fandi' nama union all
    -> select '007' nik, 'Gina' nama union all
    -> select '008' nik, 'Hari' nama union all
    -> select '009' nik, 'Icha' nama union all
    -> select '010' nik, 'Joni' nama union all
    -> select '011' nik, 'Kiki' nama union all
    -> select '012' nik, 'Lela' nama union all
    -> select '013' nik, 'Mira' nama union all
    -> select '014' nik, 'Nanda' nama union all
    -> select '015' nik, 'Ovi' nama ;
Query OK, 15 rows affected (0.52 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> create table tbl_ortu as
    -> select '001' nik, '006' ayah, '003' ibu union all
    -> select '002' nik, 'Andi' ayah, '005' ibu union all
    -> select '007' nik, 'Salsa' ayah, 'Yudi' ibu union all
    -> select '009' nik, '010' ayah, '012' ibu ;
Query OK, 4 rows affected (0.83 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from tbl_inti;
+-----+-------+
| nik | nama  |
+-----+-------+
| 001 | Aria  |
| 002 | Budi  |
| 003 | Cici  |
| 004 | Dina  |
| 005 | Elia  |
| 006 | Fandi |
| 007 | Gina  |
| 008 | Hari  |
| 009 | Icha  |
| 010 | Joni  |
| 011 | Kiki  |
| 012 | Lela  |
| 013 | Mira  |
| 014 | Nanda |
| 015 | Ovi   |
+-----+-------+
15 rows in set (0.00 sec)

mysql> select * from tbl_ortu;
+-----+-------+------+
| nik | ayah  | ibu  |
+-----+-------+------+
| 001 | 006   | 003  |
| 002 | Andi  | 005  |
| 007 | Salsa | Yudi |
| 009 | 010   | 012  |
+-----+-------+------+
4 rows in set (0.00 sec)

mysql> select inti.nik, inti.nama
    -> , coalesce(ayah.nama,ortu.ayah) ayah
    -> , coalesce(ibu.nama,ortu.ibu) ibu
    -> from tbl_ortu ortu
    -> left join tbl_inti inti on ortu.nik=inti.nik
    -> left join tbl_inti ayah on ortu.ayah=ayah.nik
    -> left join tbl_inti ibu on ortu.ibu=ibu.nik
    -> order by inti.nik ;
+------+------+-------+------+
| nik  | nama | ayah  | ibu  |
+------+------+-------+------+
| 001  | Aria | Fandi | Cici |
| 002  | Budi | Andi  | Elia |
| 007  | Gina | Salsa | Yudi |
| 009  | Icha | Joni  | Lela |
+------+------+-------+------+
4 rows in set (0.00 sec)