SQL WITH Kullanımı (CTE) ve Örnekleri
11 defa okundu
Konu: Veri Analizi
İçindekiler
SQL Server'da WITH ifadesi ile sorgunun başında geçici bir sonuç kümesi tanımlanır ve aynı sorgu içinde bir tablo gibi kullanılır. Bu yapıya CTE (Common Table Expression) denir. İç içe yazılan alt sorguların (subquery) yerine kullanıldığında sorgu daha okunaklı olur, aynı sonuç kümesi sorgu içinde birden fazla yerde kullanılabilir ve kendini çağıran (özyinelemeli, recursive) sorgular yazılabilir. WITH kullanımı SQL Server 2005 ve sonrası versiyonlarda çalışmaktadır.
WITH ifadesinin genel kullanılışı aşağıdaki gibidir.
- WITH CTE_ADI AS
- (
- SELECT ALAN1, ALAN2 FROM TABLO_ADI
- )
- SELECT * FROM CTE_ADI
Aşağıdaki örneklerde personel bilgilerinden oluşan geçici bir tablo üzerinden WITH kullanımının farklı örnekleri verilmiştir.
Veri kaynağı için geçici tablo oluşturma
- SELECT 1 AS ID,'AHMET1' AS ADI,'DENEME1' AS SOYADI,N'YÖNETİM' AS BOLUM,90000 AS MAAS,NULL AS YONETICIID
- INTO #PERSONEL
- UNION
- SELECT 2 AS ID,'AHMET2' AS ADI,'DENEME2' AS SOYADI,N'MUHASEBE' AS BOLUM,52000 AS MAAS,1 AS YONETICIID
- UNION
- SELECT 3 AS ID,'AHMET3' AS ADI,'DENEME3' AS SOYADI,N'MUHASEBE' AS BOLUM,41000 AS MAAS,2 AS YONETICIID
- UNION
- SELECT 4 AS ID,'AHMET4' AS ADI,'DENEME4' AS SOYADI,N'MUHASEBE' AS BOLUM,38000 AS MAAS,2 AS YONETICIID
- UNION
- SELECT 5 AS ID,'AHMET5' AS ADI,'DENEME5' AS SOYADI,N'SATIŞ' AS BOLUM,55000 AS MAAS,1 AS YONETICIID
- UNION
- SELECT 6 AS ID,'AHMET6' AS ADI,'DENEME6' AS SOYADI,N'SATIŞ' AS BOLUM,44000 AS MAAS,5 AS YONETICIID
- UNION
- SELECT 7 AS ID,'AHMET7' AS ADI,'DENEME7' AS SOYADI,N'SATIŞ' AS BOLUM,47000 AS MAAS,5 AS YONETICIID
- UNION
- SELECT 8 AS ID,'AHMET8' AS ADI,'DENEME8' AS SOYADI,N'SATIŞ' AS BOLUM,39000 AS MAAS,6 AS YONETICIID
- UNION
- SELECT 9 AS ID,'AHMET9' AS ADI,'DENEME9' AS SOYADI,N'BİLGİ İŞLEM' AS BOLUM,60000 AS MAAS,1 AS YONETICIID
- UNION
- SELECT 10 AS ID,'AHMET10' AS ADI,'DENEME10' AS SOYADI,N'BİLGİ İŞLEM' AS BOLUM,48000 AS MAAS,9 AS YONETICIID
- UNION
- SELECT 11 AS ID,'AHMET11' AS ADI,'DENEME11' AS SOYADI,N'BİLGİ İŞLEM' AS BOLUM,45000 AS MAAS,9 AS YONETICIID
Oluşturduğumuz geçici tablomuzun içeriği aşağıdaki gibidir. YONETICIID alanı personelin bağlı olduğu yöneticinin ID numarasıdır.
- SELECT * FROM #PERSONEL ORDER BY ID
| ID | ADI | SOYADI | BOLUM | MAAS | YONETICIID |
|---|---|---|---|---|---|
| 1 | AHMET1 | DENEME1 | YÖNETİM | 90000 | NULL |
| 2 | AHMET2 | DENEME2 | MUHASEBE | 52000 | 1 |
| 3 | AHMET3 | DENEME3 | MUHASEBE | 41000 | 2 |
| 4 | AHMET4 | DENEME4 | MUHASEBE | 38000 | 2 |
| 5 | AHMET5 | DENEME5 | SATIŞ | 55000 | 1 |
| 6 | AHMET6 | DENEME6 | SATIŞ | 44000 | 5 |
| 7 | AHMET7 | DENEME7 | SATIŞ | 47000 | 5 |
| 8 | AHMET8 | DENEME8 | SATIŞ | 39000 | 6 |
| 9 | AHMET9 | DENEME9 | BİLGİ İŞLEM | 60000 | 1 |
| 10 | AHMET10 | DENEME10 | BİLGİ İŞLEM | 48000 | 9 |
| 11 | AHMET11 | DENEME11 | BİLGİ İŞLEM | 45000 | 9 |
1. Bölüm ortalamasının üzerinde maaş alan personeller
Önce WITH ile her bölümün ortalama maaşını bulan sonuç kümesini (BOLUM_ORTALAMA) tanımlıyoruz. Sonra bu kümeyi personel tablosuyla birleştirerek maaşı bölüm ortalamasının üzerinde olan personelleri listeliyoruz.
- WITH BOLUM_ORTALAMA AS
- (
- SELECT BOLUM, AVG(MAAS) AS ORTALAMA
- FROM #PERSONEL
- GROUP BY BOLUM
- )
- SELECT P.ADI, P.SOYADI, P.BOLUM, P.MAAS, B.ORTALAMA
- FROM #PERSONEL P
- INNER JOIN BOLUM_ORTALAMA B ON B.BOLUM = P.BOLUM
- WHERE P.MAAS > B.ORTALAMA
- ORDER BY P.BOLUM, P.MAAS DESC
Sorgu Sonucu:
| ADI | SOYADI | BOLUM | MAAS | ORTALAMA |
|---|---|---|---|---|
| AHMET9 | DENEME9 | BİLGİ İŞLEM | 60000 | 51000 |
| AHMET2 | DENEME2 | MUHASEBE | 52000 | 43666 |
| AHMET5 | DENEME5 | SATIŞ | 55000 | 46250 |
| AHMET7 | DENEME7 | SATIŞ | 47000 | 46250 |
MAAS alanı tam sayı olduğu için ortalama da tam sayı olarak döner (MUHASEBE bölümünde 43.666,67 yerine 43666). Kuruşlu ortalama için AVG(MAAS * 1.0) yazılabilir.
2. Birden fazla WITH (CTE) kullanımı
Bir sorguda birden fazla CTE tanımlanabilir; WITH bir kez yazılır, sonraki CTE'ler virgül ile ayrılır. İkinci CTE birinciyi kullanabilir. Aşağıdaki sorguda önce bölüm toplamlarını (BOLUM_TOPLAM), sonra bu toplamlardan genel toplamı (GENEL_TOPLAM) buluyoruz ve her bölümün maaş toplamı içindeki payını yüzde olarak hesaplıyoruz.
- WITH BOLUM_TOPLAM AS
- (
- SELECT BOLUM, COUNT(ID) AS KISI, SUM(MAAS) AS TOPLAM
- FROM #PERSONEL
- GROUP BY BOLUM
- ),
- GENEL_TOPLAM AS
- (
- SELECT SUM(TOPLAM) AS GENEL FROM BOLUM_TOPLAM
- )
- SELECT B.BOLUM, B.KISI, B.TOPLAM,
- CAST(B.TOPLAM * 100.0 / G.GENEL AS DECIMAL(5,2)) AS YUZDE
- FROM BOLUM_TOPLAM B
- CROSS JOIN GENEL_TOPLAM G
- ORDER BY B.TOPLAM DESC
Sorgu Sonucu:
| BOLUM | KISI | TOPLAM | YUZDE |
|---|---|---|---|
| SATIŞ | 4 | 185000 | 33.09 |
| BİLGİ İŞLEM | 3 | 153000 | 27.37 |
| MUHASEBE | 3 | 131000 | 23.43 |
| YÖNETİM | 1 | 90000 | 16.10 |
3. Her bölümün en yüksek maaşlı personeli
ROW_NUMBER fonksiyonu ile her bölüm içinde maaşa göre sıra numarası veriyoruz. Sıra numarası WHERE içinde doğrudan kullanılamadığı için önce WITH ile sıralı kümeyi oluşturup sonra SIRA = 1 olan kayıtları seçiyoruz. SIRA <= 3 yazılırsa her bölümün en yüksek maaşlı üç personeli listelenir.
- WITH SIRALI AS
- (
- SELECT ADI, SOYADI, BOLUM, MAAS,
- ROW_NUMBER() OVER(PARTITION BY BOLUM ORDER BY MAAS DESC) AS SIRA
- FROM #PERSONEL
- )
- SELECT BOLUM, ADI, SOYADI, MAAS
- FROM SIRALI
- WHERE SIRA = 1
- ORDER BY BOLUM
Sorgu Sonucu:
| BOLUM | ADI | SOYADI | MAAS |
|---|---|---|---|
| BİLGİ İŞLEM | AHMET9 | DENEME9 | 60000 |
| MUHASEBE | AHMET2 | DENEME2 | 52000 |
| SATIŞ | AHMET5 | DENEME5 | 55000 |
| YÖNETİM | AHMET1 | DENEME1 | 90000 |
4. Özyinelemeli (recursive) WITH ile organizasyon şeması
WITH ile tanımlanan küme kendi içinde kendini çağırabilir. Bu yapıda iki sorgu UNION ALL ile birleştirilir: ilk sorgu başlangıç kayıtlarını (yöneticisi olmayan genel müdür), ikinci sorgu ise bir önceki adımda bulunan kişilere bağlı personelleri getirir. Yeni kayıt bulunamayınca sorgu durur. Böylece her personelin şemadaki seviyesini ve yöneticiden kendisine kadar olan yolu buluyoruz.
- WITH SEMA AS
- (
- SELECT ID, ADI, BOLUM, YONETICIID, 1 AS SEVIYE,
- CAST(ADI AS NVARCHAR(200)) AS YOL
- FROM #PERSONEL
- WHERE YONETICIID IS NULL
- UNION ALL
- SELECT P.ID, P.ADI, P.BOLUM, P.YONETICIID, S.SEVIYE + 1,
- CAST(S.YOL + N' > ' + P.ADI AS NVARCHAR(200))
- FROM #PERSONEL P
- INNER JOIN SEMA S ON P.YONETICIID = S.ID
- )
- SELECT SEVIYE, ADI, BOLUM, YOL
- FROM SEMA
- ORDER BY YOL
Sorgu Sonucu:
| SEVIYE | ADI | BOLUM | YOL |
|---|---|---|---|
| 1 | AHMET1 | YÖNETİM | AHMET1 |
| 2 | AHMET2 | MUHASEBE | AHMET1 > AHMET2 |
| 3 | AHMET3 | MUHASEBE | AHMET1 > AHMET2 > AHMET3 |
| 3 | AHMET4 | MUHASEBE | AHMET1 > AHMET2 > AHMET4 |
| 2 | AHMET5 | SATIŞ | AHMET1 > AHMET5 |
| 3 | AHMET6 | SATIŞ | AHMET1 > AHMET5 > AHMET6 |
| 4 | AHMET8 | SATIŞ | AHMET1 > AHMET5 > AHMET6 > AHMET8 |
| 3 | AHMET7 | SATIŞ | AHMET1 > AHMET5 > AHMET7 |
| 2 | AHMET9 | BİLGİ İŞLEM | AHMET1 > AHMET9 |
| 3 | AHMET10 | BİLGİ İŞLEM | AHMET1 > AHMET9 > AHMET10 |
| 3 | AHMET11 | BİLGİ İŞLEM | AHMET1 > AHMET9 > AHMET11 |
5. WITH ile ay listesi oluşturma
Özyinelemeli WITH ile tabloya ihtiyaç duymadan sayı veya tarih listesi de üretilebilir. Aşağıdaki sorgu 2026 yılının ayları için ay numarası, ay adı, ayın ilk ve son gününü listeler. Satış olmayan ayların da raporda sıfır olarak görünmesi için bu liste satış tablosuyla LEFT JOIN ile birleştirilebilir.
- WITH AYLAR AS
- (
- SELECT CAST('2026-01-01' AS DATE) AS TARIH
- UNION ALL
- SELECT DATEADD(MONTH, 1, TARIH) FROM AYLAR
- WHERE TARIH < '2026-12-01'
- )
- SELECT MONTH(TARIH) AS AY_NO, FORMAT(TARIH, 'MMMM', 'tr-TR') AS AY_ADI, TARIH AS AY_BASI,
- EOMONTH(TARIH) AS AY_SONU
- FROM AYLAR
- OPTION (MAXRECURSION 12)
Sorgu Sonucu:
| AY_NO | AY_ADI | AY_BASI | AY_SONU |
|---|---|---|---|
| 1 | Ocak | 2026-01-01 | 2026-01-31 |
| 2 | Şubat | 2026-02-01 | 2026-02-28 |
| 3 | Mart | 2026-03-01 | 2026-03-31 |
| 4 | Nisan | 2026-04-01 | 2026-04-30 |
| 5 | Mayıs | 2026-05-01 | 2026-05-31 |
| 6 | Haziran | 2026-06-01 | 2026-06-30 |
| 7 | Temmuz | 2026-07-01 | 2026-07-31 |
| 8 | Ağustos | 2026-08-01 | 2026-08-31 |
| 9 | Eylül | 2026-09-01 | 2026-09-30 |
| 10 | Ekim | 2026-10-01 | 2026-10-31 |
| 11 | Kasım | 2026-11-01 | 2026-11-30 |
| 12 | Aralık | 2026-12-01 | 2026-12-31 |
Özyinelemeli sorgular varsayılan olarak en fazla 100 kez kendini çağırabilir; daha uzun listeler için sorgunun sonuna OPTION (MAXRECURSION 1000) gibi bir sınır yazılır (0 sınırsızdır). FORMAT ve EOMONTH fonksiyonları SQL Server 2012 ve sonrasında çalışır.
6. WITH ile mükerrer kayıtları silme
WITH ile tanımlanan küme üzerinden UPDATE ve DELETE de yapılabilir; işlem asıl tabloya uygulanır. Aşağıdaki rehber tablosunda aynı kişi birden fazla kez kayıtlıdır.
- SELECT 1 AS ID,'AHMET1' AS ADI,'DENEME1' AS SOYADI,'5550001111' AS TELEFON
- INTO #REHBER
- UNION
- SELECT 2 AS ID,'AHMET2' AS ADI,'DENEME2' AS SOYADI,'5550002222' AS TELEFON
- UNION
- SELECT 3 AS ID,'AHMET1' AS ADI,'DENEME1' AS SOYADI,'5550001111' AS TELEFON
- UNION
- SELECT 4 AS ID,'AHMET3' AS ADI,'DENEME3' AS SOYADI,'5550003333' AS TELEFON
- UNION
- SELECT 5 AS ID,'AHMET2' AS ADI,'DENEME2' AS SOYADI,'5550002222' AS TELEFON
- UNION
- SELECT 6 AS ID,'AHMET1' AS ADI,'DENEME1' AS SOYADI,'5550001111' AS TELEFON
- SELECT * FROM #REHBER ORDER BY ID
| ID | ADI | SOYADI | TELEFON |
|---|---|---|---|
| 1 | AHMET1 | DENEME1 | 5550001111 |
| 2 | AHMET2 | DENEME2 | 5550002222 |
| 3 | AHMET1 | DENEME1 | 5550001111 |
| 4 | AHMET3 | DENEME3 | 5550003333 |
| 5 | AHMET2 | DENEME2 | 5550002222 |
| 6 | AHMET1 | DENEME1 | 5550001111 |
Ad, soyad ve telefonu aynı olan kayıtlara ID sırasıyla numara veriyoruz; ilk kayıt kalıyor, SIRA numarası 1'den büyük olan tekrar eden kayıtlar siliniyor.
- WITH MUKERRER AS
- (
- SELECT ID, ADI, SOYADI, TELEFON,
- ROW_NUMBER() OVER(PARTITION BY ADI, SOYADI, TELEFON ORDER BY ID) AS SIRA
- FROM #REHBER
- )
- DELETE FROM MUKERRER WHERE SIRA > 1
- SELECT * FROM #REHBER ORDER BY ID
Sorgu Sonucu:
| ID | ADI | SOYADI | TELEFON |
|---|---|---|---|
| 1 | AHMET1 | DENEME1 | 5550001111 |
| 2 | AHMET2 | DENEME2 | 5550002222 |
| 4 | AHMET3 | DENEME3 | 5550003333 |
DELETE işlemi geri alınamaz. Gerçek tabloda çalıştırmadan önce DELETE yerine SELECT * FROM MUKERRER WHERE SIRA > 1 yazarak silinecek kayıtları kontrol edin.
WITH kullanırken dikkat edilmesi gerekenler
- WITH'ten önce başka bir komut varsa o komut noktalı virgül (;) ile bitirilmelidir; aksi halde "Incorrect syntax near the keyword 'with'" hatası alınır. Bu yüzden sorguların başına ;WITH yazıldığı da görülür.
- CTE yalnızca hemen arkasından gelen tek sorguda (SELECT, INSERT, UPDATE, DELETE) geçerlidir. Sonuç birden fazla sorguda kullanılacaksa geçici tablo (#TABLO) veya tablo değişkeni (@TABLO) kullanılmalıdır.
- CTE içinde ORDER BY, TOP olmadan kullanılamaz; sıralama en sondaki sorguda yapılır.
- CTE sonuçları saklanmaz; sorgu içinde iki kez kullanılan CTE iki kez çalışır. Büyük veride aynı sonuç defalarca kullanılacaksa geçici tablo daha hızlı olabilir.
İlgili yazılar: MS SQL ile sorguya sıra numarası ekleme · MS SQL'de mükerrer kayıt bulma · SQL döngü kullanımı · MS SQL ile çapraz sorgu (pivot tablo)
