Tentang inner join, left join, right join, cross join, full join

Mari sekarang kita mempelajari tentang Inner join, Left Join, Righ Join, Cross Join dan Full Join. Saya mengambil kasus pada sql server 2000. Berikut data yang diberikan seperti pada gambar di atas.

Contoh script inner join :
select d.area_id, d.nama_area, d.luas_area, p.penduduk_id, p.nama_penduduk
from cpenduduk p
inner join
carea d on p.area_id = d.area_id

atau

select d.area_id, d.nama_area, d.luas_area, p.penduduk_id, p.nama_penduduk
from cpenduduk p, carea d
where p.area_id = d.area_id

Contoh script left join(left outer join)
select d.Area_id, d.nama_area, d.luas_area, p.penduduk_id, p.nama_penduduk from
cpenduduk p
left outer join carea d on d.area_id=p.area_id

Penjelasan : Operasi left join akan menampilkan semua isi tabel sisi kiri, walaupun data di pasangan joinnya yang disisi kanan nilainya tidak sama ataupun berisi null.

Contoh script right join(right outer join)
select d.Area_id, d.nama_area, d.luas_area, p.penduduk_id, p.nama_penduduk from
cpenduduk p
right outer join carea d on d.area_id=p.area_id

Penjelasan : Operasi right join akan menampilkan semua isi tabel sisi kanan, walaupun data di pasangan joinnya yang di sisi kiri nilainya tidak sama ataupun berisi null.


Contoh script cross join
select d.area_id, d.nama_area, d.luas_area, p.penduduk_id, p.nama_penduduk
from cpenduduk p
cross join
carea d

Penjelasan : Operasi cross join akan menampilkan semua isi tabel sisi sebelah kiri akan memiliki pasangan semua data sisi sebelah kanan. Banyaknya record cross join = jumlah record tabel pertama X jumlah record tabel kedua.


Contoh script Full join
select d.area_id, d.nama_area, d.luas_area, p.penduduk_id, p.nama_penduduk
from cpenduduk p
full join
carea d on p.area_id = d.area_id

Penjelasan : Operasi full join akan menampilkan semua isi tabel sisi kiri, walaupun data di pasangan joinnya yang disisi kanan nilainya null dan sebaliknya.


Begitulah contoh-contoh tentang inner join, left join, right join, cross join, dan full join. Sumber diambil dari beberapa artikel di internet.

SQL(Structures Query Language) seri 2 ttg Insert into

Hari ini kita akan mempelajari SQL (Structured Query Language) penggunaan insert :
Ada 2 buah metode cara melakukan insert pada tabel.
Cara 1 : INSERT INTO table_name
VALUES (value1, value2, value3,...)
Cara 2 : INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

Perbedaan dari cara 1 dengan cara 2 adalah jika cara 1 memasukkan value1, value2, dst harus sesuai dengan tipe data field pada table_name tidak boleh ada yang terbalik. Sedangkan cara 2 memasukkan value1, value2, dst harus disesuaikan penempatannya dengan column1, column2, dst.

Contoh: kita punya tabel Coba dengan field-field sebagai berikut : NRP varchar(10), Nama Varchar(50), Alamat Varchar(255), Kota Varchar(100).

cara 1 --> Insert into coba values('31108001', 'Roi', 'Sutorejo Utara II/5', 'Surabaya')
Perhatikan cara penulisan urutan untuk value1, value2, dst. 31108001 merupakan NRP, Roi merupakan field nama, dan seterusnya sesuai dengan field-field yang dideklarasikan pada tabel coba.

cara 2 --> Insert into coba(kota, nrp, alamat, nama)
values('Surabaya', '31108001', 'Sutorejo Utara II/5', 'Roi')
Perhatikan cara penulisan urutan untuk column4, column1, column3, column2) disesuaikan dengan urutan penulisan value4, value1, value3, value2.

Demikianlah contoh penggunaan sintaks sql insert into pada tabel.

SQL Select Statement

SQL Select statement

Select statement di simpan ke dalam result table, yang dinamakan result-set.
SQL Select Syntax
Select column_name(s) from table_name
And
Select * from table_name
Definisi :
Column_name(s) artinya kita dapat melakukan select pada 1 atau lebih kolom pada tabel.
Table_name artinya nama tabel yg akan kita lakukan proses select.
SQL Where clause
Klausa where digunakan untuk melakukan filter/menyaring sekumpulan record
SQl where syntax
Select column_names from table_name where column_name operator value
Operator yang diijinkan digunakan dalam klausa where adalah :

Operator	Deskripsi
=	Equal
<>	Not Equal
<	Greater than
>	Less than
>=	Greater than or equal
<=	Less than or equal
between	Between an inclusive range
Like	Search for a pattern
In	If you know the exact value you want to return for at least one of the columns

Contoh SQL dengan operator “and”
Select * from persons where FirstName = ‘Tove’ and LastName = ‘Svendson’
Arti dari sql di atas seleksi semua kolom dalam tabel persons di mana kolom FirstName berisi Tove dan LastName berisi Svendson

Contoh SQL dengan operator “or”
Select * from persons where FirstName = ‘Tove’ or FirstName = ‘Ola’
Arti dari sql di atas seleksi semua kolom dalam tabel persons di mana kolom FirstName berisi Tove atau kolom FirstName berisi Ola.

Membuat Aplikasi Java terhubung ke mysql seri 1

Berikut saya berikan sebuah contoh kasus sebuah aplikasi Java yang dihubungkan ke database mysql.

Pertama buat terlebih dahulu databasenya, dalam hal ini saya create database mysql dengan nama cobas. Kemudian buat tabel dengan nama profile.

Di dalam tabel profile tersebut terdapat 4 field yaitu :
Nama tipe data varchar(30)
Alamat tipe data varchar(50)
Kota tipe data varchar(50)
Telepon tipe data varchar(20).

Setelah itu buat aplikasi Java dengan menggunakan JFrame.

Di dalam JFrame terdiri 4 JLabel yang diberi tulisan Nama, Alamat, Kota, Telepon.
Kemudian 4 buah JTextField yang diberi nama TxtNama, TxtAlamat, TxtKota, TxtTelepon.

4 buah JButton yang diberi nama btnTambah, btnEdit, btnHapus, dan btnBatal.


Berikut contoh source code JFrame yang terbentuk.



/*
* DataMhs.java
*
* Created on July 12, 2010, 8:11 PM
*/

package javaapplicationtestdb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;

/**
*
* @author Feky Sugianto
*/
public class DataMhs extends javax.swing.JFrame {
private Connection connection = null;
private Statement statement = null;
private String strquery;
private String oldnama;


/** Creates new form DataMhs */
public DataMhs() {
initComponents();
tampilkandiGrid();
}

private boolean getConnection(){
String url = "jdbc:mysql://localhost:3306/cobas"; // sesuaikan dengan nama database.
String user = "root"; // sesuaikan dengan nama user.
String pass = ""; // sesuaikan dengan password.

try {
Class.forName("com.mysql.jdbc.Driver");
//Class.forName("jdbc.mysql.Driver");
} catch (ClassNotFoundException cnfe) {
System.err.println(cnfe.getMessage());
}

try {
connection = DriverManager.getConnection(url, user, pass);
//conn.createStatement();
return true;
} catch (SQLException se) {
return false;
}
}

public void tampilkandiGrid(){
if(getConnection()){
jLayeredPane1.setVisible(true);
jLayeredPane2.setVisible(true);

strquery = "select * from profile";
try{
//buat object statement terlebih dahulu
statement = connection.createStatement();
//buat resultset utk tempat penampungan query yg dihasilkan dari statement
ResultSet resultset = statement.executeQuery(strquery);
//columnNames adalah array Object yg terdiri dari nama-2 field tabel profile di database
Object[] columnNames = {"Nama", "Alamat", "Kota", "Telepon"};
int baris = resultset.getRow();
//buat array Object sebanyak n baris dan 4 kolom
Object[][] data = new Object[baris][4];
//buat model utk datanya
DefaultTableModel model = new DefaultTableModel(data, columnNames);
//hubungkan jtable dengan model-nya
grid.setModel(model);
while(resultset.next()){
model.addRow(new Object[]{
resultset.getString("nama"),
resultset.getString("alamat"),
resultset.getString("kota"),
resultset.getString("telepon")
});
}
//statement di close setelah tidak terpakai
statement.close();
//connection di close setelah tidak terpakai
connection.close();
grid.setRowSelectionInterval(0, 0);
}catch(SQLException e){
e.printStackTrace();
}
}


}

/** This method is called from within the constructor to
* initialize the form.
* WARNING: Do NOT modify this code. The content of this method is
* always regenerated by the Form Editor.
*/
@SuppressWarnings("unchecked")
// //GEN-BEGIN:initComponents
private void initComponents() {

jPanel1 = new javax.swing.JPanel();
jLabel1 = new javax.swing.JLabel();
jLabel2 = new javax.swing.JLabel();
jLabel3 = new javax.swing.JLabel();
jLayeredPane1 = new javax.swing.JLayeredPane();
TxtNama = new javax.swing.JTextField();
TxtAlamat = new javax.swing.JTextField();
TxtKota = new javax.swing.JTextField();
TxtTelp = new javax.swing.JTextField();
jLayeredPane2 = new javax.swing.JLayeredPane();
btnTambah = new javax.swing.JButton();
btnEdit = new javax.swing.JButton();
btnHapus = new javax.swing.JButton();
btnSimpan = new javax.swing.JButton();
btnBatal = new javax.swing.JButton();
jLabel4 = new javax.swing.JLabel();
jScrollPane1 = new javax.swing.JScrollPane();
grid = new javax.swing.JTable();

setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
setTitle("Contoh Frame");

jLabel1.setText("Nama");

jLabel2.setText("Alamat");

jLabel3.setText("Kota");

TxtNama.setBounds(10, 10, 244, 20);
jLayeredPane1.add(TxtNama, javax.swing.JLayeredPane.DEFAULT_LAYER);
TxtAlamat.setBounds(10, 40, 244, 20);
jLayeredPane1.add(TxtAlamat, javax.swing.JLayeredPane.DEFAULT_LAYER);
TxtKota.setBounds(10, 70, 240, 20);
jLayeredPane1.add(TxtKota, javax.swing.JLayeredPane.DEFAULT_LAYER);
TxtTelp.setBounds(10, 100, 240, 20);
jLayeredPane1.add(TxtTelp, javax.swing.JLayeredPane.DEFAULT_LAYER);

btnTambah.setText("Tambah");
btnTambah.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
btnTambahActionPerformed(evt);
}
});
btnTambah.setBounds(10, 10, 110, 23);
jLayeredPane2.add(btnTambah, javax.swing.JLayeredPane.DEFAULT_LAYER);

btnEdit.setText("Edit");
btnEdit.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
btnEditActionPerformed(evt);
}
});
btnEdit.setBounds(140, 10, 100, 23);
jLayeredPane2.add(btnEdit, javax.swing.JLayeredPane.DEFAULT_LAYER);

btnHapus.setText("Hapus");
btnHapus.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
btnHapusActionPerformed(evt);
}
});
btnHapus.setBounds(260, 10, 90, 23);
jLayeredPane2.add(btnHapus, javax.swing.JLayeredPane.DEFAULT_LAYER);

btnSimpan.setText("Simpan");
btnSimpan.setBounds(370, 10, 90, 23);
jLayeredPane2.add(btnSimpan, javax.swing.JLayeredPane.DEFAULT_LAYER);

btnBatal.setText("Batal");
btnBatal.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
btnBatalActionPerformed(evt);
}
});
btnBatal.setBounds(470, 10, 100, 23);
jLayeredPane2.add(btnBatal, javax.swing.JLayeredPane.DEFAULT_LAYER);

jLabel4.setText("Telepon");

javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
jPanel1.setLayout(jPanel1Layout);
jPanel1Layout.setHorizontalGroup(
jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(jPanel1Layout.createSequentialGroup()
.addContainerGap()
.addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addComponent(jLayeredPane2, javax.swing.GroupLayout.PREFERRED_SIZE, 660, javax.swing.GroupLayout.PREFERRED_SIZE)
.addGroup(jPanel1Layout.createSequentialGroup()
.addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addComponent(jLabel1)
.addComponent(jLabel2)
.addComponent(jLabel3)
.addComponent(jLabel4))
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
.addComponent(jLayeredPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 430, javax.swing.GroupLayout.PREFERRED_SIZE)))
.addContainerGap(386, Short.MAX_VALUE))
);
jPanel1Layout.setVerticalGroup(
jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(jPanel1Layout.createSequentialGroup()
.addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(jPanel1Layout.createSequentialGroup()
.addGap(23, 23, 23)
.addComponent(jLabel1)
.addGap(18, 18, 18)
.addComponent(jLabel2, javax.swing.GroupLayout.PREFERRED_SIZE, 14, javax.swing.GroupLayout.PREFERRED_SIZE)
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
.addComponent(jLabel3)
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
.addComponent(jLabel4))
.addGroup(jPanel1Layout.createSequentialGroup()
.addContainerGap()
.addComponent(jLayeredPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 122, javax.swing.GroupLayout.PREFERRED_SIZE)))
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
.addComponent(jLayeredPane2, javax.swing.GroupLayout.PREFERRED_SIZE, 52, javax.swing.GroupLayout.PREFERRED_SIZE)
.addContainerGap(19, Short.MAX_VALUE))
);

grid.setModel(new javax.swing.table.DefaultTableModel(
new Object [][] {
{null, null, null, null},
{null, null, null, null},
{null, null, null, null},
{null, null, null, null}
},
new String [] {
"Title 1", "Title 2", "Title 3", "Title 4"
}
));
grid.addMouseListener(new java.awt.event.MouseAdapter() {
public void mouseClicked(java.awt.event.MouseEvent evt) {
gridMouseClicked(evt);
}
});
jScrollPane1.setViewportView(grid);

javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
getContentPane().setLayout(layout);
layout.setHorizontalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addContainerGap()
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 724, javax.swing.GroupLayout.PREFERRED_SIZE)
.addComponent(jPanel1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
.addContainerGap())
);
layout.setVerticalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
.addGap(18, 18, 18)
.addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
.addContainerGap(61, Short.MAX_VALUE))
);

pack();
}// //GEN-END:initComponents

private void btnTambahActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnTambahActionPerformed
// TODO add your handling code here:
if(getConnection()){
strquery = "insert into profile(nama, alamat, kota, telepon) values('"+
TxtNama.getText() + "', '" + TxtAlamat.getText() + "', '" +
TxtKota.getText() + "', '" + TxtTelp.getText() + "')";
JOptionPane.showMessageDialog(null, strquery);
try{
statement = connection.createStatement();
//utk query insert tidak memerlukan resultset
int r = statement.executeUpdate(strquery);
JOptionPane.showMessageDialog(null, "Data telah masuk");
statement.close();
connection.close();
tampilkandiGrid();
}catch(SQLException ex){
ex.printStackTrace();
}
}
}//GEN-LAST:event_btnTambahActionPerformed

private void btnEditActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnEditActionPerformed
// TODO add your handling code here:
if(getConnection()){
strquery = "update profile set nama = '" + TxtNama.getText() +"', " +
"alamat = '" + TxtAlamat.getText() + "', " +
"kota = '" + TxtKota.getText() + "', " +
"telepon = '" + TxtTelp.getText() + "' " +
"where nama = '" + oldnama + "'";
JOptionPane.showMessageDialog(null, strquery);
try{
statement = connection.createStatement();
//utk query update tidak memerlukan resultset
int r = statement.executeUpdate(strquery);
JOptionPane.showMessageDialog(null, "Data telah terupdate");
statement.close();
connection.close();
tampilkandiGrid();
}catch(SQLException ex){
ex.printStackTrace();
}
}
}//GEN-LAST:event_btnEditActionPerformed

private void gridMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_gridMouseClicked
// TODO add your handling code here:
if(evt.getClickCount() == 2){
TxtNama.setText(grid.getValueAt(grid.getSelectedRow(), 0).toString());
TxtAlamat.setText(grid.getValueAt(grid.getSelectedRow(), 1).toString());
TxtKota.setText(grid.getValueAt(grid.getSelectedRow(), 2).toString());
TxtTelp.setText(grid.getValueAt(grid.getSelectedRow(), 3).toString());
oldnama = TxtNama.getText();
}
}//GEN-LAST:event_gridMouseClicked

private void btnBatalActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnBatalActionPerformed
// TODO add your handling code here:
TxtNama.setText("");
TxtAlamat.setText("");
TxtKota.setText("");
TxtTelp.setText("");
}//GEN-LAST:event_btnBatalActionPerformed

private void btnHapusActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnHapusActionPerformed
// TODO add your handling code here:
if(getConnection()){
strquery = "delete from profile " +
"where nama = '" + TxtNama.getText() + "'";
JOptionPane.showMessageDialog(null, strquery);
try{
statement = connection.createStatement();
//utk query delete tidak memerlukan resultset
int r = statement.executeUpdate(strquery);
JOptionPane.showMessageDialog(null, "Data telah terhapus");
statement.close();
connection.close();
tampilkandiGrid();
}catch(SQLException ex){
ex.printStackTrace();
}
}
}//GEN-LAST:event_btnHapusActionPerformed

/**
* @param args the command line arguments
*/
public static void main(String args[]) {
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new DataMhs().setVisible(true);
}
});
}

// Variables declaration - do not modify//GEN-BEGIN:variables
private javax.swing.JTextField TxtAlamat;
private javax.swing.JTextField TxtKota;
private javax.swing.JTextField TxtNama;
private javax.swing.JTextField TxtTelp;
private javax.swing.JButton btnBatal;
private javax.swing.JButton btnEdit;
private javax.swing.JButton btnHapus;
private javax.swing.JButton btnSimpan;
private javax.swing.JButton btnTambah;
private javax.swing.JTable grid;
private javax.swing.JLabel jLabel1;
private javax.swing.JLabel jLabel2;
private javax.swing.JLabel jLabel3;
private javax.swing.JLabel jLabel4;
private javax.swing.JLayeredPane jLayeredPane1;
private javax.swing.JLayeredPane jLayeredPane2;
private javax.swing.JPanel jPanel1;
private javax.swing.JScrollPane jScrollPane1;
// End of variables declaration//GEN-END:variables

}