- Tulis pernyataan SELECT untuk mengakses data dari lebih dari satu tabel mempergunakan equijoins dan bukan equijoins
- Gabungkan satu tabel itu sendiri dengan menggunakan join-self
- Lihat data yang umumnya tidak menjumpai satu kondisi gabungan dengan mempergunakan penggabungan luar (outer join)
Mendapatkan Kembali Dokumen dengan Penggabungan Natural
SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations
;
Mendapatkan Kembali Dokumen dengan Kalimat USING
SELECT employees.employee_id, employees.last_name,
departments.location_id, department_id
FROM employees JOIN departments
USING (department_id) ;
Menggunakan Nama Lain Tabel
SELECT e.employee_id, e.last_name,
d.location_id, department_id
FROM employees e JOIN departments d
USING (department_id) ;
Mendapatkan Kembali Dokumen dengan Kalimat ON
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-Join Menggunakan Kalimat ON
SELECT e.last_name emp, m.last_name mgr
FROM employees e JOIN employees m
ON (e.manager_id = m.employee_id);
Menerapkan Kondisi Tambahan pada Sebuah Penggabungan
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 ;
Membuat Penggabunga THREE-WAY dengan Kalimat ON
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;
Mengembalikan Dokumen dengan Non-Equijoins
SELECT
e.last_name, e.salary, j.grade_level
FROM employees e JOIN job_grades j
ON e.salary
BETWEEN j.lowest_sal AND
j.highest_sal;
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, d.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
Membuat Penggabungan Silang (CROSS)
SELECT last_name, department_name
FROM employees
CROSS JOIN departments
;
Tidak ada komentar:
Posting Komentar