Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Full Outer Join in sqlite on 4 tables

I need to join 4 tables based on a common primary key. If sqlite implemented full outer joins it might look something like this (with optimization not taken into account).

SELECT S.pair, C.ball, P.bluejeans, B.checkered 
FROM Socks S 
FULL OUTER JOIN Caps C 
FULL OUTER JOIN Pants P 
FULL OUTER JOIN Boxers B 
WHERE S.color = C.color AND S.color = P.color AND S.color = B.color;

I've looked long and hard and the best I found was this 2 table sqlite full join implemented with left joins and union alls:

SELECT employee.*, department.* 
FROM employee LEFT JOIN department 
ON employee.DepartmentID = department.DepartmentID 
UNION ALL SELECT employee.*, department.* 
FROM   department LEFT JOIN employee 
ON employee.DepartmentID = department.DepartmentID 
WHERE  employee.DepartmentID IS NULL;

I'm trying to modify this to work for more than 2 tables but I'm new to SQL and I'm not getting too far. Is it possible to get this result in a reasonable amount of time?

I think I have a correct implementation for 3 tables (it might not be correct) but I still can't seem to get it for 4. Here's what I have for 3:

SELECT S.pair, C.ball, P.bluejeans 
FROM Socks S LEFT JOIN Caps C LEFT JOIN Pants P 
ON C.color = S.color AND P.color = S.color 
UNION ALL 
SELECT S.pair, C.ball, P.bluejeans 
FROM Socks S LEFT JOIN Caps C LEFT JOIN Pants P 
ON S.color = C.color AND S.color = P.color 
WHERE S.color IS NULL;

Any help is much appreciated

like image 272
atfergus Avatar asked Oct 06 '12 10:10

atfergus


People also ask

Does SQLite support full outer join?

SQLite does not directly support the RIGHT JOIN and FULL OUTER JOIN.

Is outer joined to multiple tables?

Using OUTER-JOINS, it is possible to join only two tables at a time. In order to join three tables, it is necessary to first select two tables to join and then join the third table to the result of the first join.

How many rows in full outer join?

FULL OUTER JOIN will return 25 rows in result set.

Is full outer join faster than union?

Even a Normal UNION is faster than a join. The UNION's will make better use of indexes which could result in a faster query.


1 Answers

The general construction for a full outer join between two tables A and B in SQLite indeed is:

SELECT ... FROM A LEFT JOIN B ON ...
UNION ALL
SELECT ... FROM B LEFT JOIN A ON ... WHERE A.key IS NULL

Now create a view SocksCaps for the full outer join between Socks and Caps:

CREATE VIEW SocksCaps AS
SELECT ... FROM Socks LEFT JOIN Caps ON ...
UNION ALL
SELECT ... FROM Caps LEFT JOIN Socks ON ... WHERE Socks.color IS NULL

Do the same for Pants and Boxers.

Then treat these views just like tables and do a full outer join with the same construction:

SELECT ... FROM SocksCaps LEFT JOIN PantsBoxers ON ...
UNION ALL
SELECT ... FROM PantsBoxers LEFT JOIN SocksCaps ON ... WHERE SocksCaps.color IS NULL
like image 170
CL. Avatar answered Oct 06 '22 05:10

CL.