MySQL Full Outer Join Syntax Error

Question

Ich habe 2 MySQL Tabellen.

CREATE TABLE IF NOT EXISTS 
A (
  text VARCHAR(255)
) 
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS 
B (
  text VARCHAR(255)
) 
ENGINE = InnoDB;

INSERT INTO A (text) VALUES ("1"), ("2"), ("3"), ("4");
INSERT INTO B (text) VALUES ("3"), ("4"), ("5"), ("6");

Wenn ich eine Full Outer Join Abfrage versuche

select * from A FULL OUTER JOIN B on A.text = B.text;

meldet MySQL einen Syntax Fehler

ERROR 1064 (42000) at line 19: 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 'OUTER JOIN B on A.text = B.text' at line 1

Answer 1

Du bekommst das gleiche Ergebnis, indem du LEFT JOIN + RIGHT JOIN kombinierst und per UNION zusammenführst.

SELECT A.text AS a_text, B.text AS b_text
FROM A
LEFT JOIN B ON A.text = B.text

UNION

SELECT A.text AS a_text, B.text AS b_text
FROM A
RIGHT JOIN B ON A.text = B.text;

Comments

Wenn du Duplikate behalten willst, nimm UNION ALL (hier wären aber 3 und 4 doppelt, weil sie in beiden Teilen vorkommen).

SELECT A.text AS a_text, B.text AS b_text
FROM A
LEFT JOIN B ON A.text = B.text

UNION ALL

SELECT A.text AS a_text, B.text AS b_text
FROM A
RIGHT JOIN B ON A.text = B.text
WHERE A.text IS NULL;