Sql de kullanmış olduğunuz klasik inner , left outer , right outer ve full outer join yanı sıra oracle 12c den bu yana işlerimizi dahada kolaylaştıracak lateral, coss apply ve outer apply joinlerinin ne işe yaradığına değineceğim. Örneklerini https://livesql.oracle.com üzerinden ücretsiz hesap alıp deyebilirsiniz
LATERAL
Sql cümleleri içerisinde aynı satırda başka bir tablodan veri göstermek istediğimizde hata alıyorduk. Lateral join ile soldaki tablo referans gösterilip satır içi görünüm sağlanır. Çok karmaşık ve uzun bolca sub querry olan sorgularda yazmayı kolaylaştıracaktır. .
|
1 2 3 |
SELECT department_name, first_name|| ' ' ||last_name employee_name FROM hr.employees e, (SELECT * FROM hr.departments WHERE department_id = e.department_id) sub; |
bu sorgu sonucunda ORA-00904: “E”.”DEPARTMENT_ID”: invalid identifier hatasını almaktayız.
|
1 2 3 4 5 |
SELECT department_name, first_name|| ' ' ||last_name employee_name FROM hr.employees e, LATERAL (SELECT * FROM hr.departments d WHERE d.department_id = e.department_id) sub where sub.department_name = 'Human Resources'; |
| DEPARTMENT_NAME | EMPLOYEE_NAME |
|---|---|
| Human Resources | Susan Mavris |
Lateral join ile aynı sql de sağ taraftaki tablonun şartını kullanarak yazabiliriz.
CROSS APPLY
Cross apply olarak ta kullanılır. Soldaki tablonun tüm satırlarını döndürür. Örnekle anlatmak gerekirse insan kaynaklarında 2007 tarihinden son en yüksek departman bazında en yüksek maaşla alınan 2 kişiyi bulmak istediğimizde bir çok yere max sum vs. analitik komutlar içeren querryler yazmak yerine aşağıdaki gibi alabiliriz.
|
1 2 3 4 5 6 7 8 9 10 11 |
select d.*, first_name, last_name, salary from hr.departments d cross apply ( select first_name, last_name, salary from hr.employees e where e.department_id = d.department_id and e.hire_date >= date'2007-01-01' order by e.salary desc fetch first 2 rows only ) sub order by 1, salary desc; |
Soldaki deparments tablosunda yer alan departman bilgisi ile sağdaki sub querry de yer alan en yüksek maaş alan 2 personel bilgisini birleştirmiş olduk.
OUTER APPLY
Sağdaki tabloyu referans alır. Birleştirmenin sağ tarafı değer döndürmez ise sütünlarda NULL görülür bir nevi left outer join gibi çalışır.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT department_name, employee_id, employee_name,salary FROM hr.departments d OUTER APPLY (SELECT employee_id, first_name|| ' ' ||last_name employee_name,salary FROM hr.employees e WHERE salary >= 2000 AND e.department_id = d.department_id order by salary asc fetch first 1 rows only) ORDER BY 4 desc,1 asc; |
| DEPARTMENT_NAME | EMPLOYEE_ID | EMPLOYEE_NAME | SALARY |
|---|---|---|---|
| Benefits | – | – | – |
| Construction | – | – | – |
| Contracting | – | – | – |
| Control And Credit | – | – | – |
| Corporate Tax | – | – | – |
| Government Sales | – | – | – |
| IT Helpdesk | – | – | – |
| IT Support | – | – | – |
| Manufacturing | – | – | – |
| NOC | – | – | – |
| Operations | – | – | – |
| Payroll | – | – | – |
| Recruiting | – | – | – |
| Retail Sales | – | – | – |
| Shareholder Services | – | – | – |
| Treasury | – | – | – |
| Executive | 101 | Neena Kochhar | 17000 |
| Public Relations | 204 | Hermann Baer | 10000 |
| Accounting | 206 | William Gietz | 8300 |
| Finance | 113 | Luis Popp | 6900 |
| Human Resources | 203 | Susan Mavris | 6500 |
| Sales | 173 | Sundita Kumar | 6100 |
| Marketing | 202 | Pat Fay | 6000 |
| Administration | 200 | Jennifer Whalen | 4400 |
| IT | 107 | Diana Lorentz | 4200 |
| Purchasing | 119 | Karen Colmenares | 2500 |
| Shipping | 132 | TJ Olson | 2100 |
Bu sorguda maaşı 2000 üzerinde olan departman bazında en düşük alan personeli listeliyoruz. Görüldüğü gibi departman bilgileri NULL olarak gelmektedir.