Soru JOIN ve WHERE'deki filtreleme sorguları arasındaki fark nedir?


SQL'de sonuçları bir kimliğe göre filtrelemeyi ve aralarında herhangi bir mantıksal fark olup olmadığını merak etmeye çalışıyorum.

SELECT value 
FROM table1 
JOIN table2 ON table1.id = table2.id 
WHERE table1.id = 1

ve

SELECT value 
FROM table1 
JOIN table2 ON table1.id = table2.id AND table1.id = 1

Benim için mantık farklı görünüyor, ancak her zaman aynı sonuçları elde edersiniz ama iki farklı sonuç kümesine sahip olacağınız herhangi bir koşul olup olmadığını merak ettim (ya da her zaman aynı iki sonuç kümesini her zaman iade ederlerdi) )


22
2017-12-24 15:25


Menşei


Basit YOK HAYIR fark - Pரதீப்
Katılmayı dene, sonra farklılıkları görmeye başlayacaksın. - sstan
İkisi için alınan yolu görmek için bir gösteri planı denediniz mi? - scrappedcola
Olası kopya Hangi SQL sorgusu daha hızlıdır? Katılma ölçütlerine göre filtreleme ya da nerede yan tümce? - HoneyBadger
@HoneyBadger, aslında bir kopyası değil. Bu performans hakkında, bu mantıkla ilgili. - Drew Kennedy


Cevaplar:


Cevap YOK HAYIR fark, ama:

Her zaman aşağıdakileri yapmayı tercih edeceğim.

  • Her zaman tutmak Katılım Koşulları içinde ON fıkra
  • Her zaman koymak filtre en içinde where fıkra

Bu, sorguyu daha fazla yapar okunabilir.

Bu yüzden bu sorguyu kullanacağım:

SELECT value
FROM table1
INNER JOIN table2
        ON table1.id = table2.id
WHERE table1.id = 1

Ancak kullanırken OUTER JOIN'S filtrenin korunmasında büyük bir fark var. ON Durum ve Where şart.

Mantıksal Sorgu İşleme

Aşağıdaki listede, farklı cümlelerin mantıksal olarak işlendiği sıraya göre atanan adım numaraları ile birlikte genel bir sorgu formu bulunur.

(5) SELECT (5-2) DISTINCT (5-3) TOP(<top_specification>) (5-1) <select_list>
(1) FROM (1-J) <left_table> <join_type> JOIN <right_table> ON <on_predicate>
| (1-A) <left_table> <apply_type> APPLY <right_table_expression> AS <alias>
| (1-P) <left_table> PIVOT(<pivot_specification>) AS <alias>
| (1-U) <left_table> UNPIVOT(<unpivot_specification>) AS <alias>
(2) WHERE <where_predicate>
(3) GROUP BY <group_by_specification>
(4) HAVING <having_predicate>
(6) ORDER BY <order_by_list>;

Akış diyagramı mantıksal sorgulama işlemi

Enter image description here

  • (1) FROM: FROM aşaması, sorgunun kaynak tablolarını ve tablo operatörlerini işler. Her masa operatörü bir dizi uygular alt aşamalar. Örneğin, bir birleştirmede yer alan aşamalar (1-J1) Kartezyen ürün, (1-J2) AÇIK Filtre, (1-J3) Dış Satır Ekle. FROM faz sanal tablo VT1 üretir.

  • (1-J1) Kartezyen Ürün: Bu aşama bir Kartezyen ürünü gerçekleştirir Tablo operatöründe yer alan iki tablo arasında (çapraz birleştirmek) VT1-J1 üretiliyor.

  • (1-J2) ON Filtresi: Bu aşama, VT1-J1'den gelen satırları filtreler. ON yan tümcesinde (<on_predicate>) görünen yüklem. Bir tek Yüklemenin TRUE olarak değerlendirildiği satırlar VT1-J2.
  • (1-J3) Dış Satır Ekle: OUTER JOIN belirtilmişse (aksine CROSS JOIN veya INNER JOIN), korunan tablo veya tablolardan satırlar Bir eşleşme bulunamadığı için VT1-J2'den satırlara eklenir. VT1-J3 üreten dış sıralar.
  • (2) NEREDE: Bu aşama, satırları VT1’den WHERE yan tümcesinde () görünen yüklemeyi. Bir tek Yüklenicinin TRUE'ye değerlendirdiği satırlar VT2'ye eklenir.
  • (3) GROUP BY: Bu aşama gruplar halinde VT2'den satırları düzenler. GROUP BY deyiminde belirtilen sütun listesinde VT3 üretiliyor. Sonuçta, grup başına bir sonuç satırı olacak.
  • (4) HAVING: Bu faz, VT3'ten HAVING yan tümcesinde (<varolan_adı>) görünen yüklemeyi. Yalnızca TRICE için değerlendirmenin değerlendirildiği gruplar eklenir VT4 içine.
  • (5) SELECT: Bu aşama, SELECT maddesindeki öğeleri işler. VT5 üretiliyor.
  • (5-1) İfadeleri Değerlendirin: Bu aşama ifadeleri değerlendirir. SELECT listesi, VT5-1 üretiyor.
  • (5-2) DISTINCT: Bu aşama, çift satırları VT5-1'den kaldırır, VT5-2 üretiliyor.
  • (5-3) TOP: Bu aşama belirtilen üst sayı veya yüzdeyi filtreler ORDER tarafından tanımlanan mantıksal sıralamaya göre VT5-2 satırları BY tarafından, VT5-3 tablosunun oluşturulması.
  • (6) SİPARİŞ BY: Bu aşama, sıralara göre VT5-3 sıralar sıralar. imleci oluşturan ORDER BY yan tümcesinde belirtilen sütun listesi VC6.

Denir bu mükemmel bağlantı.


31
2017-12-24 15:28



Aynı ve kabul etti! Ayrıca özel senin katılımın ile. Yani. kullanım INNER veya LEFT-or-RIGHT OUTER Bir üye tanımlarken, IMO. - Pure.Krome
Bu ÖZEL soruya katılıyorum. OUTER birleşimleri, kriterler sınırlı eşleştirme kayıt kümesinde olduğunda sınırlama kriterlerinin yerleştirildiği yerde büyük bir fark yaratır. Filtre, yukarıdaki maddeye girebilir ve istenen sonuçlara ulaşmak için bazı durumlarda dış birleştirme kullanıldığında da gerekli olabilir. ya da nerede (örneğin, Noel Baba'nın aksine) (val='blue' or val is null)Özellikle de NULL, birleştirmede oluşturulanın ötesinde belirli bir anlamı varsa, bazen yanlış sonuçlar üretebilir. - xQbert
Konulu iken okunabilirliği: Ben de tavsiye ederim tutarlı Anahtar kelimelerinizle - hepsini UPPERCASE'a veya tümünü küçük harflere veya tümünü karışık bir kutuya koyun - hangisi umrumda değil - sadece TUTARLI - bir stil kullanmayınSELECT, FROM) ikinci bir tarzda karıştırıldı on, where) aynı sorguda ...... yapar Gerçekten mi T-SQL kodunu okumak zor… Bir stil seçin - hangisini seviyorsanız - ama sonra ÇUBUK ona ve tutarlı ol - marc_s
@xQbert - sanırım soru hakkında INNER JOIN. Bunu biliyorum Outer Join's büyük bir fark yaratmak. - Pரதீப்
@marc_s - evet kabul etti :) - Pரதீப்


Kullanırken hiçbir fark yoktur iken İç ortakVR46'nın işaret ettiği gibi, kullanım sırasında önemli bir fark var. DIŞ PARTİLER ve ikinci tablodaki bir değeri değerlendirmek (sol birleşmeler için - sağ birleşmeler için ilk tablo). Aşağıdaki ayarları göz önünde bulundurun:

DECLARE @Table1 TABLE ([ID] int)
DECLARE @Table2 TABLE ([Table1ID] int, [Value] varchar(50))

INSERT INTO @Table1
VALUES
(1),
(2),
(3)

INSERT INTO @Table2
VALUES
(1, 'test'),
(1, 'hello'),
(2, 'goodbye')

Eğer sol dış birleştirme kullanarak ondan seçer ve nerede bu cümleye bir koşul koyarsak:

SELECT * FROM @Table1 T1
LEFT OUTER JOIN @Table2 T2
    ON T1.ID = T2.Table1ID
WHERE T2.Table1ID = 1

Aşağıdaki sonuçları alırız:

ID          Table1ID    Value
----------- ----------- --------------------------------------------------
1           1           test
1           1           hello

Bunun nedeni, sonuç maddesinin sonuç kümesini sınırlandırmasıdır, bu nedenle yalnızca 1 kimliğine sahip olan tablo1'den kayıtları dahil ediyoruz. Ancak, koşulu şu maddeye taşırsak:

SELECT * FROM @Table1 T1
LEFT OUTER JOIN @Table2 T2
    ON T1.ID = T2.Table1ID
    AND T2.Table1ID = 1

Aşağıdaki sonuçları alırız:

ID          Table1ID    Value
----------- ----------- --------------------------------------------------
1           1           test
1           1           hello
2           NULL        NULL
3           NULL        NULL

Bunun nedeni, sonuç kümesini tablo1'in 1 kimliğiyle filtrelemememizdir - daha çok JOIN'i filtreliyoruz. Dolayısıyla, tablo1'in 2 DOES ID'si ikinci tabloda bir eşleşme olmasına rağmen, birleşimden hariç tutulur - ancak sonuç kümesi (yani null değerleri) DEĞİLDİR.

Yani, içsel birleşmeler için önemli değil, ama okunabilirlik ve tutarlılık için buradaki maddede tutmalısınız. Bununla birlikte, dış birleşimler için, sonucunuzu etkileyeceğinden, kondisyonu koydukça, bunun YETERSİZ OLDUĞUNUN farkında olmanız gerekir.


10
2017-12-24 15:48



Bu çok iyi açıklanmış örnek için teşekkürler. - cdabel


Bence "doğru" olarak işaretlenmiş cevap doğru değil. Niye ya? Açıklamaya çalışıyorum:

Görüşümüz var

"Her zaman Üyelik Koşullarını AÇIK cümlede tutun. Her zaman filtreyi yerleştirin   maddede nerede "

Ve bu yanlış. İç birleştirmede iseniz, her zaman, nerede değil, ON yan tümcesinde filtre paramları koyun. Nedenini soruyorsun? Karmaşık sorguyu toplam 10 tabloyla (örneğin her tablo 10k recs) birleştirerek karmaşık WHERE yan tümcesini (örneğin, kullanılan işlevler veya hesaplamalar) birleştirmeyi deneyin. Filtreleme ölçütlerini ON yan tümcesinde koyarsanız, bu 10 tablo arasında JOINS oluşmaz, WHERE yan tümcesinde yürütülmez. Bu durumda WHERE maddesinde 10000 ^ 10 hesaplamayı gerçekleştirmiyorsunuz. Bu, sadece WHERE deyimine filtreleme paramları koymamak anlamlıdır.


2
2018-01-16 12:18



Sorguları optimize eden veritabanları, orada daha iyi performans varsa WHERE koşullarını AÇIK duruma getirebilir. MySQL'de "EXPLAIN <sorgu>", durumun WHERE veya ON durumunda olup olmadığıyla aynı çıktıyı verir. - damjan