SQL, verschachtelte Unterabfragen funktionieren nicht

[MartinL]

Hallo, als Anfänger versuche ich mich an einer verschachtelten Unterabfrage, die nicht funktionieren will. Wahrscheinlich habe ich einen Denkfehler, den ich nicht erkenne. Ich habe mit PHPMyAdmin eine kleine Testdatenbank erstellt mit einer Tabelle "test" und 2 Spalten. Eine Spalte als Zeilen id und eine Spalte mit Integer-Zahlen. Mein Ziel ist es, zunächst in der zweiten Unterabfrage den Mittelwert der Spalte "zahl1" zu errechnen, in der ersten Unterabfrage diesen errechneten Mittelwert von den Zahlen abzuziehen und in der äußeren Abfrage dann zu diesem Ergebnis "27" hinzuzuaddieren. Die Berechnung des Mittelwertes funktioniert, die erste Unterabfrage (Subtraktion des Mittelwertes vom Zahlenwert) funktioniert. Beide Unterabfragen zusammen funktionieren. Wenn jedoch die äußere Abfrage hinzukommt, die Addition von "27" zu dem vorherigen Ergebnis, funktioniert es SQL Fragenicht. Mein Code:

SELECT `zaehler` + 27 AS `Ergebnis`
FROM `test`,
(SELECT `zahl1`, (`zahl1` - `mw`) AS `zaehler`
FROM `test` AS `t1`,
(SELECT AVG(`zahl1`) AS `mw`
FROM `test`) AS `t2`);

Ausgabe PHPMyAdmin:

SELECT `zaehler` + 27 AS `Ergebnis` 
FROM `test` (SELECT `zahl1`, (`zahl1` - `mw`) AS `zaehler` 
FROM `test`, (SELECT AVG(`zahl1`) AS `mw` FROM `test`) AS `t2`)
LIMIT 0, 25

#1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei 'LIMIT 0, 25' in Zeile 6

PHPMyAdmin fügt dabei an das Ende meines Codes noch "LIMIT 0, 25" an??

Wo liegt mein Fehler?

Danke für Hinweise
Martin

[Rolf B]

Hallo MartinL,

ich habe deinen Code formatiert. Dazu schreibt man eine Zeile ~~~sql vor die Abfrage und dahinter eine Zeile mit ~~~

Wenn ich das mal versuche, passend einzurücken:

SELECT `zaehler` + 27 AS `Ergebnis`
FROM `test`,
     (SELECT `zahl1`, (`zahl1` - `mw`) AS `zaehler`
      FROM `test` AS `t1`,
           (SELECT AVG(`zahl1`) AS `mw` FROM `test`) AS `t2`
     );

dann hätte ich drei Hinweise:

(1) Table-Ausdrücke in der FROM-Klausel brauchen immer einen Aliasnamen. D.h. direkt vor dem Semikolon muss noch ein AS t3 oder so stehen. Der fehlt ihm, und deswegen meckert er, dass vor dem LIMIT 0,25 was faul sei.

(2) LIMIT 0,25 fügt PHPmyadmin ein, richtig. Wenn Du mal schaust, siehst Du ein Auswahlfeld, wieviele Zeilen vom Ergebnis er anzeigen soll. Das steht per Default auf 25. Und da es beim ersten Ausführen die ersten 25 Zeilen sein sollen, muss er 0 Zeilen überspringen. Auf Mysqlesisch: LIMIT 0,25.

(3) Was zum grundgütigen Geier tust Du da? Das ist ein bedingungsloser Join der TEST Tabelle mit der Ergebnistabelle, die ich t3 genannt habe. Und t3 besteht wiederum aus einem bedingungslosen Join von test mit t2. t2 hat immerhin nur eine Row, d.h. t2 ist genauso groß wie test, aber dann entsteht ein kartesisches Produkt test × t3. Falls test 1000 Zeilen hat, besteht t3 aus 1000 Rows und das Gesamtergebnis aus einer Million Rows, von denen jeweils 1000 identisch sind, weil Du die zahl1 Spalte aus t3 nicht verwendest.

Willst Du für alle Rows in Test die Differenz von zahl1 zum Mittelwert aller zahl1 wissen, erhöht um 27? Also $$x - \overline x + 27$$?

SELECT (27 + zahl1 - (SELECT AVG(zahl1) FROM test)) AS Ergebnis
FROM test

Backticks sind ein Sicherheitsnetz gegen irrtümlich verwendete Schlüsselwörter in SQL Queries und werden auch gebraucht, wenn eine Spalte kleinster Wert heißen soll. phpMyAdmin generiert sie stumpf um jeden SQL Namen herum. Aber nötig sind sie deswegen nicht; ich lasse sie weg, wann immer es geht.

Wenn Du noch andere Dinge in der Query hast, die die Komplexität nötig machen und die Du zum Nachstellen des Fehlers weggelassen hast, ist das natürlich was anderes. Aber vielleicht solltest Du uns dann deine reale Query präsentieren, ggf. gibt's da auch Optimierungspotenzial.

Rolf

[MartinL]

Hallo Rolf, danke für die Hilfe. Der Tip mit Einfügung vn "t3" war der richtige Hinweis. Jetzt funktioniert es. Dieser Versuch ist tatsächlich nur eine kleine Übung für mich, um die Funktionsweise verschachtelter Unterabfragen zu verstehen und mich mit der nötigen Syntax vetraut zu machen. Deine Hinweise, daß diese Vorgehensweise in diesem Fall wahrscheinlich vollkommen falsch ist, werde ich noch einmal aufmwerksam betrachten. Jedenfalls kann ich jetzt dank Deiner Hilfe noch einige weitere (vielleicht auch schwachsinnige) Übungen ausprobieren. Martin

[Rolf B]

Hallo MartinL,

wichtig ist, dass Joins begrenzt werden. Wenn Du

SELECT *
FROM tableA, tableB

schreibst und nicht per WHERE Bedingung einschränkst, welche Rows in tableA mit welchen Rows in tableB zusammengehörig sind, dann erzeugt SQL ein kartesisches Produkt, d.h. jede Row aus tableA wird mit jeder Row aus tableB kombiniert.

Die Komma-Schreibweise ist übrigens die alte Form eines inneren JOIN. Heute schreibt man das so:

SELECT *
FROM tableA JOIN tableB ON tableA.spalteX = tableB.spalteY

Die ON-Klausel enthält den Teil der WHERE-Bedingungen, der für die Verknüpfung der Tabellen relevant ist.

Rolf