MS SQL Join ve Full Join Sorgusu
Güncelleme: 1.851 defa okundu
Konu: Veri Analizi
İçindekiler
MS SQL de join sorguları ile tabloları arasında ilişki kurulabilir. Inner join sorgusunda sadece her bir tabloda eşleşen kayıtlar sorgu sonucuna gelir. Left ve right join sorgusunda tablolardan birisinin tüm kayıtları gelirken, diğer tabloda olmayan veriler dışarıda kalır. Full join sorgusunda ise tablolarda bulunan bütün kayıtlar sorgu sonucuna gelir.
Örnek olarak Sehirkod ve Sehirad tablosu üzerinden join sorguları ve sorgu sonuçları aşağıda verilmiştir.
SEHIRKOD TABLOSU
| ID | KOD |
| 1 | 34 |
| 2 | 6 |
| 3 | 35 |
SEHIRAD TABLOSU
| ID | SEHIR |
| 1 | İSTANBUL |
| 2 | ANKARA |
| 4 | ANTALYA |
| 5 | BURSA |
---INNER JOIN
SQL
- SELECT * FROM
- (SELECT 1 AS ID,'34' AS KOD UNION
- SELECT 2 AS ID,'06' AS KOD UNION
- SELECT 3 AS ID,'35' AS KOD) AS SEHIRKOD
- INNER JOIN
- (SELECT 1 AS ID,'İSTANBUL' AS SEHIR UNION
- SELECT 2 AS ID,'ANKARA' AS SEHIR UNION
- SELECT 4 AS ID,'ANTALYA' AS SEHIR UNION
- SELECT 5 AS ID,'BURSA' AS SEHIR) AS SEHIRAD
- ON SEHIRKOD.ID=SEHIRAD.ID
| ID | KOD | ID | SEHIR |
| 1 | 34 | 1 | İSTANBUL |
| 2 | 6 | 2 | ANKARA |
---LEFT JOIN
SQL
- SELECT * FROM
- (SELECT 1 AS ID,'34' AS KOD UNION
- SELECT 2 AS ID,'06' AS KOD UNION
- SELECT 3 AS ID,'35' AS KOD) AS SEHIRKOD
- LEFT JOIN
- (SELECT 1 AS ID,'İSTANBUL' AS SEHIR UNION
- SELECT 2 AS ID,'ANKARA' AS SEHIR UNION
- SELECT 4 AS ID,'ANTALYA' AS SEHIR UNION
- SELECT 5 AS ID,'BURSA' AS SEHIR) AS SEHIRAD
- ON SEHIRKOD.ID=SEHIRAD.ID
| ID | KOD | ID | SEHIR |
| 1 | 34 | 1 | İSTANBUL |
| 2 | 6 | 2 | ANKARA |
| 3 | 35 | NULL | NULL |
---RIGHT JOIN
SQL
- SELECT * FROM
- (SELECT 1 AS ID,'34' AS KOD UNION
- SELECT 2 AS ID,'06' AS KOD UNION
- SELECT 3 AS ID,'35' AS KOD) AS SEHIRKOD
- RIGHT JOIN
- (SELECT 1 AS ID,'İSTANBUL' AS SEHIR UNION
- SELECT 2 AS ID,'ANKARA' AS SEHIR UNION
- SELECT 4 AS ID,'ANTALYA' AS SEHIR UNION
- SELECT 5 AS ID,'BURSA' AS SEHIR) AS SEHIRAD
- ON SEHIRKOD.ID=SEHIRAD.ID
| ID | KOD | ID | SEHIR |
| 1 | 34 | 1 | İSTANBUL |
| 2 | 6 | 2 | ANKARA |
| 3 | 35 | NULL | NULL |
---FULL JOIN
SQL
- SELECT ISNULL(SEHIRKOD.ID,SEHIRAD.ID) AS ID,KOD,SEHIR
- FROM
- (SELECT 1 AS ID,'34' AS KOD UNION
- SELECT 2 AS ID,'06' AS KOD UNION
- SELECT 3 AS ID,'35' AS KOD) AS SEHIRKOD
- FULL JOIN
- (SELECT 1 AS ID,'İSTANBUL' AS SEHIR UNION
- SELECT 2 AS ID,'ANKARA' AS SEHIR UNION
- SELECT 4 AS ID,'ANTALYA' AS SEHIR UNION
- SELECT 5 AS ID,'BURSA' AS SEHIR) AS SEHIRAD
- ON SEHIRKOD.ID=SEHIRAD.ID
| ID | KOD | SEHIR |
| 1 | 34 | İSTANBUL |
| 2 | 6 | ANKARA |
| 4 | NULL | ANTALYA |
| 5 | NULL | BURSA |
| 3 | 35 | NULL |
Teşekkürler, geri bildiriminiz alındı.
