Full OUTER JOIN with SQLite

The nature of FULL OUTER JOIN

Before SQLite version 3.39.0 came into the picture, FULL OUTER JOIN was unsupported. Therefore, the SQL community came up with the commonly accepted hack – blending LEFT JOIN and UNION ALL. Also, using a WHERE clause in the second SELECT helped filter out the matched records.

This method ensures the representation of all rows from both tables, compensating with NULL when there is no match.

The game changed with SQLite 3.39.0

Starting from SQLite 3.39.0 onwards, SQLite natively supports FULL OUTER JOIN:

SELECT A.*, B.*
FROM A
FULL OUTER JOIN B ON A.key = B.key; 


Voila! This query right here is an uncomplicated, straightforward solution allowing for efficient data merging from two separate tables, inclusive of all rows, whether they share matching keys or not. Remember, always verify your SQLite version compatibility before executing.

FULL OUTER JOIN hacks – insights and tricks

Pre SQLite 3.39.0

In the scenario where FULL OUTER JOIN support is unavailable, here are a couple of reliable workarounds:

Doubling up on LEFT JOIN

Not Robin Hood but a LEFT JOIN can save the day by hitting arrow twice in opposite directions through UNION ALL. Let me show you the magic trick:

-- Here we capture everything from left table and whatever matches from right
SELECT A.*, B.*
FROM A
LEFT JOIN B ON A.key = B.key

UNION ALL 

-- In this case, we're focusing on catching the unmatched from right table
-- I guess you could say we're LEFT-JOINING no record behind!
SELECT A.*, B.*
FROM B
LEFT JOIN A ON B.key = A.key
WHERE A.key IS NULL; 


A friendly heads up: make sure you handle NULL values with care here.

After SQLite 3.39.0

Utilizing FULL JOIN

Native support for FULL JOIN queries can be a game-changer. No more needs for simulations or creating temporal views or tables. Consider your life simplified:

-- Gives you a full display of data combinations from the two tables
SELECT A.key, A.value, B.value
FROM A
FULL OUTER JOIN B ON A.key = B.key; 


When simplicity meets efficiency, it’s a win-win!

Troubleshooting and how-to's

NULL value handling

When dealing with FULL OUTER JOIN, it’s crucial to be cautious with NULL values. These pesky little non-entities signify unmatched records and can degrade the results if ignored.

SQLite version compatibility

It's vital to ensure that your SQLite version is not only up-to-date but also compatible with the FULL OUTER JOIN feature. Upgrade to SQLite version 3.39.0 or later to enjoy native support.

Ordering your Union Output

Sort your UNION output using the ORDER BY clause to match expected result:

SELECT ...
UNION ALL
SELECT ...
-- Add ORDER BY clause
ORDER BY key;


Performance considerations

Simulating FULL OUTER JOIN using multiple joins and unions can add to processing overhead. Take full advantage of SQLite's FULL OUTER JOIN feature in newer versions—your queries would thank you!