31 Temmuz 2012 Salı

Join İşlemi - Outer Join (Left Outer Join, Right Outer Join ve Full Outer Join)


Giris
Bu makalemizde birden fazla tabloda bulunan birbiri ile iliskili verileri iliskilerine göre tek bir sorgu ile nasil çekecegimize bakacagiz.
Microsoft SQL Server gibi veritabanlari verileri birbiri ile iliskili bir sekilde farkli tablolarda tutmamizi saglarlar. Bu sayede büyük verileri tek bir tabloda tutmak yerine iliskisel olarak farkli tablolara dagitabiliriz. Bu bize performans ve daha kolay anlasilir bir yapi olarak geri döner. Örnegin bir satis sisteminde Müsteri bilgileri ve ürün bilgileri farkli iki tabloda tutulur. Bunlarla iliskili üçüncü bir tabloda ise müsterinin siparis bilgileri tutulur. Eger iliskisel bir yapi kurmazsak siparis tablosunda müsteri ve ürün ile ilgili ihtiyaç duydugumuz tüm verileri tutmak zorunda kaliriz ki buda siparis tablomuzun kolon sayisini artiracagi gibi veriler arasinda bütünlük saglanamamasi gibi büyük sorunlara yol açabilir. Bunun yerine siparis tablosunda müsteri ve ürünleri tanimlayacak birer tanimlayici ID alani tutmamiz yeterli olacaktir. Verilerin bu sekilde iliskili olarak tutulmasi ise, müsterilerin vermis oldugu siparislerin detayi ile ilgili bir sorgu yazmamiz gerektiginde daha karmasik sorgular yazmamizi gerektirecektir.  Bu makalemizde bu tür tablolardan verileri tek bir SQL cümlesi ile nasil çekecegimize deginecegiz.
Birden Fazla Tabloda Tutulan Iliskisel Verileri Almak
Yukarda bahsettigimiz gibi siparis bilgilerinin tutuldugu bir veritabanimiz olsun. Bu veritabaninda müsteri, ürün ve siparis bilgilerini tutmak için 3 farkli tablo kullanalim. 1. tablo müsteri bilgilerini 2. tablo ürün bilgilerini 3. tablo ise 1. ve 2. tablodaki ID leri iliskilendirerek hangi müsterinin hangi ürünü aldigi bilgisini tutsun. Örnegimizde kullanacagimiz basit veritabanini kullanmak için asagidaki SQL scriptini kendi SQL serverinizda çalistirin. Sorguda [VeritabaniIsmi]yerine kullanacaginiz veritabani ismini yazmayi unutmayin.
Müsteri Tablosu için;
USE [VeritabaniIsmi]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Musteri](
      [MusteriKey] [uniqueidentifier] NOT NULL,
      [Ad] [nvarchar](50) NULL,
      [Soyad] [nvarchar](50) NULL,
      [TelNo] [char](10) NULL,
      [Adres] [nvarchar](200) NULL,
 CONSTRAINT [PK_Musteri] PRIMARY KEY CLUSTERED
(
      [MusteriKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Musteri] ADD  CONSTRAINT [DF_Musteri_MusteriKey]  DEFAULT (newid()) FOR [MusteriKey]
GO
Ürün Tablosu için;
USE [VeritabaniIsmi]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Urun](
      [UrunKey] [uniqueidentifier] NOT NULL,
      [UrunAdi] [nvarchar](50) NULL,
      [Fiyat] [decimal](6, 2) NULL,
      [Açýklama] [nvarchar](50) NULL,
 CONSTRAINT [PK_Urun_1] PRIMARY KEY CLUSTERED
(
      [UrunKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Urun] ADD  CONSTRAINT [DF_Urun_UrunKey]  DEFAULT (newid()) FOR [UrunKey]
GO

Siparis Tablosu için;
USE [VeritabaniIsmi]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Siparis](
      [SiparisKey] [uniqueidentifier] NOT NULL,
      [SiparisNo] [int] NULL,
      [MusteriKey] [uniqueidentifier] NULL,
      [UrunKey] [uniqueidentifier] NULL,
      [Adet] [smallint] NULL,
      [Tarih] [datetime] NULL,
 CONSTRAINT [PK_Siparis] PRIMARY KEY CLUSTERED
(
      [SiparisKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Siparis] ADD  CONSTRAINT [DF_Siparis_SiparisKey]  DEFAULT (newid()) FOR [SiparisKey]
GO
Yukaridaki scriptleri çalistirdi iseniz veritabaniniz da örnegimizde kullanacagimiz tablolar olusmus demektir.

 
Inner Join
Birden fazla join türü vardir. Bu makalemizde Inner Join islemine deginecegiz.  Inner Join birlestirilen tablolarda iliskinin saglandigi tüm kayitlari getirir. Simdi Müsterilerin vermis oldugu siparislerin tarihleri getirelim.
select m.Ad,m.Soyad,m.Adres,s.Tarih from Musteri m inner join Siparis s on m.MusteriKey = s.MusteriKey
Yukaridaki sorguyu çalistirdigimizda asagidaki bilgiler gelir. Gelen sonuç tablolara kaydettiginiz verilere göre degisiklik gösterebilir.
Ad
Soyad
Adres
Tarih
Mehmet Sabri
KUNT
Dikmen/Ankara
2009-01-13 09:00:00.000
Seher
KUNT
Dikmen/Ankara
2009-01-13 14:00:00.000
Seher
KUNT
Dikmen/Ankara
2009-01-13 09:00:00.000

Yukaridaki sorguya göre müsteri tablomuzda olup hiç siparis vermemis olan müsteriler gelmez. Çünkü Inner Join isleminde sadece iki tabloda da ayni degere sahip olan bilgiler gelir. Örnegimizde Siparis ve Müsteri tablolarini MusteriKey ile birlestirdigimize göre Müsteri tablosunda ve Siparis tablosunda olan ayni MüsteriKey’ler birlestirilerek getirilir. Hiç siparis vermemis bir müsterimize ait MusteriKey siparis tablosunda olmayacagi için sorgu sonucunda bu müsterimize ait bir kayit dönmez. Yukaridaki join sorgusunda Musteri ve Siparis tablolarini yer degistirerek de kullanabilirdik. Iki durumda da sonuç degismezdi.
select m.Ad,m.Soyad,m.Adres,s.Tarih from  Siparis s inner join Musteri  m on m.MusteriKey = s.MusteriKey
Join sorgumuzda her iki tabloda da MusteriKey’leri esit olan kayitlari getirdik. Join isleminde iki tablo arasinda iliski kurarken seçecegimiz alanin primary ve veya foreign key olma zorunlulugu yoktur.  Veri tipi uyumlu olan herhangi iki alani iliskilendirerek sorgumuzu yazabiliriz. Tabi bu iliski mantikli olmali.  Bir foreign key alani ile iliski kurarsak sorgu performansimiz artar, ayrica text alanlar yerine int gibi alanlarin birlestirilmesi tavsiye edilir. Primary Key alanlar üzerinde birlestirme yapmak bize karsilastirdigimiz bilginin mükerrer olmadigini garantiler çünkü primary key alanlari unique olmak zorundadir. Inner Join ifadesi asagidaki gibi where kelimesi kullanilarak da yazilabilir.
select m.Ad,m.Soyad,m.Adres,s.Tarih from  Musteri m,Siparis s where s.MusteriKey = m.MusteriKey
Yukaridaki örnegimizde Siparis ve Musteri tablolarini birlestirerek müsterinin vermis oldugu siparislerin tarihlerini bulduk, simdide veritabanimizdaki 3 tabloyu da birlestirerek müsterinin siparisi ile ilgili ürün bilgisi de dahil tüm bilgileri alalim. Sorgumuzun yeni hali;
select m.Ad,m.Soyad,m.Adres,s.SiparisNo,u.UrunAdi,u.Fiyat from  Siparis s inner join Musteri  m onm.MusteriKey = s.MusteriKey
inner join Urun u on s.UrunKey = u.Urunkey
Yukaridaki sorgu sonucu asagidaki kayitlar döner.
Ad
Soyad
Adres
SiparisNo
UrunAdi
Fiyat
Mehmet Sabri
KUNT
Dikmen/Ankara
123
HP Pavilion dv2100
1400.00
Seher
KUNT
Dikmen/Ankara
123456
Samsung 19 inc LCD
250.00
Seher
KUNT
Dikmen/Ankara
123456
Samsung D880
410.00

Sorgunun sonucuna bakarsak Siparis tablosunda bulunan kayitlara ait musterikey ve urunkey lere ait musteri ve urun tablosundaki kayitlar getirilir ve hepsi tek bir satirda birlestirilir. Inner join isleminde musteri tablomuzda veya urun tablomuzda kaç adet kayit oldugunun önemi yoktur. Bizim için önemli olan Siparis, Musteri ve Urun tablosunda iliskilendirdigimiz alandaki bilgilerin ayni oldugu kayitlardir. Ara tablomuz siparis olduguna göre Siparis tablosunda bulunan MusteriKey alaninin Musteri tablosundaki karsiligi veya UrunKey alaninin Urun tablosundaki karsiligi önemlidir.  Eger siparis tablosunda olmasina ragmen Musteri tablosunda bu MusteriKey ile ilgili bir kayit yoksa bu kayit sorgumuzun sonucuna yansimaz.  Siparis tablosundaki bir kayittaki musterikeye ait bilgi musteri tablosunda  ayrica UrunKeye ait bilgide Urun tablosunda olmaliki bu siparis bilgisi sorgu sonucumuzda görünsün. Bu çok dikkat etmemiz gereken bir durumdur. Mesela bir müsteriye abc ürünü satildi daha sonra bu abc ürünü Urun tablosundan silindi ise Inner Join ile yapacagimiz sorgularda Siparis tablosunda bulunan bu kayit da ki UrunKey Urun tablosunda bulunamadigi için bu siparis bilgisi sorgu sonucumuza gelmez. Çekmek istediginiz bilgiye göre bu durum önemli olabilir.
Giris
Birinci makalemizde join islemlerine giris yapmistik. Join isleminin avantajlari ve dezavantajlari hakkinda konusmustuk. Birbiri ile iliskili tablolardan olusan bir veritabaninin tek ve büyük bir tablo yapisi ile kurulmus bir veritabanindan daha performansli ve islevsel oldugundan bahsetmistik. Ayrica birbiri ile iliskili tablolardan tek bir sorgu ile birlesik veri çekmek için join islemi kullanmamiz gerektigini daha önceki makalemizde anlattik. Önceki makalemize buradan erisebilirsiniz.  Bu makalemizde önceki makalemizde olusturdugumuz veritabanini kullanacagiz.
                Veritabani yapimiz yukaridaki diyagramda gösterilmistir. Tablolara istediginiz verileri kendiniz ekleyin.
                Join isleminde tablolari baglamak için integer veya bir uniqueidentifier veri tipindeki alanlara ihtiyacimiz yok. Istersen varchar tipinde Ad alanini da kullanarak Join islemini yapabiliriz. Ayrica Joinde bagladigimizi alanlarin primary veya Foreign Key de olmasi sart degil. Fakat integer veya Key alanlarin birbiri ile baglanmasi sonucu olusturulan Join islemleri daha performansli çalistigini da söylemek gerekir. Çok büyük verilerin bulundugu tablolarda bu önemli bir noktadir.
Önceki makalemizde iner join kullanarak siparis vermis olan müsterilerimizi ve siparis bilgilerini tek bir satir halinde göstermistik, inner Join hakkinda bilgi vermistik siradaki örnegimizde left outer join kullanarak siparis versin vermesin tüm müsterilerimizi ve siparis vermis olanlarin siparis bilgilerini listeleyecegiz.
LEFT OUTER JOIN
Inner Join de iliskilendirilen iki tablonun her ikisinde de uyusan kayitlar getirilirdi. Outer joinde ise tablolardan birinde olan tüm kayitlar ve ikinci tabloda bu kayitlarla iliski olanlar getirilir. Ikinci tabloda iliskili bir kayit yoksa birinci tablodaki veriler yinede getirilir. Bu örnekte siparis versin vermesin tüm müsterilerimizi ve siparis bilgileri varsa ayrica bu bilgileri gösterecegiz.
select m.Ad,m.Soyad,m.Adres,s.SiparisNo from  Musteri  m left outer join   Siparis s   onm.MusteriKey = s.MusteriKey
Yukaridaki sorguya dikkat ederseniz fromdan sonra Musteri tablosu var left outer join ifadesinden sonrada Siparis tablosu var. Bu dizilim müsteri tablosundaki her kayit için uyusan siparis bilgilerini getir. Eger uyusan yoksa müsteriyi yinede göster anlamina gelir. Eger müsteri ve siparis tablolarini yer degistirirseniz her siparis için müsteri bilgilerini getir, siparis veren müsteri bilgisi olmasa bile siparis bilgisini göster anlamina gelir. Yani buradaki tablolarin kullanim sirasi çok önemlidir.
Ad
Soyad
Adres
SiparisNo
Seher
KUNT
Dikmen/Ankara
123456
Seher
KUNT
Dikmen/Ankara
123456
Seher
KUNT
Dikmen/Ankara
123456
Mehmet Sabri
KUNT
Dikmen/Ankara
123
Metin
TOSUN
Balgat/Ankara
NULL

Yukaridaki sorgu sonucu gelen kayitlar yukaridaki gibidir.  Dikkat ederseniz Metin Tosuna ait bir siparis bilgisi olmamasina ragmen Metin Tosun adli müsteri listede yer almistir. Inner Join ile outer joinin en önemli farki burasidir. Inner join için önceki makalemize bakiniz.
Birden fazla tablodan kayit çekerken dikkat etmemiz gereken noktalardan biriside her iki tabloda da ayni isimle bulunan kolonlari listede göstermek istedigimizde hangi tablodaki kolonu göstermek istedigimizi belirtmek zorunda olmamizdir. Örnegin;
select m.Ad,m.Soyad,m.Adres,s.SiparisNo,MusteriKey from  Musteri  m left outer join   Siparis s   onm.MusteriKey = s.MusteriKey
Yukaridaki sorguya dikkat ederseniz MusteriKey kolonu select ifadesine eklenmistir. Bu sorguyu çalistirdigimizda SQL Server bize asagidaki hayati verir.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'MusteriKey'.

Bu hatanin sebebi MusteriKey kolonunun hem Musteri tablosunda hem de Siparis tablosunda bulunmasidir. SQL Server hangi tablodaki kolona ait bilgileri gösterecegini bilemedigi için size yukaridaki hatayi verir. Hatani çözümü ise çok basittir. Sadece kolonun önüne tablonun adini yazmak yeterlidir. Biz sorgumuzda Musteri tablosunu m ile ifade ettigimiz için m.MusteriKey yazmamiz sorgunun çalismasina için yeterli olacaktir.
RIGHT OUTER JOIN
                Right outer join tüm islevselligi ile Left outer join ile aynidir. Aralarindaki tek fark sola yazilan tablodaki tüm veriler degil saga yazilan tablodaki tüm veriler listelenir.
Yukardai yazdigimiz siparis bilgisi olan veya olmayan tüm müsteriler ve olanlarin siparis bilgilerini de getirdigimiz sorgunun right outer join ile yazilmis hali ise söyledir;
select m.Ad,m.Soyad,m.Adres,s.SiparisNo from   Siparis s right outer join Musteri  m  on m.MusteriKey =s.MusteriKey
Sorgunun sonucu asagidaki gibidir.
Ad
Soyad
Adres
SiparisNo
Seher
KUNT
Dikmen/Ankara
123456
Seher
KUNT
Dikmen/Ankara
123456
Seher
KUNT
Dikmen/Ankara
123456
Mehmet Sabri
KUNT
Dikmen/Ankara
123
Metin
TOSUN
Balgat/Ankara
NULL

FULL OUTER JOIN
Son outer join tipi olan full outer join ise join isleminde birbirine baglanan her iki tabloda bulunan kayitlari da getirir. Yani hem müsteri tablosunda siparis veren ve vermeyen, hem de siparis tablosunda müsteri bilgisi olan ya da olmayan kayitlari listelemek istersek;
select m.Ad,m.Soyad,m.Adres,s.SiparisNo from  Musteri  m full outer join   Siparis s   on m.MusteriKey =s.MusteriKey
Ad
Soyad
Adres
SiparisNo
Seher
KUNT
Dikmen/Ankara
123456
Seher
KUNT
Dikmen/Ankara
123456
Seher
KUNT
Dikmen/Ankara
123456
Mehmet Sabri
KUNT
Dikmen/Ankara
123
Metin
TOSUN
Balgat/Ankara
NULL
NULL
NULL
NULL
123456

Sorgu sonucuna dikkat ederseniz Müsteri bilgisi olmadigi halde siparis tablosundaki kayitlar ve siparis bilgisi olmadigi halde müsteri tablosundaki kayitlar dahil tüm veriler listelendi.
Bu makalemizde Outer Join hakkinda bilgi vermeye çalistim. Bir sonraki makalemizde görüsmek üzere.

Önceki makalelerde bahsettiğimiz gibi join işlemi iki veya daha fazla tablodan veri getirme işlemi için kullanılır. Veritabanımız daki her bir tabloyu matematik dersinde gördüğümüz kümelere benzetmek yanlış olmaz. Kümeler üzerinde kesişim, birleşim, fark gibi işlemleri hatırlıyoruzdur. Join işlemlerini de matematikteki bu ifadelerle anlatmanın daha anlaşılır olacağını düşünüyorum.
INNER JOIN
İner join her tabloda da aynı olan kayıtları verir. SQL de varsayılan join şekli inner joindir.
select * from Tablo1 t1
inner join Tablo2 t2 on t1.kol1 = t2.kol1

www.yazilimmutfgi.com - inner join.jpg

Resimde gördüğümüz gibi iner join iki kümenin kesişimini verir. Küme sayısı ikiden büyük olabilir.

OUTER JOIN
Outer join iki tablodaki ilişkili olmayan kayıtları da döndürmemizi sağlar. Outer join işleminin 3 farklı çeşidi vardır.
Left Outer Join
select * from Tablo1 t1
left outer Tablo2 t2 on t1.kol1 = t2.kol1

Birinci tablodaki tüm kayıtlar getirilir, buna karşın ikinci tabloda bu kayıtlarla uyuşan kayıtlarda gelir.
www.yazilimmutfgi.com left outer join.jpg

Right Outer Join
İkinci tablodaki tüm kayıtlar getirilir, buna karşın birinci tabloda bu kayıtlarla uyuşan kayıtlarda gelir.
select * from Tablo1 t1
right outer Tablo2 t2 on t1.kol1 = t2.kol1

www.yazilimmutfgi.com right outer join.jpg
Full Outer Join
Full outer joinde iki tablodaki tüm kayıtlar uyuşsun yada uyuşmasın getirilir.
select * from Tablo1 t1
full outer Tablo2 t2 on t1.kol1 = t2.kol1

www.yazilimmutfgi.com full outer join.jpg

Ek bilgiler
Outer join kullanarak kolayca yapabileceğiz bazı yöntemleri burada anlatacağım.
a - İki tablomuz var ve biz birinci tablonun ikinci tablodan farkını almak istiyoruz, yani birinci tabloda olan ama ikinci tabloda olmayan kayıtları alacağız.
Bunu iki şekilde yapabiliriz
SELECT  t1.*
FROM Table1 t1
WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)

Yukarıdaki klasik sorgu kullanımı yerine aynı işlemi aşağıdaki gibi left join ilede yapabiliriz.
SELECT t1.*,t2.*
FROM Table1 t1
LEFT OUTER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

Left outer join ile yazdığımız sorgunun ilk sorguya göre çok daha hızlı çalışacağına emin olabilirsiniz.
www.yazilimmutfgi.com right outer join where null.jpg
Yukarıdaki resmi incelersek yazdığımız sorgunu Tablo1 fark Tablo2 aldığını kolayca görebiliriz.


b- inner join ile iki kümenin kesişen kısmını aldığımız söylemiştik peki iki kümenin kesişen kısmı dışında kalan kısmı nasıl alabiliriz.
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID is NULL or t2.ID IS NULL

Sorgu sonucunca aşağıdaki resimde gösterildiği gibi sonuç döner.
www.yazilimmutfgi.com full outer join where null.jpg
Umarım join işleminin mantığını kafanızda oturtmanızda yardımcı olabilmişimdir.


Hiç yorum yok:

Yorum Gönder