Bir sorgu hatasız çalışıyor ve mantıklı görünen bir sayı döndürüyor, ancak satırlar eksik ve ortalama yanlış. Bunun nedeni çoğunlukla SQL NULL değerleridir. NULL herhangi bir normal değer gibi davranmaz ve bu hem yeni başlayanların hem de deneyimli geliştiricilerin dikkatini çeker.

Bu kılavuz NULL’un tam olarak nasıl davrandığını gösterir. Her sorguyu SQLite 3.50’de çalıştırdık ve gösterilen sonuçlar bu çalıştırmadan geldi. Bu davranışın çoğu standart SQL’dir ve NULL sıralama düzeni ve Oracle’ın boş dizeleri işlemesi gibi veritabanlarının nerede farklılık gösterdiğini not ederiz.

NULL nedir?

NULL, bir değerin eksik veya bilinmediği anlamına gelen bir işaretçidir. Bu alışılagelmiş anlamda bir değer değildir, dolayısıyla bunların hepsi farklı şeylerdir:

Aradaki fark özellikle miktarlar açısından önemlidir. 0 bonus, kişinin hiçbir şey almadığı anlamına gelir. NULL bonus, henüz kimsenin bilmediği anlamına gelir.

Örnek tablo

CREATE TABLE employees (
  id INT, name TEXT, phone TEXT, bonus INT, manager_id INT
);

INSERT INTO employees VALUES
 (1,'Meera','555-0101',500,NULL),
 (2,'Arjun',NULL,300,1),
 (3,'Sara','',NULL,1),
 (4,'Ravi',NULL,NULL,2);

Sara’nın telefon için boş bir dizisi olduğuna, Arjun ve Ravi’nin ise NULL’a sahip olduğuna dikkat edin. İkisine çok farklı davranılıyor.

NULL için test etme

Hemen hemen herkesin ilk başta yaptığı hata şu:

SELECT name FROM employees WHERE phone = NULL;
-- (no rows)

SELECT name FROM employees WHERE phone IS NULL;
-- Arjun
-- Ravi

SELECT name FROM employees WHERE phone IS NOT NULL;
-- Meera
-- Sara

İlk sorgu geçerli SQL’dir ve hata vermez. Hiçbir şey döndürmez çünkü herhangi bir şeyi NULL ile karşılaştırmak asla doğru değildir. Boş bir dize gerçek bir değer olduğundan Sara IS NOT NULL‘in altında görünür. Ancak Oracle’da boş bir dize NULL olarak değerlendirilir ve bu dikkate değer bir istisnadır.

Üç değerli mantık

Çoğu programlama dilinde bir koşul doğru veya yanlıştır. SQL üçüncü bir sonuç ekler: bilinmiyor. Bir değeri bilmiyorsanız 5’e eşit olup olmadığını söyleyemezsiniz. Ve iki bilinmeyen değerin eşit olduğu bilinmez, bu yüzden NULL = NULL doğru değildir. Testimizde bunların tümü NULL döndürdü:

SELECT NULL = NULL, NULL <> 1, 1 + NULL, 'a' || NULL;

-- NULL | NULL | NULL | NULL

Koşulların birleştirilmesi dikkatli kurallara tabidir. 1’in doğru ve 0’ın yanlış olduğu çalışmamızın sonuçları şunlardır:

WHERE konumundan kaybolan satırlar

WHERE bir satırı yalnızca koşulu doğru olduğunda tutar. Sonucun bilinmediği satırlar, yanlış olan satırlarla birlikte bırakılır. Bu şaşırtıcı sonuçlara yol açar:

SELECT name FROM employees WHERE phone <> '555-0101';

-- Sara

Telefonları kesinlikle bu numara olmadığından Arjun ve Ravi’nin ortaya çıkmasını bekleyebilirsiniz. Ancak telefonları bilinmiyor, dolayısıyla sonuç da bilinmiyor ve ortadan kayboluyorlar. Bunları dahil etmek için şunu açıkça söyleyin: WHERE phone <> '555-0101' OR phone IS NULL. Olumsuzlama da yardımcı olmuyor:

SELECT name FROM employees WHERE NOT (bonus > 400);

-- Arjun

Sara ve Ravi’nin bonusu yok, dolayısıyla ne bonus > 400‘de ne de onun tersinde görünüyorlar. Bu hatırlanması gereken önemli bir noktadır: bir koşul ve onun olumsuzlanması her zaman her satırı kapsamaz.

COALESCE: bir geri dönüş sağlayın

COALESCE listesindeki NULL olmayan ilk değeri döndürür ve tüm önemli veritabanlarında çalışır:

SELECT COALESCE(NULL, NULL, 'third', 'fourth');
-- third

SELECT name, COALESCE(phone, 'no phone') FROM employees;
-- Meera | 555-0101
-- Arjun | no phone
-- Sara  |
-- Ravi  | no phone

Boş bir dize NULL olmadığından Sara hala boş görünüyor. MySQL ve SQLite’ta IFNULL, SQL Server’da ISNULL ve Oracle’da NVL gibi benzer işlevler mevcuttur. COALESCE taşınabilir seçimdir. Hesaplamalarla ilgili daha önce belirttiğimiz nokta burada da geçerlidir. bonus + 100, bonusu olmayan herkes için NULL verir; bu nedenle, eksik bir bonusu sıfır olarak ele almak işletmeniz için doğruysa COALESCE(bonus, 0) + 100 yazın.

NULLIF: bir değeri NULL’a dönüştürür

NULLIF(a, b), eğer iki değer eşitse NULL değerini döndürür, aksi halde a değerini döndürür. İki kullanım özellikle faydalıdır. Yer tutucu değerlerini temizleyebilir:

SELECT name, NULLIF(phone, '') FROM employees WHERE id = 3;
-- Sara | NULL

SELECT name FROM employees
WHERE COALESCE(NULLIF(phone, ''), '') = '';
-- Arjun
-- Sara
-- Ravi

Bu ikinci sorgu “eksik veya boş” değerleri bulmanın kullanışlı bir yoludur. Ayrıca birçok veritabanında hataya neden olan sıfıra bölünmeye karşı da koruma sağlayabilir:

SELECT total / NULLIF(quantity, 0) FROM sales;

quantity 0 olduğunda, bu başarısızlık yerine NULL verir. Testimizde SELECT 100 / NULLIF(0, 0) NULL değerini döndürdü.

Toplamalar NULL’u atlıyor

NULL’un raporlara en fazla zararı verdiği yer burasıdır:

SELECT COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus),
       AVG(COALESCE(bonus, 0))
FROM employees;

-- 4 | 2 | 800 | 400.0 | 200.0

Peki ortalama ikramiye nedir, 400 mü yoksa 200 mü? Verilerinizde NULL’un ne anlama geldiğine bağlıdır. NULL, “bonus ödenmedi” anlamına geliyorsa 200 doğrudur. Eğer “henüz karar verilmedi” anlamına geliyorsa 400 doğru olabilir. Veritabanı bu kararı sizin yerinize veremez, dolayısıyla sorunun işi bilen birine gitmesi gerekir.

İlgili bir tuzak: hiçbir değeri olmayan SUM, 0 yerine NULL’dur. Yalnızca bonussuz satırları topladığımızda, NULL elde ettik, oysa COUNT 0 döndürdü. Bir sayı bekleyen uygulama kodu bunda çökebilir, bu yüzden onu sarın: COALESCE(SUM(bonus), 0).

GRUP BY, sıralama ve DISTINCT

Karşılaştırmalarda NULL’lar “eşit olmasa da” bu işlemler onları tek bir grup olarak ele alır:

SELECT bonus, COUNT(*) FROM employees GROUP BY bonus ORDER BY bonus;
-- NULL | 2
--  300 | 1
--  500 | 1

SELECT DISTINCT bonus FROM employees;
-- 500, 300, NULL

Gruplandırma hakkında daha fazla bilgi için SQL GROUP BY ve HAVING‘e bakın. Sıralama düzeni, veritabanlarının gerçekten farklı olduğu yerdir. SQLite ve MySQL NULL’u en küçük değer olarak ele alırken, PostgreSQL ve Oracle bunu en büyük değer olarak ele alır. Yani aynı ORDER BY bonus DESC NULL’ları birinde sona, diğerinde ilk sıraya koyabilir. PostgreSQL, Oracle ve modern SQLite bunu NULLS FIRST veya NULLS LAST kullanarak doğrudan belirtmenize olanak tanır.

Katılıyor

= kullanan bir birleştirme koşulu hiçbir zaman NULL ile eşleşmez, bu nedenle NULL anahtarı olan satırlar bir iç birleştirmeden çıkar:

SELECT e.name FROM employees e
JOIN employees m ON m.id = e.manager_id;
-- Arjun, Sara, Ravi    (Meera is gone)

SELECT e.name, m.name FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- Meera | NULL
-- Arjun | Meera
-- Sara  | Meera
-- Ravi  | Arjun

Meera’nın yöneticisi yok, bu yüzden iç birleşim bu sırayı sessizce ortadan kaldırır. A LEFT JOIN onu korur ve aynı zamanda eksik taraf için kendi NULL’larını yaratır. Daha fazlasını SQL birleştirmelerinin açıklanması bölümünde okuyun.

DEĞİLDİR tuzağı

NOT IN listesindeki tek bir NULL, tüm sonuçları siler:

SELECT name FROM employees WHERE manager_id NOT IN (1);
-- Ravi

SELECT name FROM employees WHERE manager_id NOT IN (1, NULL);
-- (no rows)

İkinci biçim manager_id <> 1 AND manager_id <> NULL anlamına gelir ve bu son kısım hiçbir zaman doğru olamaz. Bu, NULL’un fark edilmeden gelebileceği alt sorgularda özellikle tehlikelidir. Bunun yerine, SQL alt sorguları eğitimimizde açıklandığı gibi NOT EXISTS‘yi kullanın. Bu satırın manager_id NULL olması nedeniyle Meera da ilk sonuçta eksik.

NULL ile tasarlama

• Varsayılan olarak NOT NULL‘yi kullanın. NULL’a yalnızca bir değerin gerçekten eksik olabileceği durumlarda izin verin.
• Mantıklı varsayılanlar ekleyin. Örneğin, quantity INT NOT NULL DEFAULT 0.
• Sihirli değerler kullanmayın. 1900-01-01 tarihi veya “bilinmiyor” yerine -1 yaşı hesaplamalarınızı bozacaktır.
• BENZERSİZ davranışı bilin. Çoğu veritabanı, bir UNIQUE sütununda birden fazla NULL’a izin verir ve testimiz, hiçbir hata olmadan iki tane ekledi. SQL Server bir istisnadır çünkü normalde buna izin verir.
• Birincil anahtarın NULL olamayacağını unutmayın. Bkz. birincil anahtar ile yabancı anahtarın karşılaştırılması.
• NULL’larla test edin. NULL içermeyen test verileri bu hataları gizler. Diğer son durumlarda yaptığınız gibi, bunları da bilerek ekleyin.

Son sözler

NULL’a “bilinmeyen” muamelesi yaptığınızda davranışının çoğu anlamlı olmaya başlar: bilinmeyen artı bir bilinmiyor ve bilinmeyen eşittir bilinmeyenin doğru olduğu bilinmiyor. Bu hafta kendi raporlarınızdan birini denetleyin. COUNT(*)‘e karşı COUNT(column)‘yi kullanarak null olabilen sütunları kontrol edin, herhangi bir NOT IN arayın ve her ortalamada NULL’un ne anlama gelmesi gerektiğine karar verin.

Sık sorulan sorular

Öğrenmeye devam et