JOIN ORACLE

TIPE JOIN :
1. Equijoin : perbandingan berdasarkan data yang sama, antara 2 tabel.
non-equijoin : perbandingan berdasarkan data yang tidak mesti sama, data pada table pertama di cari yang sama maupun yang tidak sama dengan data di tabel ke dua

2. Self join : join ke tabelnya sendiri

3. Outer join : data yang bisa sama maupun tidak sama dengan di tabel ke 2 nya

4. Cartesian product : membangingkan 1 per 1 data2 pada tabel nya. dari tabel yang ada.

Tipe join :
select (data yang ingin di tampilkan)
from (asal table untuk ditampilkan)
where (syarat yang mau di tampilkan)

JOIN
1. from A natural join B –> dibandingkan berdasarkan semua nama kolom yang sama
2. from A join B using (id) –> pemilihan nama kolom yang namanya sama
3. from A join B on (A.id = B.code) –> memungkinkan perbandingan tidak mesti sama nama kolom,
tetapi yang penting tipe data pada kolonm yang di bandingkan sama

CONTOH
NATURAL JOIN :
select department_id, department_name, location_id, city
from departments natural join locations;

CREATE JOINS WITH THE USING CLAUSE
select l.city, d.department_name
from locations l join departments d using (location_id)
where location_id = 1400;

select e.employee_id, e.last_name, d.location_id, department_id
from employees e join departments d
using (department_id);

CREATE JOINS WITH THE ON CLAUSE
select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
from employees e join departments d
on (e.department_id = d.department_id);

SELF-JOINS USING THE ON CLAUSE / JOIN KE DIRINYA SENDIRI
select e.last_name emp, m.last_name mgr
from employees e join employees m
on (e.manager_id = m.employee_id);

ADDITION CONDITIONS TO A JOIN
select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
from employees e join departments d
on (e.department_id = d.department_id)
and e.manager_id = 149;

THEREE-WAY JOINS WITH THE ON CLAUSE
select employee_id, city, department_name
from employees e
join departments d
on d.department_id = e.department_id
join locations l
on d.location_id = l.location_id;

NON-EQUIJOINS
drop table job_grades purge;
create table job_grades (gra varchar2(1), batas_bawah number, batas_atas number);
insert into job_grades values (’A', 1000, 2999);
insert into job_grades values (’B', 3000, 4999);
insert into job_grades values (’C', 5000, 9999);
insert into job_grades values (’D', 10000, 14999);
insert into job_grades values (’E', 15000, 24999);
insert into job_grades values (’F', 25000, 40000);
alter table job_grades add constraint pk_job primary key (gra);
commit;

Modifikasi tabel :
- Manambah/ modifikasi tipe data kolom
alter table nama_table add|modify (nama_kolom tipe_kolom[size]);
- Merubah nama kolom
alter table nama_tabel rename column nama_kolom to nama_kolom_baru;
- Menghapus kolom
alter table nama_table drop column nama_kolom;

Query Non-equijoins
select e.last_name, e.salary, j.gra
from employees e join job_grades j
on e.salary
between j.batas_bawah and j.batas_atas;

OUTER JOINS
Ada tiga tipe dalam outer joins
- left outer
- right outher
- full outher

Contoh :
LEFT OUTER JOIN
select e.last_name, e.department_id, d.department_name
from employees e left outer join departments d
on (e.department_id = d.department_id);

RIGHT OUTER JOIN
select e.last_name, e.department_id, d.department_name
from employees e right outer join departments d
on (e.department_id = d.department_id);

FULL OUTER JOIN
select e.last_name, e.department_id, d.department_name
from employees e full outer join departments d
on (e.department_id = d.department_id);