[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

[techgnome]

You can also do a left-right and union the results:

SELECT * FROM t1 
LEFT JOIN t2 ON t1.id = t2.id 
UNION 
SELECT * FROM t1 
RIGHT JOIN t2 ON t1.id = t2.id

Also... I'm a bit surprised it hasn't been implemented in mySQL...

-tg

[wqweto]

Btw, UNION is always returning unique rows (so no DISTINCT is required) and is a relatively expensive operation compared to UNION ALL which is cheap because it does not deduplicate.

OUTER JOIN can be emulated with INNER JOIN and a UNION ALL like this (in pseudo-SQL)

a LEFT JOIN b <=> a JOIN b UNION ALL a WHERE a.ID NOT IN (b.ID)

This idea can be used with FULL OUTER JOIN too like this

SELECT l.ID, l.ValueLeft, r.ID, r.ValueRight
FROM tbl_left l
LEFT JOIN tbl_right r
ON l.ID = r.ID

UNION ALL

SELECT NULL, NULL, r.ID, r.ValueRight
FROM tbl_right r
WHERE r.ID NOT IN (SELECT ID FROM tbl_left)

(not tested)

cheers,
</wqw>

[Zvoni]

OK, many ways to skin that cat.

Agreed on the DISTINCT vs. UNION
In my Queries the DISTINCT sneaks in pretty much every time i start writing a Query, so i blame it on (bad) habit.

EDIT: After thinking about it, what i do like with my approach: I'm not bound to two tables.
I can join as many tables as i want, as long as the Field you want to Join the tables to of each table is in the primary SELECT UNION