PBD pert 10

PERTEMUAN 10

JOIN TABLE
--> MENGGABUNGKAN DUA TABEL YANG BERBEDA DENGAN KRITERIA TERTENTU

JOIN TABLE DI BAGI 4
1. NATURAL JOIN
--> PENGGABUNGAN DUA BUAH TABEL YANG MENGHASILKAN TABEL BARU DIMANA TERDAPAT KESAMAAN DATA DIANTARA KEDUANYA. (DATA YANG TIDAK SAMA TIDAK DI TAMPILKAN).

2. LEFT JOIN
--> PENGGABUNGAN DUA BUAH TABEL YANG MENGHASILKAN TABEL BARU DIMANA DATA PADA TABEL KIRI DI TAMPILKAN SEMUA WALAUPUN DATA TERSEBUT TIDAK TERDAPAT PADA TABEL KANAN

3. RIGHT JOIN
--> PENGGABUNGAN DUA BUAH TABEL YANG MENGHASILKAN TABEL BARU DIMANA DATA PADA TABEL KANAN DI TAMPILKAN SEMUA WALAUPUN DATA TERSEBUT TIDAK TERDAPAT PADA TABEL KIRI

TABEL KIRI = TABEL SETELAH FROM
TABEL KANAN = TABEL SETELAH JOIN

4. INNER JOIN
--> SAMA DENGAN NATURAL JOIN TP HASILNYA TELAH DI URUTKAN

SINTAX NATURAL JOIN

SELECT NT1.NF, NT2.NF DST
FROM NT1 NATURAL JOIN NT2;

NT1 = NAMA TABEL1
NF = NAMA FIELD
DST = DAN SETERUSNYA


SINTAX LEFT JOIN
SELECT NT1.NF, NT2.NF DST
FROM NT1 LEFT JOIN NT2
ON NT1.AA = NT2.AA;


AA = FIELD YANG SAMA ANTAR KEDUA TABEL


SINTAX LEFT JOIN
SELECT NT1.NF, NT2.NF DST
FROM NT1 RIGHT JOIN NT2
ON NT1.AA = NT2.AA;

SINTAX LEFT JOIN
SELECT NT1.NF, NT2.NF DST
FROM NT1 INNER JOIN NT2
ON NT1.AA = NT2.AA;

PENGGANTI KATA ON DALAM JOIN TABLE

1. NATURAL
2. USING
-> ;
ERROR 1046 (3D000): No database selected
mysql> CREATE DATABASE AA;
Query OK, 1 row affected (0.02 sec)

mysql> USE AA;
Database changed
mysql> CREATE TABLE BARANG
-> (ID_BAR INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> NAMA VARCHAR, HARGA DOUBLE, SATUAN VARCHAR(20));
ERROR 1064 (42000): You have an error in your SQL syntax; check thdoublee manual that
corresponds to your MySQL server version for the right syntax to use near ' HARG
A DOUBLE, SATUAN VARCHAR(20))' at line 3
mysql> CREATE TABLE BARANG
-> (ID_BAR INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> NAMA VARCHAR(20), HARGA DOUBLE, SATUAN VARCHAR(20));
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE JUAL
-> (ID_JUAL INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> ID_BAR INT, TOTAL DOUBLE, TANGGAL DATE,
-> ADMIN VARCHAR(20));
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO BARANG
-> (NAMA, HARGA, SATUAN) VALUES
-> ('BAJU',10000,'PCS'),
-> ('TOPI',5000,'PCS'),
-> ('SAPU',13000,'PCS'),
-> ('PANCI',13000,'PCS'),
-> ('PIRING',26000,'LUSIN');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> INSERT INTO JUAL
-> (ID_BAR, TOTAL, TANGGAL, ADMIN) VALUES
-> (1,100000,'2011-06-20','JOKO'),
-> (2,50000,'2011-06-21','SANTI'),
-> (3,26000,'2011-06-20','JOKO');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0


mysql> INSERT INTO JUAL
-> (ID_BAR, TOTAL, TANGGAL, ADMIN) VALUES
-> (1,100000,'2011-06-21','SANTI'),
-> (2,50000,'2011-06-23','ANTO');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM JUAL;
+---------+--------+--------+------------+-------+
| ID_JUAL | ID_BAR | TOTAL | TANGGAL | ADMIN |
+---------+--------+--------+------------+-------+
| 1 | 1 | 100000 | 2011-06-20 | JOKO |
| 2 | 2 | 50000 | 2011-06-21 | SANTI |
| 3 | 3 | 26000 | 2011-06-20 | JOKO |
| 4 | 1 | 100000 | 2011-06-21 | SANTI |
| 5 | 2 | 50000 | 2011-06-23 | ANTO |
+---------+--------+--------+------------+-------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM BARANG;
+--------+--------+-------+--------+
| ID_BAR | NAMA | HARGA | SATUAN |
+--------+--------+-------+--------+
| 1 | BAJU | 10000 | PCS |
| 2 | TOPI | 5000 | PCS |
| 3 | SAPU | 13000 | PCS |
| 4 | PANCI | 13000 | PCS |
| 5 | PIRING | 26000 | LUSIN |
+--------+--------+-------+--------+
5 rows in set (0.01 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B, JUAL J
-> WHERE B.ID_BAR = J.ID_BAR;
+--------+------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
+--------+------+-------+--------+------------+
5 rows in set (0.00 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B, JUAL J
-> WHERE B.ID_BAR = J.ID_BAR;
+--------+------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
+--------+------+-------+--------+------------+
5 rows in set (0.01 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B, JUAL J
-> WHERE B.ID_BAR = J.ID_JUAL;
+--------+--------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+--------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 4 | PANCI | 13000 | 100000 | 2011-06-21 |
| 5 | PIRING | 26000 | 50000 | 2011-06-23 |
+--------+--------+-------+--------+------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM JUAL;
+---------+--------+--------+------------+-------+
| ID_JUAL | ID_BAR | TOTAL | TANGGAL | ADMIN |
+---------+--------+--------+------------+-------+
| 1 | 1 | 100000 | 2011-06-20 | JOKO |
| 2 | 2 | 50000 | 2011-06-21 | SANTI |
| 3 | 3 | 26000 | 2011-06-20 | JOKO |
| 4 | 1 | 100000 | 2011-06-21 | SANTI |
| 5 | 2 | 50000 | 2011-06-23 | ANTO |
+---------+--------+--------+------------+-------+
5 rows in set (0.00 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL
-> FROM BARANG B NATURAL JOIN JUAL J;
+--------+------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
+--------+------+-------+--------+------------+
5 rows in set (0.00 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B
-> LEFT JOIN JUAL J
-> ON B.ID_BAR = J.ID_BAR;
+--------+--------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+--------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 4 | PANCI | 13000 | NULL | NULL |
| 5 | PIRING | 26000 | NULL | NULL |
+--------+--------+-------+--------+------------+
7 rows in set (0.00 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B
-> RIGHT JOIN JUAL J
-> ON B.ID_BAR = J.ID_BAR;
+--------+------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
+--------+------+-------+--------+------------+
5 rows in set (0.00 sec)

mysql> INSERT INTO JUAL
->
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 1
mysql> DESC JUAL;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| ID_JUAL | int(11) | NO | PRI | NULL | auto_increment |
| ID_BAR | int(11) | YES | | NULL | |
| TOTAL | double | YES | | NULL | |
| TANGGAL | date | YES | | NULL | |
| ADMIN | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.07 sec)

mysql> INSERT INTO JUAL
-> (ID_BAR, TOTAL, TANGGAL, ADMIN) VALUES
-> (10,100000,'2011-06-21','JOKO');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B
-> RIGHT JOIN JUAL J
-> ON B.ID_BAR = J.ID_BAR;
+--------+------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
| NULL | NULL | NULL | 100000 | 2011-06-21 |
+--------+------+-------+--------+------------+
6 rows in set (0.01 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B
-> INNER JOIN JUAL J
-> ON B.ID_BAR = J.ID_BAR;
+--------+------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
+--------+------+-------+--------+------------+
5 rows in set (0.02 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B
-> NATURAL LEFT JOIN JUAL J;
+--------+--------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+--------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 4 | PANCI | 13000 | NULL | NULL |
| 5 | PIRING | 26000 | NULL | NULL |
+--------+--------+-------+--------+------------+
7 rows in set (0.01 sec)

mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B
-> NATURAL LEFT JOIN JUAL J;
+--------+--------+-------+--------+------------+
| ID_BAR | NAMA | HARGA | TOTAL | TANGGAL |
+--------+--------+-------+--------+------------+
| 1 | BAJU | 10000 | 100000 | 2011-06-20 |
| 1 | BAJU | 10000 | 100000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-21 |
| 2 | TOPI | 5000 | 50000 | 2011-06-23 |
| 3 | SAPU | 13000 | 26000 | 2011-06-20 |
| 4 | PANCI | 13000 | NULL | NULL |
| 5 | PIRING | 26000 | NULL | NULL |
+--------+--------+-------+--------+------------+
7 rows in set (0.00 sec)

mysql>
mysql> SELECT B.ID_BAR, B.NAMA, B.HARGA,
-> J.TOTAL, J.TANGGAL FROM BARANG B
-> LEFT JOIN JUAL J
-> USING (ID_BAR);