MS SQL Join ve Full Join Sorgusu
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
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
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
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
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 |
