Bab 4 Join Tabel & Subkueri

4.1 Pendahuluan

Pada bagian pendahuluan ini kita akan membahas query untuk menampilkan data dari beberapa tabel MySQL. Karena sering kali mengambil data dari database yang disimpan di beberapa tabel untuk diolah sehingga menjadi satu keluaran. Kita menggunakan empat tabel, yaitu: tabel pelanggan, produk, transaksi, dan transaksi_detail. Struktur dan hubungan keempat tabel tersebut tampak seperti pada gambar berikut:

4.2 CREATE Database Baru

Pertama-tama kita buat database baru untuk menyimpan menyimpan tabel seperti dipelihatkan diatas, sebagai berikut:

# import module 
import mysql.connector

# koneksi server
db1 = mysql.connector.connect(
  host='localhost',
  user='bakti',
  passwd='123'
)

# membuat objek 'cursor` sebagai kata kunci eksekusi SQL
cursor1 = db1.cursor()

# Perintah SQL
sql='''CREATE DATABASE IF NOT EXISTS join_tabel'''

# Eksekusi perintah  SQL
cursor1.execute(sql)

Untuk memastikan koneksi sudah berhasil, berikut ini coba check database yang sudah tersimpan di server lokal anda.

# Perintah SQL
sql = 'SHOW DATABASES'

# Eksekusi perintah  SQL
cursor1.execute(sql)

# menampilkan daftar database
for x in cursor1:
  print(x) 

4.2.1 CREATE Tabel

Sintaks berikut ini adalah untuk membangun ke-empat tabel yang kan kita gunakan. Pertama-tama kita buatkan koneksi dan kursor baru sebagai berikut:

# import module 
import mysql.connector

# koneksi server
db2 = mysql.connector.connect(
  host='localhost',
  user='bakti',
  passwd='123',
  database='join_tabel'
)

# membuat objek 'cursor` sebagai kata kunci eksekusi SQL
cursor2 = db2.cursor()

Setelah koneksi dan kursor baru berhasil dibuatkan, berikutnya kita buatkan struktur tabel yang diperlukan;

Struktur Tabel Barang

# Perintah SQL
sql = '''
CREATE TABLE IF NOT EXISTS `barang` (
  `id_barang` int(11) NOT NULL AUTO_INCREMENT,
  `id_kategori` smallint(6) NOT NULL,
  `nama_barang` varchar(255) CHARACTER SET latin1 NOT NULL,
  `harga` int(11) NOT NULL,
  `stok` int(11) NOT NULL,
  PRIMARY KEY (`id_barang`)
); 
'''

# Eksekusi perintah  SQL
cursor2.execute(sql)

# Simpan perubahan
db2.commit()

Struktur Tabel Pelanggan

# Perintah SQL
sql = ''' 
CREATE TABLE IF NOT EXISTS `pelanggan` (
  `id_pelanggan` int(11) NOT NULL AUTO_INCREMENT,
  `nama` varchar(50) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id_pelanggan`) 
); 
'''

# Eksekusi perintah  SQL
cursor2.execute(sql)

# Simpan perubahan
db2.commit()

Struktur Tabel Penjualan

# Perintah SQL
sql = ''' 
CREATE TABLE IF NOT EXISTS `penjualan` (
  `id_transaksi` int(11) NOT NULL AUTO_INCREMENT,
  `id_pelanggan` int(11) DEFAULT NULL,
  `tgl_transaksi` date NOT NULL,
  `total_transaksi` int(11) NOT NULL,
  PRIMARY KEY (`id_transaksi`),
  KEY `FK_penjualan_pelanggan` (`id_pelanggan`),
  CONSTRAINT `FK_penjualan_pelanggan` FOREIGN KEY (`id_pelanggan`) REFERENCES `pelanggan` (`id_pelanggan`) ON DELETE SET NULL ON UPDATE CASCADE
); 
'''

# Eksekusi perintah  SQL
cursor2.execute(sql)

# Simpan perubahan
db2.commit()

Struktur Tabel Detail Penjualan

# Perintah SQL
sql = ''' 
CREATE TABLE IF NOT EXISTS `penjualan_detail` (
  `id_transaksi_detail` int(11) NOT NULL AUTO_INCREMENT,
  `id_transaksi` int(11) DEFAULT NULL,
  `id_barang` int(11) DEFAULT NULL,
  `jml_barang` smallint(6) DEFAULT NULL,
  `harga_satuan` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_transaksi_detail`),
  KEY `FK_tb_penjualan_detail_tb_barang` (`id_barang`),
  KEY `FK_tb_penjualan_detail_tb_penjualan` (`id_transaksi`),
  CONSTRAINT `FK_tb_penjualan_detail_tb_barang` FOREIGN KEY (`id_barang`) REFERENCES `barang` (`id_barang`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_tb_penjualan_detail_tb_penjualan` FOREIGN KEY (`id_transaksi`) REFERENCES `penjualan` (`id_transaksi`) ON DELETE CASCADE ON UPDATE CASCADE
)
'''

# Eksekusi perintah  SQL
cursor2.execute(sql)

# Simpan perubahan
db2.commit()

Catatan: Pengertian Cascade dalam MySql

Keterangan:

Pengaturan Struktur Tabel SQL digunakan untuk menentukan aturan struktur data dalam tabel. Batasan digunakan untuk membatasi jenis data yang dapat masuk ke dalam tabel, memastikan keakuratan dan keandalan data dalam tabel. Batasan dapat berupa tingkat kolom atau tingkat tabel. Batasan tingkat kolom berlaku untuk kolom, dan batasan tingkat tabel berlaku untuk keseluruhan tabel. Berikut Batasan yang biasanya digunakan dalam SQL:

• NOT NULL: Memastikan kolom tidak boleh memiliki nilai NULL
• UNIQUE: Memastikan bahwa semua nilai dalam kolom berbeda
• PRIMARY KEY: Kombinasi NOT NULL dan UNIQUE. Secara unik mengidentifikasi setiap baris dalam tabel
• FOREIGN KEY: Secara unik mengidentifikasi baris/record di tabel lain
• CHECK: Memastikan bahwa semua nilai dalam kolom memenuhi kondisi tertentu
• DEFAULT: Menetapkan nilai default untuk kolom saat tidak ada nilai yang ditentukan
• INDEX: Digunakan untuk membuat dan mengambil data dari database dengan sangat cepat
• AUTO_INCREMENT: memungkinkan inset data unik pada kolom tertentu yang dihasilkan secara otomatis saat data baru dimasukkan ke dalam tabel. Seringkali kolom ini adalah adalah juga PRIMARY KEY dan ingin dibuat secara otomatis setiap kali catatan baru dimasukkan.

4.2.2 INSERT Data Tabel

Berikut ini adalah koding yang digunakan untuk menambahkan data ke dalam tabel;

Insert Data Barang

sql = '''
INSERT INTO `barang` (`id_barang`, `id_kategori`, `nama_barang`, `harga`, `stok`) 
      VALUES (%s, %s, %s, %s, %s)'''
      
values = [(1, 1, 'RAM', 230000, 4),
            (2, 1, 'Mainboard', 1250000, 7),
            (3, 1, 'Mouse', 80000, 6),
            (4, 3, 'Mousepad', 35000, 3),
            (5, 3, 'Keyboard', 80000, 5)
         ]
         
for val in values:
  cursor2.execute(sql, val)
  db2.commit()

Insert Data Pelanggan

sql = '''
INSERT INTO `pelanggan` (`id_pelanggan`, `nama`, `email`)
      VALUES (%s, %s, %s)'''
      
values = [(1, 'Alfa', 'alfa@yahoo.com'),
            (2, 'Beta', 'beta@gmail.com'),
            (3, 'Delta', 'delta@hotmail.com'),
            (4, 'Bakti', 'dsciencelabs@outlook.com')
           ]
         
for val in values:
  cursor2.execute(sql, val)
  db2.commit()

Insert Data Penjualan

sql = '''
INSERT INTO `penjualan` (`id_transaksi`, `id_pelanggan`, `tgl_transaksi`, `total_transaksi`)
      VALUES (%s, %s, %s, %s)'''
      
values = [(1, 1, '2023-06-22', 230000),
            (2, 3, '2023-06-22', 195000),
            (3, 2, '2023-05-01', 1710000),
            (4, 1, '2023-07-04', 310000),
            (5, 3, '2023-07-10', 80000)
           ]
         
for val in values:
  cursor2.execute(sql, val)
  db2.commit()

Insert Data Penjulan Detail

sql = '''
INSERT INTO `penjualan_detail` (`id_transaksi_detail`, `id_transaksi`, `id_barang`, `jml_barang`, `harga_satuan`)
      VALUES (%s, %s, %s, %s, %s); 
      '''
values = [(1, 1, 1, 1, 230000),
            (2, 2, 4, 1, 35000),
            (3, 2, 5, 2, 80000),
            (4, 3, 2, 1, 1250000),
            (5, 3, 1, 2, 230000),
            (6, 4, 4, 2, 35000),
            (7, 4, 5, 1, 80000),
            (8, 4, 3, 2, 80000),
            (9, 5, 3, 1, 80000)
           ]
         
for val in values:
  cursor2.execute(sql, val)
  db2.commit()

4.2.3 READ Tabel

Membaca data tabel SQL dengan Python diperlukan koneksi dan engine yang aktif untuk memuat data yang diperlihatkan ke pengguna. Berikut ini adalah langkah pembentukan koneksi dan enginya.

from sqlalchemy import create_engine
import pymysql
import pandas as pd

# membuat engine
engine = create_engine("mysql+pymysql://{user}:{pw}@localhost/{db}"
                       .format(user="bakti",
                               pw="123",
                               db="join_tabel"))

# engine koneksi ke database
en_co = engine.connect()

Read Tabel Barang

Selanjutnya, untuk membaca tabel sql menjadi DataFrame, tanpa mengeksekusi kueri apa pun, kita dapat menggunakan fungsi read_sql_table() yang ada pada library Pandas.

barang = pd.read_sql('barang', en_co)
barang

Read Tabel Pelanggan

pelanggan = pd.read_sql('pelanggan', en_co)
pelanggan

Read Tabel Penjualan

penjualan = pd.read_sql('penjualan', en_co)
penjualan

Read Tabel Penjulan Detail

detail_penjualan = pd.read_sql('penjualan_detail', en_co)
detail_penjualan

4.3 Join Tabel

SQL JOIN adalah fungsi yang disediakan SQL yang digunakan untuk menggabungkan tabel melalui kolom atau key tertentu yang nilainya saling terkait untuk memperoleh sekumpulan data dengan informasi yang lengkap. Dengan perintah JOIN pengguna dapat menggabungkan beberapa tabel agar mendapat informasi secara lengkap sesuai yang dibutuhkan untuk analisis. Beberapa jenis join tabel ini akan dijelaskan satu persatu pada sub-topik berikut.

4.3.1 INNER JOIN

Pada MySQL, penulisan INNER JOIN dapat dilakukan dengan tiga cara yaitu (1) menggunakan klausa INNER JOIN (2) menggunakan klausa CROSS JOIN (3) cukup menggunakan klausa JOIN saja. Pengguna bebas menggunakan sintaks yang mana saja asal konsisten, penulis lebih prefer menggunakan JOIN saja, karena lebih simpel.

Sebagai contoh kita akan menampilkan data pelanggan yang melakukan pesanan, query yang kita jalankan:

# Perintah SQL
sql = ''' 
SELECT pl.id_pelanggan, nama, tgl_transaksi, total_transaksi
  FROM pelanggan AS pl
    JOIN penjualan AS pn
      ON pl.id_pelanggan = pn.id_pelanggan
'''

# Eksekusi perintah  SQL
pd.read_sql(sql, en_co)

Tabel yang dihasilkan adalah tabel yang berisikan nilai yang sama pada kedua tabel (matching values). Jika menggunakan klausa USING, maka query akan berbentuk seperti berikut:

# Perintah SQL
sql = ''' 
SELECT pl.id_pelanggan, nama, tgl_transaksi, total_transaksi
  FROM pelanggan AS pl
    JOIN penjualan AS pn
      USING(id_pelanggan)
'''

# Eksekusi perintah  SQL
pd.read_sql(sql, en_co)

Kuis:

• Berdasarkan hasil diatas, pada saat kapan sebiaknya anda akan menggunakan join tabel?

4.3.2 LEFT JOIN

Pada LEFT JOIN, semua data pada tabel sebelah kiri akan ditampilkan, sedangkan data pada tabel disebelah kanan hanya akan ditampilkan jika data terkait pada tabel tersebut muncul di tabel sebelah kiri. Contohnya, kita tampilkan semua data pelanggan beserta data transaksinya, jalankan query berikut:

# Perintah SQL
sql = ''' 
SELECT *
  FROM pelanggan AS pl
    LEFT JOIN penjualan AS pn 
      USING(id_pelanggan)
'''

# Eksekusi perintah  SQL
pd.read_sql(sql, en_co)

4.3.3 LEFT JOIN (Partial)

Tabel yang dihasilkan akan mengandung seluruh nilai tabel di sisi kiri, dengan nilai yang sama antara tabel kiri dan kanan.

# Perintah SQL
sql = ''' 
SELECT *
  FROM pelanggan AS pl
    LEFT JOIN penjualan AS pn 
      USING(id_pelanggan)
        WHERE pn.id_pelanggan IS NULL
'''

# Eksekusi perintah  SQL
pd.read_sql(sql, en_co)

Kuis:

• Berdasarkan hasil diatas, pada saat kapan sebiaknya anda akan menggunakan Left Join (Partial)?

4.3.4 RIGHT JOIN

Kebalikan dari LEFT JOIN, pada RIGHT JOIN, data pada tabel sebelah kanan akan ditampilkan semua, sedangkan data pada sebelah kiri hanya ditampilkan jika data terkait pada tabel tersebut muncul pada tabel sebelah kanan.

# Perintah SQL
sql = ''' 
SELECT *
  FROM pelanggan pl
    RIGHT JOIN penjualan 
      USING(id_pelanggan)
'''

# Eksekusi perintah  SQL
pd.read_sql(sql, en_co)

Kuis:

• Perlukah RIGHT JOIN?

Jika kita teliti lebih lanjut, sebenarnya right join hanya memindah posisi tabel, dari kiri ke kanan, contoh query pada right join dapat kita ubah dengan menjadi LEFT JOIN dengan mengubah posisi tabel.

4.3.5 FULL JOIN

Tabel yang dihasilkan adalah tabel dengan semua nilai dari kedua tabel. Apabila terdapat nilai yang tidak memiliki kesamaan dari kedua tabel, maka muncul nilai null.

# Perintah SQL
sql = ''' 
SELECT *
  FROM penjualan AS pn
    JOIN penjualan_detail AS pede
      ON pn.id_transaksi != pede.id_transaksi
'''

# Eksekusi perintah  SQL
pd.read_sql(sql, en_co)

4.3.6 Implisit Join Tabel

Sejauh ini, kita menampilkan data dari beberapa tabel MySQL dengan menggunakan klausa JOIN. Selain menggunakan klausa JOIN, terdapat satu cara lagi untuk menggabungkan tabel MySQL, yaitu menggunakan implisit join, disebut implisit join karena kita tidak menggunakan klausa JOIN, pada implisit join, kriteria hubungan antar tabel di definisikan pada klausa WHERE. Sebagai contoh, mari kita gabungkan tabel pelanggan dan penjualan, jalankan query berikut:

# Perintah SQL
sql = ''' 
SELECT pl.id_pelanggan, nama, id_transaksi, tgl_transaksi, total_transaksi
  FROM pelanggan pl, penjualan pn
    WHERE pl.id_pelanggan = pn.id_pelanggan
'''

# Eksekusi perintah  SQL
pd.read_sql(sql, en_co)

Perhatikan bahwa hasil tersebut sama persis dengan hasil pada contoh INNER JOIN, sehingga dapat disimpulkan bahwa implisit join = inner join.

Implisit JOIN ini merupakan cara lama ketika pertama kali standar SQL dibuat, setelah muncul standar yang lebih baru (SQL2) maka mulai digunakanlah klausa JOIN. Saya sendiri prefer menggunakan klausa JOIN karena lebih mudah dibaca dan dipahami, terutama hubungan antara tabel yang digabungkan.

Pada bentuk klausa JOIN, hubungan antar tabel dinyatakan pada klausa ON atau USING, sedangkan filter datanya dilakukan pada klausa WHERE, misal:

# Perintah SQL
sql = ''' 
SELECT pl.id_pelanggan, nama, id_transaksi, tgl_transaksi, total_transaksi
  FROM pelanggan pl
    LEFT JOIN penjualan pn USING (id_pelanggan)
      WHERE pl.id_pelanggan = 2 OR pl.id_pelanggan = 1
'''

# Eksekusi perintah  SQL
pd.read_sql(sql, en_co)

Sedangkan pada implisit JOIN, hubungan antar tabel dan filter datanya, semua didefinisikan pada klausa WHERE, misal:

# Perintah SQL
sql = ''' 
SELECT pl.id_pelanggan, nama, id_transaksi, tgl_transaksi, total_transaksi
  FROM pelanggan pl, penjualan pn
    WHERE pl.id_pelanggan = pn.id_pelanggan
      AND (pl.id_pelanggan = 2 OR pl.id_pelanggan = 1)
'''

# Eksekusi perintah  SQL
pd.read_sql(sql, en_co)

4.4 SUBQUERY

Subquery yang digunakan adalah scalar subquery, tetapi subqueries juga dibagi menjadi beberapa tipe berdasarkan penggunaannya yaitu sebagaimana dikutip dari Scalar:

• Single row subquery
• Multiple row subquery
• Correlated subquery
• Nested subqueries

4.4.1 Single row subquery

hasil yang diperoleh digunakan sebagai operator pembanding seperti =, >, <, atau klausa IN.

from sqlalchemy import create_engine
import pymysql
import pandas as pd

# membuat engine
engine = create_engine("mysql+pymysql://{user}:{pw}@localhost/{db}"
                       .format(user="bakti",
                               pw="123",
                               db="jne2"))

# engine koneksi ke database
en_co2 = engine.connect()

Contoh dibawah ini adalah melihat status dari harga jual maksimum dari order items.

# Perintah SQL
sql = '''
SELECT order_id, product_id, status , sale_price
  FROM `order_items` 
  WHERE sale_price = (SELECT MAX(sale_price) FROM `order_items`)
'''

# Eksekusi perintah  SQL
pd.read_sql(sql, en_co2)

4.4.2 Multiple row subquery

Multiple row subquery merupakan jenis subquery yang menghasilkan beberapa baris data. Serupa dengan single row subquery, hasil yang diperoleh dari subquery akan digunakan sebagai operator pembanding.

Contoh dibawah ini adalah melihat negara-negara mana saja yang berada berdasarkan tabel products. Maka akan ditampilkan bahwa didalam tabel products, hanya distribusi dari 6 negara.

# Perintah SQL
sql = '''
SELECT id, name,latitude, longitude 
  FROM `distribution_centers` 
    WHERE id IN (SELECT distribution_center_id FROM `products`)
'''

# Eksekusi perintah  SQL
pd.read_sql(sql, en_co2)

4.4.3 Correlated Subquery

Correlated subquery merupakan jenis subquery yang hasilnya bergantung dengan query utama (main query). Sifatnya yang dependen membuat subquery jenis ini berbeda dari jenis lainnya. Ciri-ciri subquery jenis ini adalah memiliki sumber data yang sama dengan query utama. Umumnya, hasil yang diperoleh akan digunakan untuk memfilter hasil dari query utama.

Contohnya adalah dengan mengambil id, product_id, product_category, dan product_name dengan melihat product retail price yang lebih kecil dari rata rata product retail price keseluruhannya.

# Perintah SQL
sql = '''
SELECT id,product_id, product_category, product_name 
  FROM `inventory_items` 
  WHERE product_retail_price <(SELECT AVG(product_retail_price) 
    FROM `inventory_items`)
'''

# Eksekusi perintah  SQL
pd.read_sql(sql, en_co2)

4.4.4 Nested Subqueries

Nested Subqueries merupakan jenis subqueries di mana terdapat subquery lain di dalam subquery. Pada umumnya, subquery jenis ini digunakan untuk perhitungan atau filter yang cukup kompleks untuk sebuah dataset.

Contoh disini tidak jauh beda dengan contoh sebelumnya namun disini dikhususkan untuk product category “ACCESSORIES”

# Perintah SQL
sql = '''
SELECT id,product_id,product_name FROM `inventory_items` WHERE product_retail_price < (SELECT AVG(product_retail_price) FROM `inventory_items` WHERE product_category="Accessories")
'''

# Eksekusi perintah  SQL
pd.read_sql(sql, en_co2)

4.5 Studi Kasus

Database JNE2 adalah data fiktif yang dikembangkan oleh tim dsciencelabs untuk pelatihan Data Analytics with Python & SQL. Kumpulan data tersebut berisi informasi tentang pelanggan, produk, pesanan, logistik, promosi, dan kampanye pemasaran digital. Tugas peserta pelatihan di sini adalah menjawab beberapa pertanyaan terkait bisnis, mendapatkan wawasan, dan memberikan beberapa rekomendasi untuk meningkatkan pendapatan suatu perusahaan.

4.5.1 Soal 1

Andaikan Divisi Marketing akan memberikan promosi selama perayaan Imlek untuk pelanggan wanita di Jepang melalui email. Bagaimana anda mendapatkan data tentang berapa banyak pelanggan yang harus dipromosikan setiap email!

# Perintah SQL
sql = ''' 
SELECT id, email
  FROM `users` 
    WHERE gender = 'F' AND country = 'Japan'
      ORDER BY 1
'''

# Eksekusi perintah  SQL
pd.read_sql(sql, en_co2)

Kuis:

• Apakah maksud dari ORDER BY 1 ?

4.5.2 Soal 2

Cara pemasaran apa yang harus perlu ditingkatkan oleh marketing?

# Perintah SQL
sql = ''' 
SELECT
  u.traffic_source, 
  COUNT(DISTINCT oi.user_id) total_customer
FROM `order_items` oi
LEFT JOIN `users` u
ON oi.user_id = u.id
WHERE oi.status NOT IN ('Cancelled','Returned')
GROUP BY 1
ORDER BY 2 DESC
'''

# Eksekusi perintah  SQL
pd.read_sql(sql, en_co2)

4.5.3 Soal 3

Berikan daftar 10 id pelanggan dengan total pembelian paling banyak secara keseluruhan.

# Perintah SQL
sql = ''' 
SELECT oi.user_id, u.email,
  SUM(oi.sale_price*o.num_of_item) total_purchase
FROM `order_items` oi
  LEFT JOIN `users` u ON oi.user_id = u.id
  LEFT JOIN `orders` o ON oi.order_id = o.order_id
WHERE oi.status NOT IN ('Cancelled','Returned')
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 10
'''

# Eksekusi perintah  SQL
pd.read_sql(sql, en_co2)

4.6 Soal Latihan Kelompok

1. Berapa Penjualan setiap barang yang terdapat pada penjualan detail
2. Siapa Nama Pelanggan yang total transaksi paling banyak
3. Nama Barang yang dibeli oleh setiap pelanggan dan jumlahnya
4. Mencari dan Menunjukkan Status dari orders dengan berdasarkan nilai maximum dari order_id dengan menampilkan order_id, user_id, num_of_item, status.
5. Mencari dan Menunjukkan Negara mana saja yang TIDAK tercantum dalam data inventory_items dengan menampilkan id, name,latitude, longitude.
6. Mencari dan Menunjukkan nama product dan brand jika dengan berdasarkan retail price lebih besar dari rata-rata retail price keseluhurannya dalam data products dengan menampilkan id,name,brand
7. Mencari dan Menunjukkan nama product jika dengan berdasarkan retail price lebih besar dari rata-rata retail price keseluhurannya dalam data products Serta berdasarkan brand “TYR” dengan menampilkan id,cost,name,sku