DERS 8: BİRDEN FAZLA TABLODAN VERİ GÖRÜNTÜLEME

 Tabloları birbirine bağlayarak sorgular oluşturabiliriz. Bu işleme JOIN işlemi deriz. Join işlemini yapabilmek için bir tabloda iki tane önemli alanımız hatta olmazsa olmaz alanımız vardır. Primary Key (unique) alandır. Sadece o tabloya özeldir. Birde foreign key alanıdır. İki tabloyu join ederken primary key olan alanı diğer tablodaki foreign olan alan birleştiririz. Foreign key alanı zaten join işlemi için vardır. Bir tablodaki primary key alanı başka bir tabloda forign key olarak eklenir. Örneğin: employees tablosundaki employee_id, job_id, manager_id, department_id gibi alanlardır. Çünkü bu alanlar kendi ilgili tablolarında primary key olarak bulunmaktadırlar. Employees tablosundaki department_id alanı departments tablosunda primary key olarak bulunmaktadır. Ancak managers diye bir tablo yok peki o zaman employees tablosunda neden manager_id var dersek, employees tablosunuda kendi kendine de join edebilmekteyiz, işte bu yüzden manager_id alanı eklenmiştir. Yani çalışanlar aynı zamanda müdür olabileceğinden bununla ilgili sorgularda gerekecektir ki örneklerle daha iyi anlayacağızdır.

Bütün tabloları bir tabloya ekleyebilirdik, ancak bütün tablolar bir tabloya eklenirse, milyarlarca kayıt olan veri tabanlarında inanılmaz performans kayıpları yaşanır hatta sorguların sonucunu alamayabiliriz. İşte bu nedenle mümkün olduğunca tabloları ayrı tutarız. Bu yüzden de primary ve foreign key alanları ortaya çıkmıştır.

Bu aşamada da tasarımın önemi ortaya çıkar. En çok hangi tablolar sorgulanıyorsa o tablolar birleştirilmelidir.

Eğer bir çalışanın adı ile departmanın adını sorgulamak istersek işte bu durumda join gerekmektedir. bunun için employees tablosu ile departments tablosunu join yapmamız lazım. Bunun içinde employees tablosundaki department_id sütunu ile departments tablosundaki department_id sütununu join etmemiz gerekmektedir.

Joinler natural join, self join, nonequijoinler, outer join (left, right ve full outer join) ve cross join olmak üzere 5 çeşittir.

 

Natural Join

İki tablodan veri çekmemiz gerekiyorsa join işlemi yapmamız gerektiği anlaşılmaktadır. Natural join çok kullanacağımız bir join türü değildir ancak değinmekte fayda var. Natural join yapmak için iki tabloda da aynı isimde ve aynı veri türünde sütunlar olması gerekmektedir. Aynı isimde olmaması gibi durumlar muhtemeldir. Bu nedenle çok tercih edeceğimiz bir join türü olmayabilecektir. Bu join türü sadece Oracle’a özel bir türdür. Natural joini örnekle inceleyelim.

Department_id ve department_name, location_id ve city sütunlarını sorgulamak istersek, natural join yöntemi ile bu işlemi aşağıdaki gibi yaparız.

SELECT                      department_id, department_name, location_id, city FROM                        departments NATURAL JOIN        locations;

 

Yukarıdaki sorguyu incelediğimizde natural join yöntemiyle bir sonuç döndü. İstediğimiz sütunları görüntülemiş olduk. İki tablonun birleştirilme işlemini bu şekilde gerçekleştirmiş olduk. Ancak unutulmamalı ki natural join işleminde mutlaka her iki tabloda da ortak veri tipine ve aynı isme sahip sütunlar bulunmalıdır. Küçük yapılarda bu tip bir sorgu mümkün olsa da, büyük veri tabanlarında aynı isimde sütunların olması pek mümkün görünmemekte. Tablo sayısı çoğaldıkça natural join sayısı da çoğalır.

SELECT                      department_id, department_name, location_id, city FROM                        departments NATURAL JOIN        locations NATURAL JOIN        ragions;

Yukarıdaki örnekte olduğu gibi eklenmeye devam eder. Veri tabanımızda primary key ve foreign key alanları aynı isimde ve aynı veri tipinde ise bu join tipini kullanabiliriz ki işimizi de kolaylaştırmış olacaktır.

 

JOIN USING

Eğer birleştireceğimiz tablolardaki ortak alanların isimlerini belirtmek istersek bu durumda using kelimesini kullanmamız gerekecektir. Örnekle açıklayalım.

 

SELECT                      department_id, department_name, location_id, city FROM                        departments NATURAL JOIN        locations USING                                   (location_id);

 

Dönen sonuç aynı olmakla beraber tek yapılan işlem ortak sütunun using kelimesi kullanılarak belirtilmesidir. Başka bir örnek daha yapalım.

 

SELECT                      employee_id, last_name, loacation_id, department_id FROM                        employees NATURAL JOIN        departments USING                                   (department_id);

 

Department_id sütununu belirterek natural join işlemini using kelimesini kullanarak yapmış olduk. Using kullanıyorsak alias atamaya gerek yoktur. Birazdan inceleyeceğimiz join türlerinde alias atamaları yapmaktayız. Eğer using kullanımı olan join türünü kullanıyorsak zaten kendisi ortak sütunları anlayarak işlem yaptığından sütun ya da koşul için kullandığımız sütun adlarının başına tablo isimlerini yazmak zorunda kalmayız.

 

 

JOIN ON:

Join on en çok kullanabileceğimiz kelimedir. Tabloları join etmek için on kelimesini kullanırız ve bu kelimeleri kullanırken mutlaka hangi (foreign key) alanların birleştirileceği belirtilmekle beraber select satırında yazılan sütunların da hangi tabloda olduğu belirtilmelidir. Syntax’ı aşağıdaki gibidir.

SELECT          tablo_adı | [alias].sütun_adı FROM            tablo_adı [alias] JOIN               tablo_adı [alias] ON                  (tablo_adı | [alias].sütun_adı = tablo_adı |[alias].sütun_adı);

Select satırında join on ile birleştirdiğimiz tabloları belirterek sütun adını yazarız. Ancak tablo adlarını yazmak uzun olacağından tablolara alias atayarak bu işlemler kolaylaştırılabilir. Ayrıca on satırında parantez içerisinde birleşecek sütunlarda tablo isimleriyle birlikte yazılmak zorundadır. Join on ile ilgili örnek yapalım.

 

 

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);

 

Yukarıdaki örneği incelediğimizde, employees tablosu ile departments tablosunu birleştirdik. Employees tablosuna e aliasını, departments tablosuna ise d aliasını atadık. Böylece select satırındaki her bir sütunun başına uzun uzun employees.  ya da departments. Yazmak zorunda kalmamış oluyoruz. Bu nedenle from satırında employees tablosunu e aliası ile yazmış olduk ve join satırında da departments tablosunu d aliası ile yamış olduk. On satırında da parantez içerisinde employees tablosu ile departments tablosunun ortak alanlarını yazdık ki yukarıdaki örneğe göre ortak alanlar department_id sütunlarıdır. Employees tablosunda departments tablosunun primary keyi olan department_id ile deptments tablosunun primary keyi olup employees tablosunda foreign key olan deparments_id tablosunu birleştirmiş olduk. Çünkü department_id unique (tekil) alandır. Oradaki veriler tekrar etmeyen verilerdir. Bu yüzden bu iki sütun ile birleştirmiş olduk. Ve select satırında yazmış olduğumuz sütunlar ise her iki tablodan çekilen sütunlardır. Ayrıca parantez kullanımı zorunlu değildir. Kullanmaya da biliriz.

Peki sadece iki tablomu birleşir tabi ki hayır. Join on çoğaltılarak yapılabilir. Örneklendirelim;

 

SELECT          e.first_name, e.last_name, d.department_name, l.city FROM            employees e             JOIN   departments d         ON      e.department_id = d.department_id             JOIN   locations l                 ON      d.location_id = l.location_id;

 

Yukarıda ki sorguda aynı anda 3 tablodan veri çekmek istedik. Daha fazla tablo için de her seferinde join on kullanılması gerekecektir.

İstersek ayrıca bir de koşul da koyabiliriz bunu için AND ya da WHERE kullanabiliriz. Örneklendirelim.

SELECT          e.first_name, e.last_name, d.department_name, l.city FROM            employees e             JOIN               departments d         ON      e.department_id = d.department_id             JOIN               locations l                 ON      d.location_id = l.location_id WHERE          e.manager_id = 149;

 

 

SELECT          e.first_name, e.last_name, d.department_name, l.city FROM            employees e             JOIN               departments d         ON      e.department_id = d.department_id             JOIN               locations l                 ON      d.location_id = l.location_id AND               e.manager_id = 149;

 

Yukarıda hem where hem de and ile aynı sonucu almış olduk.

Join on ANSI kurallarına göredir ve bütün veri tabanları bu syntax’ı destekler. Ayrıca AND koşulunu kullanarak daha performanslı sonuç alırız. Ancak birde bütün veri tabanlarının desteklemediği bir metod var. Onu da öğrenelim ki karşımıza çıktığında bu nedir diye düşünmeyelim. Join on’dan farklı olarak birde aşağıdaki yöntem var. Ayrıca join on da where ve and gibi koşullar konulabilmekte.

 

 

SELECT          d.department_id, e.last_name, l.city, c.country_name, r.region_name FROM            employees e, departments d, locations l, contries c, regions r WHERE          d.department_id     = e.deparment_id AND               d.location_id                        = l.location_id AND               l.country_id              = c.country_id AND               c.region_id               = r.region_id;

 

Yukarıdaki mantık ile join on mantığı aşağı yukarı aynı bazı farklılıkları var onlardan da bahsedeceğiz. Ama yukarıdaki join yönetimi ANSI kurallarına göre değil, her veri tabanına çalışmayacaktır. Ve ayrıca yukarıdaki join türünde uygulayacağımız koşul performans anlamında join on türüne göre daha çok performans harcayacaktır. Join on tavsiye edilir.

 

 

Self Join:

Tablolarımızı kendi içerisinde de join edebiliriz. Örnek employees tablomuzu ele alırsak, manager_id’leri başka bir tabloda oluşturmak yerine employees tablosu içerisine koyulmuştur. Manager_id’si 90 olan kişi dediğimizde employee_id’ine bakarız. Join işlemini ise manager_id ile employee_id arasında yaparız.

 

 

SELECT          emp2.last_name “Manager”, emp1.last_neme “Çalışan” FROM            employees emp1, employees emp2 WHERE          emp1.manager_id = emp2.employee_id

 

Yukarıdaki gibi sorgularda select satırındaki sıralama çok önemli.

 

Join işleminde ortak olanı olmayan tabloları da sorgulayabiliriz. Bu join türüne ise Nonequijoins denir.

 

Nonequijoin:

 

Orak sütunu olmayan tablolarda bir birine join edilebilir. Genelde bu işlemi yapmak için tablolardan biri lookup tablosu olur. Örnek olarak aşağıdaki iki tabloyu baz alalım.

Yukarıdaki tablolara baktığımızda biri employees tablomuz diğeri ise job_garden isimli bir tablo. Job_garden tablosuna baktığımızda employees tablosu ile hiç bağı bulunmamaktadır. Bu tip tablolara lookup tablolar deriz. amacı ise şudur. Employees tablosunda maaşlara bak job_garden tablosunda belirteceğimiz hangi aralığa denk geliyorsa grade levelini getir. Bir örnek ile anlayalım.

 

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;

 

Yukarıdaki sorguda employees tablosunun salary sütunu baz alınarak job_grades tablosunda hangi maaş aralığına geliyorsa grade_level’ini getir. Ancak bu tür sorgular kesinlikle tavsiye edilmez.

 

Outer Join:

Sorgularımızda eksiklik olmasını istemeyiz. Örneğin manager_id’si null olan bir kişi sorguda gelmeyebilir. Bu da bizim için eksik sorgu demektir. Bu sorunu düzeltmek için outer join’ler devreye girer. Outer join 3 tiptir. Left outer join, Right outer join ve Full outer join.

 

Left Outer Join: Bu join tipi çok kullanılmaktadır. Left sol taraftaki tablodaki bütün verileri getirmek için kullanılır. Bir örnek ile açıklayalım.

 

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;

 

Yukarıdaki sorguyu incelediğimizde departmanı olmayan bir kişiyi daha getirmiş oldu. Ve bu bizim için önemlidir. Çünkü eksik bilgi olmasını istemeyiz. Örnek tablolarımız için 1 eksik olabilir ancak büyük veri tabanlarında binlerce belki de daha fazla eksik bilgi oluşabilir. Bir örnek daha yapalım.

 

 

SELECT                      e.employee_id, d.department_name FROM                        employees e LEFT OUTER JOIN   departments d ON                              e.department_id = d.department_id;

 

Sorgunun sonucuna baktığımızda 107 kaydı görmekteyiz.

Right Outer Join: Left outer join’inin tam tersi de Right outer join’dir ki buda sol taraftaki tablodaki bütün bilgileri getirir.

 

SELECT                      e.employee_id, d.department_name FROM                        employees e RIGHT OUTER JOIN departments d ON                              e.department_id = d.department_id;

 

Bazı departmanlarımızın çalışanlarının olmadığını right outer join yaparak görmüş olduk. Yukarıdaki sorguda eşitliğin sol tafaında kalan yani d.department_id verilerinin tamamını getirmiş olduk.

 

Full Outer Join: Bütün tablolardaki verilerin tamamını getirmek için kullanılır. Left ya da right yazmak yerine full  yazılır ve iki tabloda ki bütün veriler getirilir.

 

Yukarıdaki örnekler hep ANSI yani bütün veri tabanlarında geçerli olan outer join’lerdi. Şimdi bir de oracle’a özel olanı inceleyelim. Oracle için özel olan outer join’de (+) parantez içinde artı işaretini kullanırız. Örnekle açıklayalım.

 

SELECT          emp2.last_name “Manager”, emp1.last_neme “Çalışan” FROM            employees emp1, employees emp2 WHERE          emp1.manager_id = emp2.employee_id(+);

 

Yukarıdaki örnek Left outer joinin karşılığıdır. Eşitliğin sol tarafındaki verilerin tamamını getirmek için yukarıdaki gibi sol tarafa, yani emp2.employee_id yanına (+) koyarız. Eşitliğin sağ tarafı için ise sol tarafa yani emp1.manager_id yanına (+) koyarız.

Cross Join: Tabloları birbirine karışık şekilde join olur. Bu join tipinde eşitlik yoktur. Her iki tablodaki her satırın birbiriyle birleşmesidir. Ki bu tip joinler veri tabanında planlanmamış şekilde belirlendiği an kill edilmelidir. Aksi takdirde performansı çok etkileyecektir. Örnek ile inceleyelim.

 

SELECT          last_name, department_name FROM            employees CROSS JOIN  departments

 

Yukarıdaki sorguya baktığımızda soy isim ve departman isimlerini sorgulayan bir sorgu bulunmakta ve her ismin yanına bütün departmanları tek tek yazmıştır. Bu da bizim istemeyeceğimiz performans kayıplarına neden olur. Peki neden var dersek cross join? Performans tesleri için kullanılır.