Çoklu Tablolardan Sorgulamalar

10. Hafta
Çoklu Tablolardan Sorgulama Yapma
BPR255 Veritabanı
Yönetim Sistemleri-II
Bu Derste Öğrenecekleriniz:
1- Birden Fazla Tablodan Sorgulama
2- Tablo Birleştirme işlemleri (JOIN)
a. INNER JOIN
b. OUTER JOIN
i. LEFT OUTER JOIN
ii. RIGHT OUTER JOIN
iii. FULL OUTER JOIN
3- Tablo Ekleme işlemleri (UNION)
4- Sorgulamaları VIEW (sanal tablo) olarak kaydetme
Aşağıdaki şemaya dikkat edin. Sorgulamalarımızı genellikle bu şemaya göre yapacağız.
Aşağıdaki sorgulama örneğine dikkat edin:
SELECT
B.[BÖLÜM KODU], S.[DERS KODU], D.[DERS ADI],
AVG(S.VİZE) AS [VİZE SINIF ORTALAMASI]
FROM
WHERE
SINAVLAR AS S, ÖĞRENCİLER AS O, BÖLÜMLER AS B, DERSLER AS D
S.[ÖĞRENCİ NO] = O.NUMARA AND S.[DERS KODU] = D.[DERS KODU]
AND B.[BÖLÜM KODU] = O.BÖLÜM
GROUP BY
HAVING
ORDER BY
S.[DERS KODU], D.[DERS ADI], B.[BÖLÜM KODU]
AVG(S.VİZE)>=60
[DERS KODU] DESC
Bu sorgunun ne anlama geldiği şimdilik sizin için bir şey ifade etmiyor olabilir. Ama bu karışık sorgulamanın
ne anlama geldiğini hep beraber öğreneceğiz.
1
Bartın MYO – Bilgisayar Programcılığı | Öğr. Gör. Bayram AKGÜL | 2013-2014 Güz Dönemi
10. Hafta
Çoklu Tablolardan Sorgulama Yapma
BPR255 Veritabanı
Yönetim Sistemleri-II
1. Birden Fazla Tablodan Sorgulama Yapma
Birden fazla tablodan sorgulama yapmak Kartezyen çarpım yapmak gibidir. Örneğin normalde öğrenciler
tablosunda 100 öğrenci bölümler tablosunda da 15 bölüm olduğunu düşünürsek aşağıdaki sorgulama
sonucu 100*15=1500 satır olacaktır.
SELECT *
FROM
ÖĞRENCİLER, BÖLÜMLER
Sorgu Sonuçlarından bazıları:
Dikkat ederseniz tabloda bölüm kodu iki defa görüntülenmiştir. Bunların birisi öğrenciler
tablosundan, diğeri ise bölümler tablosundan gelmektedir. Ve yine dikkat ederseniz bazı kayıtlar
tekrarlanmış gibidir. Çünkü iki tablo Kartezyen olarak çarpılmıştır.
Aslında bu tür bir sorgudan öğrenmek isteyeceğimiz öğrencilerin bölüm adıdır. Bu durumda
öğrenciler tablosundaki öğrencinin hangi bölümün öğrencisi olduğunu gösteren bir referans anahtarımızı
2
Bartın MYO – Bilgisayar Programcılığı | Öğr. Gör. Bayram AKGÜL | 2013-2014 Güz Dönemi
10. Hafta
Çoklu Tablolardan Sorgulama Yapma
BPR255 Veritabanı
Yönetim Sistemleri-II
kullanacağız. Öğrenciler tablosundaki öğrencinin hangi bölümün öğrencisi olduğunu gösteren referans
anahtar BÖLÜM alanıdır. Öyle ise referans anahtarımız ile kendisinden referans alınan alanın eşit olduğu
durumlar gerçek kayıtlar olacaktır.
Bu durumda tablodaki iki bölüm kodu bir birine eşit olan alanlara dikkat edin. Bizim sadece bu
satırları seçmemiz gerekir. Bunu yapmak için koşul olarak öğrenciler tablosundaki bölüm kodunun bölümler
tablosundaki bölüm koduna eşit olma şartını arayacağız. Buna tablo birleştirme diyoruz. Bunun sorgusu
aşağıdaki gibi yazılır.
SELECT
FROM
WHERE
*
ÖĞRENCİLER, BÖLÜMLER
ÖĞRENCİLER.BÖLÜM = BÖLÜMLER.[BÖLÜM KODU]
Sorgulama Sonucu:
Hala tabloda bölüm kodu iki defa var! Bundan kurtulmak için * yerine direk olarak görmek istediğimiz alan
isimlerini yazmamız gerekiyor.
SELECT
FROM
WHERE
NUMARA, ADI, SOYADI, [BÖLÜM KODU], [BÖLÜM ADI]
ÖĞRENCİLER, BÖLÜMLER
ÖĞRENCİLER.BÖLÜM = BÖLÜMLER.[BÖLÜM KODU]
Böyle bir sorgu yazdığımız zaman hata verebilir. Çünkü SELECT ile seçeceğimiz alanlardan bazıları iki tabloda
da olabilir. Böyle durumlarda hangi tablonun alanını seçmek istediğimizi belirtmemiz gerekiyor. Bunun
yerine sorguyu aşağıdaki gibi yapmamız gerekecektir.
SELECT
FROM
WHERE
3
ÖĞRENCİLER.NUMARA, ÖĞRENCİLER.ADI, ÖĞRENCİLER.SOYADI,
BÖLÜMLER.[BÖLÜM KODU], BÖLÜMLER.[BÖLÜM ADI]
ÖĞRENCİLER, BÖLÜMLER
ÖĞRENCİLER.BÖLÜM = BÖLÜMLER.[BÖLÜM KODU]
Bartın MYO – Bilgisayar Programcılığı | Öğr. Gör. Bayram AKGÜL | 2013-2014 Güz Dönemi
10. Hafta
Çoklu Tablolardan Sorgulama Yapma
BPR255 Veritabanı
Yönetim Sistemleri-II
Yukarıdaki örnekte aslında hangi alanın hangi tabloya ait olduğunu sistem biliyor. Dolayısıyla yukarıdaki
örnek için tablo isimlerini alanların önünde belirtmek zorunda değiliz. Fakat aynı isimde farklı tablolara ait
alanlar olsaydı bunu belirtmemiz zorunlu olacaktı. Aslında sisteme hangi alanın hangi tabloya ait olduğunu
sürekli biz bildirirsek hem sistemde daha az iş yükü oluşacak, hem de yazdığımız sorgu anlaşılır olacaktır.
Bu sefer de her alan için uzun uzun tablo adını yazmak zor gelebiliyor. Bunun yerine kısaltmaları
kullanabiliriz. Uzun uzun tabloların ismini yazmaktansa tablo isimlerini AS ile takma isimlerini oluşturarak
kullanabiliriz. Aşağıdaki örnekte öğrenciler için O, bölümler için B takma ismi kullanılmıştır.
SELECT
FROM
WHERE
O.NUMARA, O.ADI, O.SOYADI, B.[BÖLÜM KODU], B.[BÖLÜM ADI]
ÖĞRENCİLER AS O, BÖLÜMLER AS B
O.BÖLÜM = B.[BÖLÜM KODU]
Örneğin Öğrenciler, Dersler ve Sınavlar tablosunu birleştirelim. Öğrenciler tablosunda 100, Dersler
tablosunda 6, Sınavlar tablosunda ise 500 kayıt varsa aşağıdaki sorgu sonucu 6*100*500 = 300000 satır
olacaktır.
SELECT *
FROM
ÖĞRENCİLER, DERSLER, SINAVLAR
Fakat biz bunu düzeltmek için tablolardaki referans anahtarları kendisinden referans alınmış anahtarlar ile
eşitleyerek sorguyu oluşturduğumuzda gerçek sonuçları alacağız.
SELECT
FROM
WHERE
4
*
ÖĞRENCİLER AS O, DERSLER AS D, SINAVLAR AS S
O.NUMARA = S.[ÖĞRENCİ NO] AND D.[DERS KODU] = S.[DERS KODU]
Bartın MYO – Bilgisayar Programcılığı | Öğr. Gör. Bayram AKGÜL | 2013-2014 Güz Dönemi
10. Hafta
Çoklu Tablolardan Sorgulama Yapma
BPR255 Veritabanı
Yönetim Sistemleri-II
Sorgu sonucunda görüldüğü gibi ders kodu, öğrenci numarası tekrar etmiş, istemediğimiz alanlar tabloda
bulunuyor. Tüm alanları sorgulamak yerine sorgumuzu şekildeki gibi yaparsak daha anlamlı olacaktır.
SELECT O.NUMARA, O.ADI, O.SOYADI, D.[DERS KODU], D.[DERS ADI],
S.VİZE, S.FİNAL, S.ORTALAMA
FROM
ÖĞRENCİLER AS O, DERSLER AS D, SINAVLAR AS S
WHERE O.NUMARA = S.[ÖĞRENCİ NO] AND D.[DERS KODU] = S.[DERS KODU]
Sorgu Sonucu:
Şimdi artık aşağıdaki sorgunun ne anlama geldiğini biliyoruz:
SELECT
B.[BÖLÜM KODU], S.[DERS KODU], D.[DERS ADI],
AVG(S.VİZE) AS [VİZE SINIF ORTALAMASI]
FROM
SINAVLAR AS S, ÖĞRENCİLER AS O, BÖLÜMLER AS B, DERSLER AS D
WHERE
S.[ÖĞRENCİ NO] = O.NUMARA AND S.[DERS KODU] = D.[DERS KODU]
AND B.[BÖLÜM KODU] = O.BÖLÜM
GROUP BY
HAVING
ORDER BY
S.[DERS KODU], D.[DERS ADI], B.[BÖLÜM KODU]
AVG(S.VİZE)>=60
[DERS KODU] DESC
Yukarıdaki sorgulamanın sonucu aşağıdaki gibi olacaktır.
5
Bartın MYO – Bilgisayar Programcılığı | Öğr. Gör. Bayram AKGÜL | 2013-2014 Güz Dönemi
10. Hafta
Çoklu Tablolardan Sorgulama Yapma
BPR255 Veritabanı
Yönetim Sistemleri-II
2. Tablo Birleştirme İşlemleri (JOIN)
JOIN (birleştir, eşleştir) bir veri tabanında iki veya daha fazla veri tabanı tablosunu ortak bir paydada bir
araya getirmek amacıyla kullanılan SQL sorgu işlemidir. Örneğin bir tablonuzda Kişiler var, diğer iki
tablomuzda ise bu kişilerin telefonları ve mail adresleri olmuş olsun. Bazı kişilerin telefon veya mail
adreslerini sorgulamak istediğimizde önce kişinin numarasını daha sonrada bu numaraya göre kişinin mail
veya telefon numarasını sorgulayabiliriz. Bunun için en az iki veya üç sorgu yazmamız gerekecektir. Böyle
durumlarda JOIN işlemi bunu tek sorguda yapabilmenizi sağlar.
Aşağıdaki örnek veri tabanının tabloları arasındaki ilişkilere dikkat edelim. JOIN işlemlerini bu tablolara
göre yapacağız.
Bu tablolara göre bir kişinin sonsuz telefon numarası veya sonsuz mail adresi olabilmektedir. Örneğin
kişilere ait telefon numaralarını sorgulamak istersek şimdiye kadar öğrendiğimiz yöntem ile aşağıdaki gibi
bir sorgulama yapabiliriz.
SELECT K.NUMARA, ADI, SOYADI, TELEFON
FROM
KISILER K , TELEFONLAR T
WHERE K.NUMARA = T.NUMARA
Bu sorgulama sonucu ile kişilere ait varsa bütün telefon numaraları gelecektir. Fakat telefon numarası
olmayan kişi kayıtları gelmeyecektir.
Kişiler
tablosundaki
tüm kayıtlar .
6
Bartın MYO – Bilgisayar Programcılığı | Öğr. Gör. Bayram AKGÜL | 2013-2014 Güz Dönemi
10. Hafta
Çoklu Tablolardan Sorgulama Yapma
BPR255 Veritabanı
Yönetim Sistemleri-II
Yukarıdaki sorgunun aynısını JOIN komutu ile aşağıdaki gibi yapabiliriz.
SELECT K.NUMARA, ADI, SOYADI, TELEFON
FROM
KISILER K INNER JOIN TELEFONLAR T
ON K.NUMARA = T.NUMARA
JOIN komutları INNER JOIN ve OUTER JOIN olmak üzere iki temel gruba ayrılır. OUTER JOIN de kendi
içinde LEFT OUTER JOIN, RIGHT OUTER JOIN ve FULL OUTER JOIN olmak üzere üçe ayrılır. INNER JOIN
komutu ile birleştirilmek istenen her iki tabloda sadece eşleşen kayıtlar gösterilir. Örneğin kişilerin mail
adreslerine yönelik bir sorgulama yapılacağı zaman aşağıdaki gibi bir sorgulama yapılabilir.
SELECT K.NUMARA, ADI, SOYADI, MAIL
FROM
KISILER K INNER JOIN MAILLER M
ON K.NUMARA = M.NUMARA
Bu sorgunun sonucunda sadece numarası eşleşen kayıtlar gelecektir.
Eğer mail tablosunda olmayan kişilerin de sorguda görünmesi isteniyorsa bu durumda aşağıdaki gibi bir
sorgu yazılabilir.
SELECT
FROM
K.NUMARA, ADI, SOYADI, MAIL
KISILER K LEFT OUTER JOIN MAILLER M
ON K.NUMARA = M.NUMARA
Bu sorgulama sonucuna dikkat edilirse sol
tarafa yazılan tablo esas tablo olarak kabul
edilmektedir.
Bu sorgulama ile sol tablodaki tüm kayıtlar
getirilir. Buna karşılık sağ tarafa yazılan
tablodan kayıtlar getirilerek sol taraftaki
kayıtlar ile eşleştirilir. Eşleştirme bu
sorgulamada ON K.NUMARA = M.NUMARA
şeklinde yapılmıştır. Eğer sol tablodaki kayda
karşılık sağ tarafta eşleşen kayıt yoksa karşısı
NULL olarak kalmaktadır.
Bir başka örnek olarak telefonlar tablosu ile mailler tablosunu birleştirelim:
7
Bartın MYO – Bilgisayar Programcılığı | Öğr. Gör. Bayram AKGÜL | 2013-2014 Güz Dönemi
10. Hafta
SELECT
FROM
Çoklu Tablolardan Sorgulama Yapma
BPR255 Veritabanı
Yönetim Sistemleri-II
T.NUMARA, TUR, TELEFON, MAIL
TELEFONLAR T LEFT OUTER JOIN MAILLER M
ON T.NUMARA = M.NUMARA
LEFT JOIN birleştirmesinde ana
tablo sol tarafta yazılan tablo
olduğundan sol taraftaki
telefonlar tablosundaki tüm
kayıtlar getirilmiş, karşılığında
varsa mailler tablosundan
kayıtlar getirilmiştir. Karşılığı
yoksa NULL bırakılmıştır.
Şimdi de sol tarafa mailler tablosunu yazalım.
SELECT
FROM
M.NUMARA, MAIL, TUR, TELEFON
MAILLER M LEFT OUTER JOIN TELEFONLAR T
ON M.NUMARA = T.NUMARA
LEFT JOIN birleştirmesinde ana
tablo sol tarafta yazılan tablo
olduğundan sol taraftaki
mailler tablosundaki tüm
kayıtlar getirilmiş, karşılığında
varsa telefonlar tablosundan
kayıtlar getirilmiştir. Karşılığı
yoksa NULL bırakılmıştır.
RIGHT JOIN; LEFT JOIN birleştirmesinin tam tersi olarak davranmaktadır. RIGHT JOIN birleştirmesinde ana
tablo sağdaki tablo olmaktadır.
FULL OUTER JOIN birleştirmelerinde ise her iki tabloda da olan tüm kayıtlar birleştirilir. Varsa eşleşen
kayıtlar eşleştirilir. Yoksa karşıları NULL olarak kalır.
SELECT
FROM
T.NUMARA, TUR, TELEFON, MAIL
TELEFONLAR T FULL OUTER JOIN MAILLER M
ON T.NUMARA = M.NUMARA
Bu sorgulama ile hem telefonlar tablosundaki tüm kayıtlar hem de mailler tablosundaki tüm kayıtlar
eşleştirilerek görüntülenmiştir. Telefonu olup maili olmayan veya maili olup telefonu olmayan kayıtların
karşılıkları NULL olarak bırakılmıştır.
8
Bartın MYO – Bilgisayar Programcılığı | Öğr. Gör. Bayram AKGÜL | 2013-2014 Güz Dönemi
10. Hafta
Çoklu Tablolardan Sorgulama Yapma
BPR255 Veritabanı
Yönetim Sistemleri-II
Not: LEFT OUTER JOIN, RIGHT OUTER JOIN veya FULL OUTER JOIN birleştirmelerinde OUTER anahtar
sözcüğünü yazmak isteğe bağlıdır. Yazılmasa da işlem gerçekleştirilir.
Aynı şekilde INNER JOIN birleşimlerinde de INNER anahtar sözcüğünü yazmak isteğe bağlıdır. Eğer sadece
JOIN yazılsa INNER JOIN anlamına gelmektedir.
JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
veya
veya
veya
veya
INNER
LEFT
RIGHT
FULL
JOIN
OUTER
OUTER
OUTER
JOIN
JOIN
JOIN
JOIN işlemleri ile ikiden fazla tablo birleştirilebilir.
SELECT
FROM
9
K.NUMARA, ADI, SOYADI, TUR, TELEFON, MAIL
KISILER K FULL JOIN TELEFONLAR T
ON K.NUMARA = T.NUMARA FULL JOIN MAILLER M ON K.NUMARA = M.NUMARA
Bartın MYO – Bilgisayar Programcılığı | Öğr. Gör. Bayram AKGÜL | 2013-2014 Güz Dönemi
10. Hafta
Çoklu Tablolardan Sorgulama Yapma
BPR255 Veritabanı
Yönetim Sistemleri-II
3. Tablo Ekleme İşlemleri (UNION)
JOIN birleştirmede tablolar sütun bazlı olarak birleştirilmekteydi. UNION işlemlerinde ise tablolar satır bazlı
olarak birleştirilmektedir.
UNION birden fazla sorgu cümlesinin sonucunu tek tabloda birleştirir. Kullanımı:
SELECT
UNION
SELECT
alan1,alan2,[,…n]
FROM
tablo_adı1
alan1,alan2,[,…n]
FROM
tablo_adı2
Not: varsayılanda UNION küme işlemleri gibi birleştirme yapar. Yani tekrarlayan kayıtlara izin vermez. Eğer
tekrarlayan kayıtlara izin verilsin isteniyorsa UNION ALL şeklinde yazılmalıdır.
SELECT
FROM
UNION
SELECT
FROM
K.NUMARA, ADI, SOYADI, TUR , TELEFON
KISILER K, TELEFONLAR T WHERE K.NUMARA = T.NUMARA
K.NUMARA, ADI, SOYADI, 'e-mail', MAIL
KISILER K, MAILLER M WHERE K.NUMARA = M.NUMARA
Not 1: UNION ile birleştirilen sorgu sonuçlarındaki alan isimleri her zaman ilk sorgudaki alan isimleri olur.
Yukarıdaki sorgu birleştirilmesinde telefonlar ile mailler birleştirilmiş ve telefon alanında maillerde
yazılmıştır.
Not 2: UNION ile sorgulamalar birleştirilmek isteniyorsa her iki sorgulamada da eşit sayıda alan olmalıdır.
Yukarıdaki ikinci sorguda mail için tür olmamasına rağmen eşit sayıda alan oluşması için tür için sabit olarak
‘e-mail’ bilgisi eklenmiştir.
Not 3: Aynı alana eklenmek istenen veriler bir birleri ile uyuşmalıdır. Örneğin sayısal veriye sahip bir alana
metinsel bir alan gelmemelidir.
10
Bartın MYO – Bilgisayar Programcılığı | Öğr. Gör. Bayram AKGÜL | 2013-2014 Güz Dönemi
10. Hafta
Çoklu Tablolardan Sorgulama Yapma
BPR255 Veritabanı
Yönetim Sistemleri-II
4. Sorgulamaları VIEW (Sanal Tablo) olarak kaydetme
Sorgulamalar view (sanal tablo) olarak kaydedilip daha sonra bu sanal tablolardan sorgulama yapılabiliyor.
View oluşturmak için CREATE VIEW view_adı AS sorgu şeklinde tanımlama yapılıyor.
CREATE VIEW [SINAV SONUCLARI]
AS
SELECT O.NUMARA, O.ADI, O.SOYADI, D.[DERS KODU],
D.[DERS ADI], S.VİZE, S.FİNAL, S.ORTALAMA
FROM
ÖĞRENCİLER AS O, DERSLER AS D, SINAVLAR AS S
WHERE
O.NUMARA = S.[ÖĞRENCİ NO] AND D.[DERS KODU] = S.[DERS KODU]
Daha sonra sanki SINAV SONUCLARI diye bir tablomuz varmış gibi istediğimiz şekilde sorgulama yapabiliriz.
Aşağıdaki sorgulama sonucu ile BPR251 dersinin sınav sonuçları listelenecektir.
SELECT * FROM [SINAV SONUCLARI] WHERE [DERS KODU] = 'BPR251'
Aşağıdaki sorgulama ile bir öğrencinin sınav sonuçları alınmıştır.
SELECT * FROM [SINAV SONUCLARI]
WHERE NUMARA = 1001001
View i sistemden silmek için DROP VIEW view_adı şeklinde komut yazılır. View silinince sistemdeki gerçek
tablolara bir zarar gelmez.
DROP VIEW [SINAV SONUCLARI]
11
Bartın MYO – Bilgisayar Programcılığı | Öğr. Gör. Bayram AKGÜL | 2013-2014 Güz Dönemi