[SQL] Full Outer Join for DBMS that don't support it

[Zvoni]

Hi Folks,
recently i stumbled upon the fact that there are (popular) Database-Systems, which don't support a Full Outer Join out of the box.
Prominently there are SQLite and MySQL.

I worked out a Workaround.
Sample-Code is for SQLite

CREATE TABLE "tbl_left" (
    "ID"    INTEGER NOT NULL UNIQUE,
    "ValueLeft"    TEXT,
    PRIMARY KEY("ID" AUTOINCREMENT)
);
CREATE TABLE "tbl_right" (
    "ID"    INTEGER NOT NULL UNIQUE,
    "ValueRight"    TEXT,
    PRIMARY KEY("ID" AUTOINCREMENT)
);
/*Sample Data for both tables*/
INSERT INTO tbl_right VALUES (2,'test 2'),(3,'test 3'),(4,'test 4'),(5,'test 5'),(7,'test 7'),(8,'test 8'),(10,'test 10');
INSERT INTO tbl_left VALUES (1,'test 1'),(2,'test 2'),(4,'test 4'),(5,'test 5'),(6,'test 6'),(8,'test 8'),(9,'test 9'),(10,'test 10')

And here's the workaround:
The trick is: You have to make a DISTINCT UNION on both tables which you use as the master appending two LEFT JOIN's to it.

SELECT l2.ID As LeftID, l2.ValueLeft, r2.ID AS RightID, r2.ValueRight 
FROM 
(SELECT DISTINCT l.ID As ID
FROM tbl_left As l 
UNION 
SELECT r.ID AS ID 
FROM tbl_right As r) As C
 LEFT JOIN tbl_left as l2 ON C.ID=l2.ID 
LEFT JOIN tbl_right As r2 ON r2.ID=C.ID 
ORDER BY c.ID

Returns:

LeftID	ValueLeft	RightID	ValueRight
1	test 1
2	test 2	2	test 2
		3	test 3
4	test 4	4	test 4
5	test 5	5	test 5
6	test 6
		7	test 7
8	test 8	8	test 8
9	test 9
10	test 10	10	test 10