UNION in IN() als Teil einer WHERE-Klausel?

[Auge]

Hallo

Ich möchte in MySQL eine UNION-Abfrage, die ein LIMIT enthält, in einer Bedingung der WHERE-Klausel benutzen. Der UNION an sich funktioniert und bringt mir – wegen des impliziten DISTINCTs – wie erwartet zwischen 6 und 12 Ergebniszeilen.

(SELECT id FROM entries WHERE spam = 0 ORDER BY time DESC LIMIT 6)
UNION
(SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6)

Setze ich diese Abfrage nun in die WHERE-Klausel einer anderen Abfrage …

SELECT
 id,
 user_id,
 name,
 time,
 subject,
 category
FROM entries
 LEFT JOIN userdata
  ON userdata.user_id = entries.user_id
 WHERE entries.id IN((SELECT id FROM entries WHERE spam = 0 ORDER BY time DESC LIMIT 6) UNION (SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6))
 ORDER BY time DESC
 LIMIT 6

… meldet mir MySQL (in phpMyAdmin, worin ich das Konstrukt teste) einen Fehler (Zeilenumbrüche von mir).

#1064 - You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near
'UNION (SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6))' at line 11

und

Ein Ausdruck wurde erwartet. (bei FROM)

Gibt's da zu viele LIMIT- und/oder ORDER BY-Anweisungen oder wo liegt mein Fehler?

Tschö, Auge

[dedlfix]

Tach!

 WHERE entries.id IN((SELECT id FROM entries WHERE spam = 0 ORDER BY time DESC LIMIT 6) UNION (SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6))

Hast du mal die Klammern um die Einzel-Selects weggelassen? Oder noch eine Klammer um das Gesamt-Union-Konstrukt gesetzt? Oder beides?

#1064 - You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near
'UNION (SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6))' at line 11

Ich vermute, dass MySQL aufgrund der öffnenden Klammer eine Subquery erwartet. Und die endent nach der ), woraufhin Syntax folgt, die in einem IN() falsch ist.

dedlfix.

[Auge]

Hallo

 WHERE entries.id IN((SELECT id FROM entries WHERE spam = 0 ORDER BY time DESC LIMIT 6) UNION (SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6))

Hast du mal die Klammern um die Einzel-Selects weggelassen?

Nein. Da die einzelnen zum UNION gehörenden SELECTs jeweils ihre eigenen ORDER BY edited DESC LIMIT 6 haben, sollen sie laut der MySQL-Doku geklammert werden, damit die letzen Bedingungen nicht auf den gesamten UNION angewandt werden. Also habe ich es nicht anders probiert.

Oder noch eine Klammer um das Gesamt-Union-Konstrukt gesetzt? Oder beides?

Das werde ich morgen noch einmal probieren.

#1064 - You have an error in your SQL syntax;
check the manual … for the right syntax to use near
'UNION (SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6))' at line 11

Ich vermute, dass MySQL aufgrund der öffnenden Klammer eine Subquery erwartet. Und die endent nach der ), woraufhin Syntax folgt, die in einem IN() falsch ist.

Hmm. Klammer auf vom IN, Klammer auf des ersten SELECT, Klammer zu des ersten SELECT, UNION, Klammer auf des zweiten SELECT, Klammer zu des zweiten SELECT und schließlich Klammer zu von IN. Eigentlich sollte das stimmen.

WHERE … IN((SELECT … ORDER BY time DESC LIMIT 6) UNION (SELECT … ORDER BY edited DESC LIMIT 6))

Danke erst einmal für den Denkanstoß.

Tschö, Auge

[dedlfix]

Tach!

Hast du mal die Klammern um die Einzel-Selects weggelassen?

Nein. Da die einzelnen zum UNION gehörenden SELECTs jeweils ihre eigenen ORDER BY edited DESC LIMIT 6 haben, sollen sie laut der MySQL-Doku geklammert werden, damit die letzen Bedingungen nicht auf den gesamten UNION angewandt werden. Also habe ich es nicht anders probiert.

Gut, dann wird es wohl so sein, dass du noch ein Klammernpaar um die gesamte Subquery brauchst. Subquerys brauchen immer eine Klammerung, auch wenn bereits Klammern (zum Beispiel die vom IN()) vorhanden sind.

Ich vermute, dass MySQL aufgrund der öffnenden Klammer eine Subquery erwartet. Und die endent nach der ), woraufhin Syntax folgt, die in einem IN() falsch ist.

Hmm. Klammer auf vom IN, Klammer auf des ersten SELECT,

Begin der Subquery

Klammer zu des ersten SELECT,

Ende der Subquery

UNION,

Nicht mehr Teil der Subquery → Fehler.

dedlfix.

[Auge]

Hallo

Da die einzelnen zum UNION gehörenden SELECTs jeweils ihre eigenen ORDER BY edited DESC LIMIT 6 haben, sollen sie laut der MySQL-Doku geklammert werden, damit die letzen Bedingungen nicht auf den gesamten UNION angewandt werden. Also habe ich es nicht anders probiert.

Gut, dann wird es wohl so sein, dass du noch ein Klammernpaar um die gesamte Subquery brauchst. Subquerys brauchen immer eine Klammerung, auch wenn bereits Klammern (zum Beispiel die vom IN()) vorhanden sind.

Das klingt nach einer schlüssigen Erklärung. [edit]Im übrigen riecht mir auch dieser Thread aus dem November 2013 nach der selben Fehlerursache.[/edit]

Hmm. Klammer auf vom IN, Klammer auf des ersten SELECT,

Begin der Subquery

Klammer zu des ersten SELECT,

Ende der Subquery

UNION,

Nicht mehr Teil der Subquery → Fehler.

Tja, dann testen wir morgen mal.

Tschö, Auge

[MudGuard]

Hi,

Ich möchte in MySQL eine UNION-Abfrage, die ein LIMIT enthält, in einer Bedingung der WHERE-Klausel benutzen. Der UNION an sich funktioniert und bringt mir – wegen des impliziten DISTINCTs – wie erwartet zwischen 6 und 12 Ergebniszeilen.

(SELECT id FROM entries WHERE spam = 0 ORDER BY time DESC LIMIT 6)
UNION
(SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6)

Setze ich diese Abfrage nun in die WHERE-Klausel einer anderen Abfrage … … meldet mir MySQL (in phpMyAdmin, worin ich das Konstrukt teste) einen Fehler (Zeilenumbrüche von mir).

IIRC mag MySQL union nicht in direkter Subquery. Was aber funktionieren soll, ist, die unionische Subquery nochmal in ein "Select x from (select x from ... union select x from ...) zu verpacken, und das dann in das where z in (...) einzuwickeln.

Ohne Garantie, ist schon länger her, daß ich mit MySQL gearbeitet hab.

cu,
Andreas a/k/a MudGuard

[Auge]

Hallo @dedlfix, @MudGuard und alle anderen

Leider hat weder der Tip, den gesamten UNION-SELECT innerhalb IN() einzuklammern, noch der, den UNION selbst noch einmal zu selektieren, funktioniert. MySQL meldet weiterhin, wie im OP gezeigt, einen Syntaxfehler.

Die Nutzerkommentare in der MySQL-Doku zu UNION brachten mich aber auf die Idee, den UNION als dessen Basis in den FROM-Block zu stecken.

SELECT
 ent.id,
 ent.user_id,
 ent.name,
 udat.user_name,
 ent.time,
 ent.subject,
 ent.category
FROM ((
 SELECT id, pid, tid, name, user_id, time, edited, last_reply, subject, category FROM entries WHERE spam = 0 ORDER BY time DESC LIMIT 6)
  UNION (
 SELECT id, pid, tid, name, user_id, time, edited, last_reply, subject, category FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6))
 AS ent
 LEFT JOIN userdata AS udat
  ON udat.user_id = ent.user_id
 ORDER BY time DESC
 LIMIT 6

Das funktioniert erst einmal grundsätzlich, auch wenn ich noch gewaltig an den Details feilen muss. Ich melde mich dann noch einmal mit weiteren Fragen oder dem Endergebnis.

Tschö, Auge

[dedlfix]

Tach!

Leider hat weder der Tip, den gesamten UNION-SELECT innerhalb IN() einzuklammern, noch der, den UNION selbst noch einmal zu selektieren, funktioniert. MySQL meldet weiterhin, wie im OP gezeigt, einen Syntaxfehler.

Muss es unbedingt eine einzelne Monster-Query sein, oder kannst du das Union-Ergebnis erstmal in einer temporären Tabelle ablegen und diese dann nutzen?

dedlfix.

[Auge]

Hallo

Leider hat weder der Tip, den gesamten UNION-SELECT innerhalb IN() einzuklammern, noch der, den UNION selbst noch einmal zu selektieren, funktioniert. MySQL meldet weiterhin, wie im OP gezeigt, einen Syntaxfehler.

Muss es unbedingt eine einzelne Monster-Query sein, oder kannst du das Union-Ergebnis erstmal in einer temporären Tabelle ablegen und diese dann nutzen?

Nein, eine Zwischenlagerung fällt aus, aber mit einem INNER JOIN auf die Tabelle selbst komme ich natürlich mit dem Feld „id“ als Ergebnis des UNION aus. Besser?

SELECT
 ent1.id,
 ent2.user_id,
 ent2.name,
 udat.user_name,
 ent2.time,
 ent2.subject,
 ent2.category
FROM (((
 SELECT id FROM entries WHERE spam = 0 ORDER BY time DESC LIMIT 6)
  UNION (
 SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6))
 AS ent1
 INNER JOIN entries AS ent2
  ON ent1.id = entries.id)
 LEFT JOIN userdata AS udat
  ON udat.user_id = entries.user_id
 ORDER BY ent2.time DESC
 LIMIT 6

Tschö, Auge

[dedlfix]

Tach!

Muss es unbedingt eine einzelne Monster-Query sein, oder kannst du das Union-Ergebnis erstmal in einer temporären Tabelle ablegen und diese dann nutzen?

Nein, eine Zwischenlagerung fällt aus,

Mit temporärer Tabelle meinte ich den im System enthaltenen Mechanismus, eine solche Tabelle für die aktuelle Sitzung zu erstellen, die dann am Ende von selbst wieder verschwindet. Das ist auch nicht besonders aufwändig: CREATE TEMPORARY TABLE name AS SELECT ...

Besser?

Vielleicht kann man noch das LEFT JOIN entsorgen und stattdessen den Wert user_name (ist ja anscheinend nur dieser eine) per correlated Subquery in der Select-Klausel ermitteln. Das nimmt ein Stück Komplexität aus der FROM-Klausel raus, und diese Tabelle wird für das Erstellen der Zwischenergebnismenge nicht benötigt.

dedlfix.

[Auge]

Hallo

Muss es unbedingt eine einzelne Monster-Query sein, oder kannst du das Union-Ergebnis erstmal in einer temporären Tabelle ablegen und diese dann nutzen?

Nein, eine Zwischenlagerung fällt aus,

Mit temporärer Tabelle meinte ich den im System enthaltenen Mechanismus, eine solche Tabelle für die aktuelle Sitzung zu erstellen, die dann am Ende von selbst wieder verschwindet.

Ich weiß nicht so recht, ob das sinnvoll ist. Ich habe bisher einfach noch keinen Anwendungsfall für temporäre Tabelle gehabt. Daher kann ich die Vor- und Nachteile bezüglich Performanz und Komplexität überhaupt nicht einschätzen.

Wenn ich mit PHP den Query zur Erstellung einer temporären Tabelle absetze, bleibt die Tabelle bis zum schließen der Verbindung vorhanden?

Vielleicht kann man noch das LEFT JOIN entsorgen und stattdessen den Wert user_name (ist ja anscheinend nur dieser eine) per correlated Subquery in der Select-Klausel ermitteln. Das nimmt ein Stück Komplexität aus der FROM-Klausel raus, und diese Tabelle wird für das Erstellen der Zwischenergebnismenge nicht benötigt.

Da der Name aus der Eintrags- oder der Benutzertabelle kommen kann, habe ich das mit einem IF() gelöst. Leider wird die „user_id“, so sie nicht vorhanden ist, mit einer „0“ statt mit NULL abgespeichert. Daher kommt der Vergleich mit ent2.user_id > 0. Die „user_id“ fällt dafür aus dem Abfrageergebnis heraus.

Stattdessen kommt nun das Feld „edited“ mit einem Zeitstempel oder NULL als Beweis für einen geänderten Eintrag hinzu. Zudem kommt ein zweites IF() hinzu, mit dem zwischen den Zeitstempeln für den Eintrag bzw. der letzten Bearbeitung gewählt wird. Mit diesem wird die gesamte Ergebnismenge sortiert.

Das alles lässt die Ausführungszeit für den Query beim ersten Aufruf zwar etwa auf nicht ganz das Doppelte der vorher vorhandenen Lösung steigen, aber danach, aus dem Cache, ist der Query so schnell, wie gehabt.

SELECT
 ent1.id,
 (IF(ent2.user_id > 0, (SELECT user_name FROM userdata WHERE userdata.user_id = ent2.user_id), ent2.name)) AS name,
 ent2.time,
 ent2.edited,
 ent2.subject,
 ent2.category,
 (IF(ent2.edited IS NOT NULL, ent2.edited, entries.time)) AS orderTime
FROM ((
 SELECT id FROM entries WHERE spam = 0 ORDER BY time DESC LIMIT 6)
  UNION (
 SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6))
 AS ent1
 INNER JOIN entries AS ent2
  ON ent1.id = ent2.id
 ORDER BY orderTime DESC
 LIMIT 6

Tschö, Auge

[dedlfix]

Tach!

Wenn ich mit PHP den Query zur Erstellung einer temporären Tabelle absetze, bleibt die Tabelle bis zum schließen der Verbindung vorhanden?

Ja, so arbeiten temporäre Tabellen. Eine kürzere Zeitdauer wäre kaum sinnvoll, eine längere auch nicht.

Da der Name aus der Eintrags- oder der Benutzertabelle kommen kann,

Wenn das am Ende doch komplexer ist, als du zeigst, dann ist meine Antwort natürlich gegenstandslos - was auch nicht schlimm ist.

Das alles lässt die Ausführungszeit für den Query beim ersten Aufruf zwar etwa auf nicht ganz das Doppelte der vorher vorhandenen Lösung steigen, aber danach, aus dem Cache, ist der Query so schnell, wie gehabt.

Zu Performance kann ich nichts sagen. Zum einen waren meine Datenmengen bisher nicht so groß, dass ich großartig optimieren hätte müssen, und da fehlt mir dann Erfahrung. Mir kommt es vor allem auf die Lesbarkeit an, und wenn die lesbarere Query länger braucht, hat mich das bisher nicht gestört, weil es nicht aufgefallen ist.

dedlfix.

[James Moriarty]

Sehr subtiler Fehler :)

Das geht:

(SELECT id FROM entries WHERE spam = 0 ORDER BY time DESC LIMIT 6)
UNION
(SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6)

Aber das nicht:

SELECT * FROM
(SELECT id FROM entries WHERE spam = 0 ORDER BY time DESC LIMIT 6)
UNION
(SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6)

Und die Meldung lautet hier: #1248 - Every derived table must have its own alias !

Mit einem Alias geht der Fehler weg:

SELECT * FROM
(SELECT id FROM entries WHERE spam = 0 ORDER BY time DESC LIMIT 6) E
UNION
(SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6)

Aber das ist ja für die IN-Klausel nicht das, was Du willst. Und einen Alias-Namen DIREKT innerhalb einer IN Klausel nimmt der Bursche auch nicht.

Das Problem sind die Klammern um die beiden Subselects, die sind nicht nötig und führen nur dazu, dass sie als namenspflichtige Subselects behandelt werden. Das brauchst Du aber gar nicht.

SELECT  id, user_id, name, time, subject, category
FROM entries LEFT JOIN userdata ON userdata.user_id = entries.user_id
WHERE entries.id IN (SELECT id FROM entries WHERE spam = 0 ORDER BY time DESC UNION SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC)

behebt das Alias-Problem, aber leider muss man die LIMITs weglassen, sonst liefert MySQL 5.6.33 diese Meldung:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Welche MySQL Version das unterstützt, habe ich nicht wirklich herausgefunden, aber wir haben Glück, es gibt einen Workaround (Quelle). Man tarnt die LIMITs durch einen dazwischengeschobenen Subselect:

SELECT  id, user_id, name, time, subject, category
FROM entries LEFT JOIN userdata ON userdata.user_id = entries.user_id
WHERE entries.id IN (
   SELECT id 
   FROM (SELECT id FROM entries WHERE spam = 0 ORDER BY time DESC LIMIT 6
         UNION
         SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6) X )

Beachte das X zwischen den beiden letzten Klammern, sonst kommt wieder die Meldung mit dem fehlenden Alias.

Getestet auf MySQL 5.6.33.

J

[Auge]

Hallo

das [geht] nicht:

SELECT * FROM
(SELECT id FROM entries WHERE spam = 0 ORDER BY time DESC LIMIT 6)
UNION
(SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6)

Mit einem zusätzlichen Paar Klammern um den gesamten UNION-SELECT geht das auch (auch ohne Alias).

SELECT * FROM (
  (SELECT id FROM entries WHERE spam = 0 ORDER BY time DESC LIMIT 6)
    UNION
  (SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6)
)

Und so habe ich das, wie einen Zweig weiter zu lesen ist, auch gemacht.

Das Problem sind die Klammern um die beiden Subselects, die sind nicht nötig und führen nur dazu, dass sie als namenspflichtige Subselects behandelt werden. Das brauchst Du aber gar nicht.

Für MySQL 5.5 (was bei mir läuft) und dein MySQL 5.6 wird das stimmen, aber ab MySQL 5.7 müssen die einzelnen SELECTs eingeklammert werden, wenn sie jeweils einzelne ORDER-BY- und LIMIT-Klauseln haben.

Aus der Doku zu UNION:

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

Note

Previous versions of MySQL may permit such statements without parentheses. In MySQL 5.7, the requirement for parentheses is enforced.

Nun bin ich – offensichtlich fälschlicherweise – davon ausgegangen, dass das auch vor 5.7 schon (in der von mir gewünschten Weise) fuunktioniert („Previous versions … may permit … statements without parentheses … MySQL 5.7 the requirement … is enforced“). Das, zumal ich die auf den verschiedenen Zielsystemen installierten MySQL-Versionen nicht kenne, die Klammerpflicht von/ab MySQL 5.7 also durchaus zuschlagen könnte.

Ich habe mittlerweile zwar eine funktionierende Lösung, werde deinen Query aber trotzdem ausprobieren.

SELECT  id, user_id, name, time, subject, category
FROM entries LEFT JOIN userdata ON userdata.user_id = entries.user_id
WHERE entries.id IN (
   SELECT id 
   FROM (SELECT id FROM entries WHERE spam = 0 ORDER BY time DESC LIMIT 6
         UNION
         SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6) X )

Danke auch dir.

Tschö, Auge

[Rolf b]

Mit einem zusätzlichen Paar Klammern um den gesamten UNION-SELECT geht das auch (auch ohne Alias).

SELECT * FROM (
  (SELECT id FROM entries WHERE spam = 0 ORDER BY time DESC LIMIT 6)
    UNION
  (SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6)
)

Unter mysql 5.6 will er hinter der letzten Klammer auch bei mir einen Aliasnamen haben! Sehr merkwürdig, das würde ich als einen unnötig erbrechenden Change sehen.

Warum unter 5.7 Klammern um die ge-UNION-ten Selects nötig sind, ist mir nicht ganz einsichtig. Aber wenn Larry es so haben will, dann neigen wir demütig unser Haupt vor dem Orakel und sagen "Sehr wohl, Oh Sohn der Elli".

Rolf

[Auge]

Hallo

Mit einem zusätzlichen Paar Klammern um den gesamten UNION-SELECT geht das auch (auch ohne Alias).

SELECT * FROM (
  (SELECT id FROM entries WHERE spam = 0 ORDER BY time DESC LIMIT 6)
    UNION
  (SELECT id FROM entries WHERE spam = 0 ORDER BY edited DESC LIMIT 6)
)

Warum unter 5.7 Klammern um die ge-UNION-ten Selects nötig sind, ist mir nicht ganz einsichtig.

Um die ORDER BY und/oder LIMIT-Anweisungen der Einzelselects von denen des gesamten UNION zu unterscheiden, ist das für mich einsichtig. Ich wollte das ja auch so machen, um eine Vorfilterung, aus der ich eine schlusseinliche Auswahl treffe, vornehmen zu können.

Ziel war es, (angenommene) sechs Datensätze auszuwählen, die aus den neuen und/oder editierten Datensätzen stammen können. Mein Plan, hole die sechs neuesten frisch angelegten und die sechs neuesten editierten Datensätze und wähle aus dieser Menge von mindestens sechs bis maximal zwölf Datensätzen, zeitlich absteigend sortiert, die sechs neuesten Datensätze aus. Also brauche ich schon in den einzelnen Selects die Sortierung und das Limit, um dort nur noch die relevanten Einträge zu haben und dann noch einmal die gleichartige Sortierung-Limit-Kombi für das Gesamtergebnis. Um die beiden Fälle unterscheiden zu können, halte ich eine Klammerung durchaus für sinnvoll.

Tschö, Auge