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

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

Code:

---

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.

Code:

---

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

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

Code:

---

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

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

Code:

---

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>

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