mysql - Warum funktioniert Abfrage in ON aber nicht in WHERE

[mark]

Guten Abend,

ich verstehe etwas Grundlegendes nicht und zwar:

Gegeben sei eine Übersetzungstabelle. Eine Zeile besteht aus einer "id", der Sprache "locale", einem Fremdschlüssel "foreign_key" und dem Übersetzten Text "text".

Ich erzeuge nun eine temporäre Tabelle mit welcher ich die Sprachen abgleichen möchte:

DROP TABLE IF EXISTS available_lang;
CREATE TEMPORARY TABLE IF NOT EXISTS `available_lang` (
      locale VARCHAR(6) DEFAULT NULL
);
INSERT INTO available_lang (locale) VALUES ('de_DE'),('en_US'),('it_IT'),('fr_FR');

Um die fehlenden Sprachen anzuzeigen verwende ich folgendes Query:

SELECT 
   -- alle spalten
 
    FROM available_lang

   LEFT JOIN i18n
       ON i18n.locale = available_lang.locale
       AND foreign_key = 'cc0d1b70-637d-4f1f-89e5-2d74f26226f5'

Das funktioniert.

Die Frage ist warum liefert folgendes Query nicht ein identisches Ergebnis:

SELECT 
    -- alle spalten

    FROM available_lang

   LEFT JOIN i18n
       ON i18n.locale = available_lang.locale

WHERE 
   foreign_key = 'cc0d1b70-637d-4f1f-89e5-2d74f26226f5'
   OR foreign_key IS NULL 

Müsste es doch? Aber ich glaube, ich übersehe da was Grundlegendes.

lg mark

[dedlfix]

Tach!

Um die fehlenden Sprachen anzuzeigen verwende ich folgendes Query:

Warum nicht

SELECT * FROM available_lang 
WHERE locale NOT IN (
  SELECT locale FROM i18n WHERE foreign_key = 'cc0d1b70-637d-4f1f-89e5-2d74f26226f5'
)

Die Frage ist warum liefert folgendes Query nicht ein identisches Ergebnis:

In der zweiten ist zusätzlich auch noch ein OR foreign_key IS NULL drin. Vielleicht ist es aber auch noch ein Unterschied, ob die Bedingung ´schon beim Joinen oder erst danach ausgewertet wird. Da bin ich mir nicht ganz klar. Jedenfalls versuche ich immer Joins zu verhindern. Subquerys sind meist besser verständlich, und vor allem auch separat testbar.

dedlfix.

[mark]

Danke für die Antwort.

Der Grund, warum ich kein Subquery verwende ist, weil ich gelesen habe, dass in der Regel, bzw. meistens JOINs performanter sind.

Meine i18n-Tabelle hat 638.340 Einträge (ich weiß nicht, ob das viel ist?) und ich joine da noch eine Tabelle, welche die identifier enthält, auf welche sich die Übersetzungen beziehen.

Ich werde mich mal einlesen, wie ich die Performance testen kann.

[dedlfix]

Tach!

Der Grund, warum ich kein Subquery verwende ist, weil ich gelesen habe, dass in der Regel, bzw. meistens JOINs performanter sind.

Mag sein, aber kommt das bei den drei Datensätzen auf Performance an?

Meine i18n-Tabelle hat 638.340 Einträge (ich weiß nicht, ob das viel ist?) und ich joine da noch eine Tabelle, welche die identifier enthält, auf welche sich die Übersetzungen beziehen.

Okay, es sind ein paar mehr. Aber ist dein Ziel, eine vereinigte Menge zu haben? Nein, dann versuch es erstmal ohne eine solche. Man kann am Ende immer noch eine Join-Variante daraus erstellen.

Ich werde mich mal einlesen, wie ich die Performance testen kann.

Performance hängt auch immer von den konkreten Umständen ab. Wenn deine i18n-Tabelle Indexe an den passenden Stellen hat, ist schon mal eine Menge gewonnen.

dedlfix.

[mark]

Mag sein, aber kommt das bei den drei Datensätzen auf Performance an?

Nein. Das kommt es in diesem Fall nicht, da hast du recht.

"Alles mit Joins machen" war für mich Übung, damit ich nicht dauernd bei mehreren Joins grübeln muss ... und jetzt ist mir das eben hängen geblieben. Vielleicht ist das die eigentliche Erklärung :)

[mark]

... hmm wahrscheinlich, weil NULL ein Wert in einem Feld sein muss und eben nicht der Wert, welcher zurückgegeben wird, wenn ein Feld gar nicht existiert?

Ist diese Erklärung richtig? Dann nämlich wäre meine anschließende Frage: Kann man das dann überhaupt im WHERE machen?

[mark]

Ich poste zum besseren Verständnis die unterschiedlichen Ergebnisse.

Bei der Abfrage mit WHERE fehlt Französisch fr_FR. Dieser Eintrag fehlt in i18n.

Mit der Abfrage im JOIN:

avaiable_locale	i18n_id	locale	text	foreign_key
de_DE	b28f3622-5480-4c52-8356-e476e897c991	de_DE	andere	cc0d1b70-637d-4f1f-89e5-2d74f26226f5
en_US	84285a7a-2147-4c2d-94df-5ff2754c4033	en_US	other	cc0d1b70-637d-4f1f-89e5-2d74f26226f5
it_IT	b58a1117-01dd-450b-8526-223bb7e8b525	it_IT	altro	cc0d1b70-637d-4f1f-89e5-2d74f26226f5
fr_FR	\N	\N	\N	\N

Mit der Abfrage im WHERE:

avaiable_locale	i18n_id	locale	text	foreign_key
de_DE	b28f3622-5480-4c52-8356-e476e897c991	de_DE	andere	cc0d1b70-637d-4f1f-89e5-2d74f26226f5
en_US	84285a7a-2147-4c2d-94df-5ff2754c4033	en_US	other	cc0d1b70-637d-4f1f-89e5-2d74f26226f5
it_IT	b58a1117-01dd-450b-8526-223bb7e8b525	it_IT	altro	cc0d1b70-637d-4f1f-89e5-2d74f26226f5

[Rolf B]

Hallo mark,

ich habe das gerade minimalistisch nachzustellen versucht - bei mir tritt dieser Effekt nicht auf.

CREATE TEMPORARY TABLE IF NOT EXISTS lang (
      locale VARCHAR(6) DEFAULT NULL
);

INSERT INTO lang (locale) VALUES ('de_DE'),('en_US'),('it_IT'),('fr_FR');

CREATE TEMPORARY TABLE IF NOT EXISTS texte (
      locale VARCHAR(6) DEFAULT NULL,
      id     VARCHAR(6) DEFAULT NULL,
      textid VARCHAR(6) DEFAULT NULL
);
INSERT INTO texte (locale, id, textid) VALUES 
   ('de_DE', '0001', '0001'),   ('en_US', '0002', '0001'),   ('it_IT', '0003', '0001'),
   ('de_DE', '0011', '0002'),   ('en_US', '0012', '0002'),   ('it_IT', '0013', '0002');

select * 
from lang left join texte on lang.locale = texte.locale
where textid='0002' or textid is null

Die fr_FR Zeile ist im Ergebnis drin, und das ist so auch zu erwarten.

D.h. bei Dir kommt noch irgend ein anderer Effekt hinzu, den du uns nicht zeigst.

Rolf

[mark]

Hallo Rolf,

hmmm ... ich übersehe da etwas ... dein Beispiel funktioniert bei mir auch! Aber ich sehe den Unterschied zwischen deinem und meinem Beispiel nicht.

Ich habe nun ein POC zusammengebastelt mit meinen Daten. Hier tritt der "Fehler" auf. Könntest du das bitte ausführen und den output verifizieren?

-- define temporary available lang
DROP TEMPORARY TABLE IF EXISTS available_lang;
CREATE TEMPORARY TABLE IF NOT EXISTS `available_lang` (
      locale VARCHAR(6) DEFAULT NULL
);
INSERT INTO available_lang (locale) VALUES ('de_DE'),('en_US'),('it_IT'),('fr_FR');

-- define temporary i18n_test
DROP TEMPORARY TABLE IF EXISTS i18n_test;
CREATE TEMPORARY TABLE IF NOT EXISTS `i18n_test` (
      id VARCHAR(36),
      locale VARCHAR(6) DEFAULT NULL,
      foreign_key VARCHAR(36) DEFAULT NULL,
      `text` MEDIUMTEXT
);
INSERT INTO `i18n_test` (`id`, `locale`, `foreign_key`, `text`) VALUES ('84285a7a-2147-4c2d-94df-5ff2754c4033', 'en_US', 'cc0d1b70-637d-4f1f-89e5-2d74f26226f5', 'other');
INSERT INTO `i18n_test` (`id`, `locale`, `foreign_key`, `text`) VALUES ('b28f3622-5480-4c52-8356-e476e897c991', 'de_DE', 'cc0d1b70-637d-4f1f-89e5-2d74f26226f5', 'andere');
INSERT INTO `i18n_test` (`id`, `locale`, `foreign_key`, `text`) VALUES ('b58a1117-01dd-450b-8526-223bb7e8b525', 'it_IT', 'cc0d1b70-637d-4f1f-89e5-2d74f26226f5', 'altro');


-- query with join
SELECT
	available_lang.locale AS available_locale
	, i18n_test.id
	, i18n_test.foreign_key
	, i18n_test.locale AS i18n_locale
	, i18n_test.`text`
	
	FROM available_lang 
	LEFT JOIN  i18n_test
		ON i18n_test.locale = available_lang.locale
		AND i18n_test.foreign_key = 'cc0d1b70-637d-4f1f-89e5-2d74f26226f5';


		
-- query with where
SELECT
	available_lang.locale AS available_locale
	, i18n_test.id
	, i18n_test.foreign_key
	, i18n_test.locale AS i18n_locale
	, i18n_test.`text`
	
	FROM available_lang 
	LEFT JOIN  i18n_test
		ON i18n_test.locale = available_lang.locale

WHERE i18n_test.foreign_key = 'cc0d1b70-637d-4f1f-89e5-2d74f26226f5'
	OR i18n_test.foreign_key = NULL;

Ergebnis erstes Query (JOIN):

available_locale	id	foreign_key	i18n_locale	text
en_US	84285a7a-2147-4c2d-94df-5ff2754c4033	cc0d1b70-637d-4f1f-89e5-2d74f26226f5	en_US	other
de_DE	b28f3622-5480-4c52-8356-e476e897c991	cc0d1b70-637d-4f1f-89e5-2d74f26226f5	de_DE	andere
it_IT	b58a1117-01dd-450b-8526-223bb7e8b525	cc0d1b70-637d-4f1f-89e5-2d74f26226f5	it_IT	altro
fr_FR	\N	\N	\N	\N

Ergebnis zweites Query (WHERE fr_FR fehlt):

available_locale	id	foreign_key	i18n_locale	text
de_DE	b28f3622-5480-4c52-8356-e476e897c991	cc0d1b70-637d-4f1f-89e5-2d74f26226f5	de_DE	andere
en_US	84285a7a-2147-4c2d-94df-5ff2754c4033	cc0d1b70-637d-4f1f-89e5-2d74f26226f5	en_US	other
it_IT	b58a1117-01dd-450b-8526-223bb7e8b525	cc0d1b70-637d-4f1f-89e5-2d74f26226f5	it_IT	altro

[mark]

Ich hab den Unterschied gefunden!!

Es ist ein Unterschied ob man schreibt "IS NULL" oder "= NULL".

"IS NULL" liefert das von mir erwartete Ergebnis.

Ich hatte zwar auch mal "IS NULL" in meinen vorhergehenden Post verwendet, aber da habe ich das Query vereinfacht und es kam wahrscheinlich noch ein weiterer Fehler hinzu.

So ... und jetzt noch die MySql Dokumentation durchlesen um zu erfahren warum das so ist.

[dedlfix]

Tach!

Es ist ein Unterschied ob man schreibt "IS NULL" oder "= NULL".

Ja, das war der Teil, den du uns nicht gezeigt hast.

Wann immer du eine Operation mit NULL als einem Teil der Operanden ausführst ist das Ergebnis des Ausdrucks NULL. Deshalb kannst du nicht auf = NULL testen, weil das Ergebnis ebenfalls NULL ist und im booleschen Kontext als false erkannt wird. Nur der spezielle Operator IS berücksichtigt das NULL angemessen und liefert ein boolesches Ergebnis.

dedlfix.

[pl]

Was soll denn dieser join bringen? Deine Tabelle available_locale hat ein einziges Feld über das Du joinst wobei dieses Feld in der anderen Tabelle NULL sein kann. Damit dürfte sich mit ON .. etwas undefinierbares ergeben, bestenfalls NULL. MfG