FULL OUTER JOIN with SQLite

Yes, see the example on Wikipedia.

SELECT employee.*, department.*FROM   employee        LEFT JOIN department           ON employee.DepartmentID = department.DepartmentIDUNION ALLSELECT employee.*, department.*FROM   department       LEFT JOIN employee          ON employee.DepartmentID = department.DepartmentIDWHERE  employee.DepartmentID IS NULL

Following Jonathan Leffler's comment in Mark Byers' answer, here's an alternative answer which uses UNION instead of UNION ALL:

SELECT * FROM table_name_1 LEFT OUTER JOIN table_name_2 ON id_1 = id_2UNIONSELECT * FROM table_name_2 LEFT OUTER JOIN table_name_1 ON id_1 = id_2

Edit: The original source for the SQLite example above and from where further SQLite examples could be found was http://sqlite.awardspace.info/syntax/sqlitepg06.htm but it seems as though that site is now returning a 404 Not Found error.

For people, searching for an answer to emulate a Distinct Full Outer Join:Due to the fact, that SQLite does neither support a Full Outer Join, nor a Right Join, i had to emulate a distinct full outer join / an inverted inner join (however you might call it).The following Venn diagram shows the expected output:

To receive this expected output, i combined two Left Join clauses (the example refers to two identical built tables with partially differing data. I wanted to output only the data which does either appear in table A OR in table B).

SELECT A.flightNumber, A.offblockTime, A.airspaceCount, A.departure, A.arrival FROM D2flights A    LEFT JOIN D1flights B        ON A.flightNumber = B.flightNumber        WHERE B.flightNumber IS NULLUNION SELECT A.flightNumber, A.offblockTime, A.airspaceCount,  A.departure, A.arrival FROM D1flights A    LEFT JOIN D2flights B        ON A.flightNumber = B.flightNumber        WHERE B.flightNumber IS NULL

The SQLite statement above returns the expected result in one query. It appears, that the UNION clause does also order the output via the flightNumber column.

The code has been tested with SQLite version 3.32.2