Şimdiye kadar sorgularımızda hep bir tablodan veri çektik. Yani FROM "tabloismi" ifadesini sadece bir tablo ile kullandık. Oysa buradaki tablo sayısını artırmak mümkündür. Çoğu zaman gelişmiş sorgularda tek tablo ile değil belki de üç tablo ile çalışmanız gerekecek. Bu gibi durumları bugün ele alalım. Öncelikle işi karmaşıklaştırmadan FROM ile nasıl birden fazla tablolarla çalışırız ona bakalım.
Şema üzerinde küçük açıklama yaparsak, aralardaki bağlantılar hangi tablonun hangi tablo ile ilişkili olduğunu gösteriyor. Bağlantıları takip ederseniz bağlantılı olan iki tabloda aynı sütun isminin ortak olduğunu görürsünüz. Bu sizin birden çok tablo ile çalışmanızı sağlamak içindir. Örneğin employees tablosunun birincil anahtarı olan mavi ile yazılan employee_id; aynı zamanda job_history'nin de birincil anahtarıdır. Yine jobs tablosunun birincil anahtarı olan job_id, employees tablosunda da olup foreign anahtar konumundadır.
Birden fazla tablo ile yapılan işlemler JOIN yani birleştirme işlemleri olarak anılırlar ve JOIN sözcüğü kullanılarak gerçekleştirilirler. Biz JOIN işleminin ayrıntılarına geçmeden önce bir kaç çoklu tablo ile işlem yapalım:
Yukarıda bahsettiğimizin dışında yani tablolar arasındaki ilişkileri gözetmeden bir sorgu tasarlayalım ve sonucu inceleyelim:
SELECT u.country_name, e.first_name
FROM countrıes u, employees e;
COUNTRY_NAME FIRST_NAME
---------------------------------------- --------------------
Argentina Ellen
Argentina Sundar
...
2675 rows selected
Gördüğünüz gibi sonuç korkunç ve anlamsız. Tüm işçileri (107 tane) tüm ülkeler (25 adet) ile kartezyen çarpım şeklinde karşımıza getirdi. Yani 107x25 = 2675. Yani aralarında ilişki bulunmadan tablolar ile sorgulama yapsanız da anlamsız sonuçlarla karşılaşırsınız.
NOT(Takma İsimler): Sorgudaki FROM kısmında tabloadi takmaisim (countries u) kullanımına dikkat ediniz. Birden fazla tablo ile çalışırken kafanızın karışmasını istemiyorsanız ve her defasında uzun uzun tabloismi.sütunismi yazımını kullanmak istemiyorsanız böyle takma isimlerle çalışın. Çünkü SQL'de birden fazla tablo ile çalışırken hangi sütunun hangi tabloya ait olduğunu bildirmek zorundasınız. Bu "önek" kullanımıyla ortadaki karmaşayı kaldırırsınız. Çünkü gördüğünüz gibi aynı sütun birden fazla tabloda ortak olabiliyor. Bu sayede hem performansı artırırsınız, hem sorgunun kalan kısmında uzun uzun tablo ismini yazmazsınız. İsimler kısalınca SQL'in saklandığı hafıza alanı da azalacaktır. Kısacası kullanın! :)
Şimdi daha mantıklı bir çoklu tablo işlemi yapalım. Mesela tüm işçilerin hangi departmanlarda çalıştığını görmek istersek ne yaparız. Sorgu aşağıdaki gibidir:
SELECT e.fırst_name || ' ' || e.last_name AS isim, d.department_name
FROM departments d, employees e
WHERE e.department_ıd = d.department_ıd;
ISIM DEPARTMENT_NAME
---------------------------------------------- ------------------
Jennifer Whalen Administration
Pat Fay Marketing
Michael Hartstein Marketing
Sigal Tobias Purchasing
...
106 rows selected
Öncelikle işimizin olduğu iki tabloyu belirledik: employees ve departments. Yukarıdaki HR şemasını kontrol ederseniz bu iki tabloda ortak olan bir sütun var. Yani bu iki tablo birbiri ile ilişkili. Sorguda da bu ilişkiyi kullanmak durumundayız. Bu ilişki her iki tabloda da department_id sütununun olması. Demek ki "hangi işçi hangi departmanda çalışıyor?" sorusunun cevabı burada gizli. Bu ilişkiyi ise WHERE şartındaki eşitlik ile sağlıyoruz. Temel olarak birden çok tablo ile çalışmanın yöntemi bu. Burada 107 işçi olmasına rağmen 106 sonucun görüntülenmesinin sebebi sanırım bir işçinin departmanının belirsiz olması. Bu yan gelip yatan çalışanın kim olduğunu bulalım hadi :)
SELECT e.fırst_name || ' ' || e.last_name AS isim
FROM employees e
WHERE e.department_ıd IS NULL;
ISIM
----------------------------------------------
Kimberely Grant
1 rows selected
Artık patrona kimi rapor edeceğinizi biliyorsunuz :)
Şimdi işleri biraz daha karıştıralım.
Mesela yöneticiniz sizden Canada ülkesinde çalışan işçileri yöneten müdürün bilgilerini istiyor diyelim. Nereden yola çıkarsınız? Nasıl işe başlarsınız? Görüldüğü gibi hiç de kolay olmayan bir sorgu...
"Öncelikle hangi tablolarla benim işim var?" sorusuna cevap ararsınız: Countries ve Employees. Sonra bu iki tablonun arasındaki bağlantıları kontrol edersiniz. Employees -> Departments -> Locations -> Countries. Yani 4 tablo üzerinden çalışmanız gerekecektir. Aklınıza şu soru gelebilir: Neden böyle temel ve önemli bir bilgiye ulaşmak bu kadar zor? Cevabı ilişkisel veritabanı modelinde gizli. İlişkisel veritabanlarında normalizasyon kurallarına göre her tablo sadece kendisi ile ilgili çekirdek sütunları tutar. Yani ülkelerin bulunduğu bir tabloda işçinin maaşı yada müdürün ismi olmamalı. Bu gibi bir tabloyu oluştursanız da bu bir görüntü (view) şeklinde diğer tabloları temel almalıdır. Görüntüler konusuna ileride geleceğiz.
Burada aslında uzun uzun JOIN işleminin faydalarını size sunmaya çalıştım. Gelin bu işlemi kendi kafamızdaki şimdiye kadar öğrendiğimiz bilgilerle yapalım. Yukarıdaki gibi bir mantıkla aşağıdaki sorgu tasarlanır:
SELECT e.manager_ıd, e.fırst_name, c.country_name
FROM countrıes c, employees e, locatıons l, departments d
WHERE c.country_ıd = l.country_ıd
AND l.locatıon_ıd = d.locatıon_ıd
AND e.department_ıd = d.department_ıd
AND c.country_name = 'Canada';
MANAGER_ID FIRST_NAME COUNTRY_NAME
---------------------- -------------------- ---------------------
100 Michael Canada
201 Pat Canada
2 rows selected
Görüldüğü gibi yapılan işlemler aynı. Tabloları belirledikten sonra kendinize bu iki tablo arasında bir yol çiziyorsunuz (Şemayı kullanarak). Daha sonra tabloları ikili olarak ayırdığınızda bu iki tablolarda hangi iki sütun ismi ortak buna bakıyorsunuz. Bunları belirledikten sonra tüm bu şartları WHERE ifadesinde bildiriyorsunuz. En sona da ülke ismini eleyerek çözmüş oluyoruz :)
Şimdi gelelim JOIN işlemine.
JOIN İşlemi
Birden çok tablodan veri sorgularken kullanılır. Öncelikle JOIN tiplerine bir bakalım:
- Natural Join
- Self Join
- Outer Join
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
- Cross Join
Şimdi tek tek bunların neler olduğunu inceleyelim.
1. NATURAL JOIN - INNER JOIN
Bizim yukarıda JOIN anahtar kelimesini kullanmadan yaptığımız JOIN türüdür. INNER JOIN olarak da ifade edilir. Varsayılan olarak gelen JOIN işlemi budur. Yukarıdaki mantıkla sağlanır. Yani aslında bu konuyu öğrendiniz. Yine de inceleyeceğiz. Bu türde, tablolarda aynı olan ortak sütunlar kullanılarak veriler çekilir. Şunu burada eklemekte fayda var: Sütunların ismi aynı olmasına rağmen eğer bu sütunlar farklı veri tiplerini içeriyorsa; JOIN işleminiz hatalıdır ve sorgu çalışmaz. Şuna da dikkat etmek gerekir ki NULL ifadeleri eşleştirerek JOIN işlemi yapmamak gerekir.
Bu JOIN tipinde iki ifade ile karşılaşırız: ON ve USING. ON az önce yukarıdaki WHERE ifadesinde eşleştirilen sütunları verdiğimiz kısımdır. Yani ortak olan sütun isimleri burada belirtilir. JOIN'lenecek tablo isimleri ise FROM sözcüğünden sonra aralarına NATURAL JOIN | JOIN | INNER JOIN ifadelerinden biri gelecek şekilde verilir. Yani birincitablo JOIN ikincitablo şeklinde.USING ifadesinde ise durum şudur: Eğer birden çok sütun aynı isme sahip olduğu halde veri tipleri uyuşmuyorsa, NATURAL JOIN; sütunları belirtmek için USING ifadesi kullanılarak uygulanabilir. USING ifadesi bir sütundan daha fazlası eşleştiğinde; sadece bir sütunu eşleştirmek için kullanılır.
Şimdi yukarıda yaptığımız iki örneğin bu JOIN tipi ile verilmiş sorgularını yazalım. Sonuçları vermeye gerek yok. Aynı sonuçlar listelenecektir.
Hangi işçi hangi departmanda çalışıyor?
SELECT e.fırst_name || ' ' || e.last_name AS isim, d.department_name
FROM departments d INNER JOIN employees e ON e.department_ıd = d.department_ıd;
Canada'da çalışanların yönetici id'si:
SELECT e.manager_ıd, e.fırst_name, c.country_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id
JOIN countrıes c
ON c.country_ıd = l.country_id
WHERE c.country_name = 'Canada';
Bu yazım tarzını iyice incelemenizi öneririm. Yola çıktığınız tablodan itibaren ikili JOIN'ler yaparak son tabloya erişiyorsunuz. Yani biz bu örnekte 4 tablo kullandıysak 3 adet JOIN işlemi yapmanız gerekiyor ve JOIN'ler birbirini takip etmek durumunda.
Bazı Notlar(ÖNEMLİ!)
- JOIN işlemleriyle birlikte ON ifadesini kullanmak zorunda değilsiniz. Birinci örneği şu şekilde de oluşturabilirdik:
SELECT e.fırst_name || ' ' || e.last_name AS isim, d.department_nameFROM departments d JOIN employees eUSING (department_id);
Belirttiğimiz gibi USING ifadesi eşleştirilecek sütunları belirtmek için kullanılır ve aynı olan birden fazla sütun olduğunda karmaşanın önlenmesini sağlar. Yani ON yerine USING kullanmış olduk.
Yine aynı sorguyu NATURAL JOIN ifadesi ile yazarsak şöyle olur:SELECT fırst_name || ' ' || last_name AS isim, department_name, department_ıd
FROM departments NATURAL JOIN employees;
Tek sütun ortak olduğu için USING kullanmanıza gerek yoktur. Eğer birden fazla sütun ortak olsaydı bu kez USING ifadesi kullanılacaktı. - Dikkat edilecek bir diğer husus bir SQL ifadesinde USING kısmında takma isimler kullanılamaz. Yani tabloismi.sütunismi kullanımına izin verilmez. Sadece ilgili sütun ismine yer verilir. Aynı şekilde takmaisim.sütunismi de kullanılamaz. Bu durumlara dikkat edilmelidir. Bir SQL ifadesinde bir sütun bir başka yerde daha kullanılacaksa o sütuna takma isim vermemelisiniz. SELECT l.city, d.department_nameFROM locations l JOIN departments dUSING (location_id)WHERE d.location_id = 1400;Bu sorgu hatasız gibi görünüyor ama WHERE kısmında location_id'nin tekrar kullanılması problem oluşturur. Bu kısmı kaldırırsak sorgu sorunsuz çalışır. Bu gibi durumlarda takma ismi problem oluşturabilir.
- Bir JOIN ifadesinde JOIN şartlarını ON ifadesi altında yazıyoruz. Ekstra şartları da WHERE ifadesi altına yazıyoruz. Uzun uzun WHERE yazmak istemiyorsanız ON joinşartı AND diğerşartlar şeklinde AND ile de yazabilirsiniz.
2. SELF JOIN
Bir tablonun kendisi ile JOIN işlemine tabi tutulmasıdır. Çok fazla kullanılmayan bir birleştirme işlemidir. Bazen gerektiğinde bir tablo üzerinde verilerle karşılaştırma işlemi uygulamak istediğinizde kullanırsınız. Basit bir örnek ile anlayalım.
Aslında normal JOIN işleminden farksızdır. Çünkü employees tablosundan iki tane olduğunu düşünürsek bu iki tablonun da employees_id sütununun ortak olmasından yola çıkarak birleştirme işlemi yapılacaktır. Aşağıdaki sorguyu inceleyelim:
SELECT isci.last_name "işçi", yonetici.last_name "Yönetici"
FROM employees isci JOIN employees yonetici
ON (isci.manager_id = yonetici.employee_id);
işçi Yönetici
------------------------- -------------------------
Smith Cambrault
Ozer Cambrault
...
106 rows selected
Buradaki örnekle bu işlemin kullanışlı olabileceğini görmüş oluyoruz. Çünkü aynı tabloda hem çalışanlar hem de yöneticiler aynı sütunlarda tutuluyor. Bu sütunları nasıl ayırt edip, "hangi çalışanı kim yönetiyor?" sorusuna cevap ararken etkili bir çözüm yolu olan Self-Join işlemini kullanabilirsiniz.
3. OUTER JOIN
Bu JOIN tipi biraz daha farklı olarak direkt eşleşmeyen sütunları içeren tablolarda yapılan JOIN işlemidir. Birleştirme işleminin uyuşmadığı satırlara NULL değerler oluşturarak sonuçları gösterir. Bu tip birleştirme işlemlerinde ikiden fazla tablo kullanımına izin verilmez. Birleştirmenin hangi tabloyu baz alacağına göre üç kısma ayrılır:
3.a. Left Outer Join
LEFT OUTER JOIN ifadesinin solunda kalan tablo ismi baz alınır ve bu tablodaki birleştirmeye giren sütuna ait tüm değerler sonuçta gösterilir. Sağda kalan tablonun ise sadece soldaki tablo ile ilişkili (eşleşmiş) satırları sonuca girer. Eğer sağdaki tabloda hiç bir veri soldaki tablo ile ilişkili değilse o zaman yine soldaki tablodaki veriler karşımıza gelir. Kısaca LEFT JOIN olarak kullanılır. Şimdi hatırlarsak employees tablosundaki 190 nolu bölümde çalışan bulunmuyordu. Şimdi bu kısmı baz alarak bir sorgu yazalı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) ;
LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
------------------------- ---------------------- ----------------
Whalen 10 Administration
Fay 20 Marketing
...
Higgins 110 Accounting
Grant
...
107 rows selcted
Sonuçlardan görüldüğü üzere employees'teki boş bölüm sağdaki departments tablosunda bir eşleşme olmamasına rağmen sonuçlarda gözlenmiştir.
3.b. Right Outer Join
RIGHT OUTER JOIN ifadesinin sağında kalan tablo ismi baz alınır ve bu tablodaki birleştirmeye giren sütuna ait tüm değerler sonuçta gösterilir. Solda kalan tablonun ise sadece sağdaki tablo ile ilişkili (eşleşmiş) satırları sonuca girer. Eğer soldaki tabloda hiç bir veri sağdaki tablo ile ilişkili değilse o zaman yine sağdaki tablodaki veriler karşımıza gelir. Kısaca RIGHT JOIN olarak kullanılır. Yukarıdaki ile aynı örnek verilebilir.
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) ;
LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
------------------------- ---------------------- ----------------
Whalen 10 Administration
...
Higgins 110 Accounting
Treasury
Corporate Tax
...
122 rows selected
Bu kez görüldüğü gibi sonuçlarda sol kısımdaki değerler NULL olarak çekildi. Yani tüm sonuçların gösterildiği tablo sağdaki departments tablosu oldu. derpatments ile eşleşen employees değerleri gösterilirken, eşleşmeyen kısımlarda ise sadece departments'taki veriler gösterildi.
3.c. Full Outer Join
FULL OUTER JOIN ifadesinin her iki tarafında kalan tablo isimleri baz alınır ve bu tablolardaki birleştirmeye giren sütuna ait tüm değerler sonuçta gösterilir. Kısaca FULL JOIN olarak kullanılır. Yukarıdaki aynı örneği verelim:
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) ;
...
123 rows selected
Sonuçları vermeye gerek duymadım zira sonuçtaki satır sayısı sonucu açıklıyor. Bu kez hem sağda hem solda eşleşmeyen değerler aynı anda sonuçta yer aldı. Left Joini hatırlarsanız sadece 1 değer NULL olarak gösterildi. Right join de ise sonuç 122 idi. Bu ikisinin toplamı da bize full join'i vermiş oldu.
4. Cross Join(Kartezyen Çarpım)
Aslında buna örnek vermiştik. Yazının girişindeki ilk örneği hatırlayın. 2675 sonuç veren örnekte olası bütün eşleştirme sonuçlarını bize göstermişti. Bu aslında istenmeyen bir sonuçtur. Bu sonuçlardan kaçınmak için diğer JOIN tipleri kullanılır. Aynı işlemi CROSS JOIN anahtar kelimesiyle yapalım:
SELECT u.country_name, e.first_name
FROM countrıes u CROSS JOIN employees e;
...
2675 rows selected
Sonuç aynıdır. Kartezyen çarpımda bütün olasılıklar karşınıza dizilir. Mesela iki grubunuz olsun: A, B ile 1, 2, 3. Bu iki grubun kartezyen çarpımı sonucu oluşan sayı 2x3 = 6 dır ve elemanlar da : A-1, A-2, A-3, B-1, B-2, B-3'tür. Sonuçlar bu mantığa göre karşınıza gelir. Bu sonucu şu durumlarda görürsünüz:
- JOIN işleminizin şartı geçersizse
- JOIN şartı atlanmışsa
- İki tablonun kartezyen çarpımı alındıysa
Burada JOIN konusunu bitirmiş oluyoruz. Biraz uzun oldu ama tam anlamıyla anlatıldığını düşünüyorum. Herkese kolay gelsin...
Hiç yorum yok:
Yorum Gönder