FULL OUTER JOIN with SQLite

sql sqlite join full-outer-join
Yada picture Yada · Dec 17, 2009 · Viewed 44.2k times · Source

SQLite only has INNER and LEFT JOIN.

Is there a way to do a FULL OUTER JOIN with SQLite?

Answer

Mark Byers picture Mark Byers · Dec 17, 2009

Yes, see the example on Wikipedia.

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

Related questions

SQLite - How do you join tables from different databases?

I have an application that uses a SQLite database and everything works the way it should. I'm now in the process of adding new functionalities that require a second SQLite database, but I'm having a hard time figuring out how …

Read More
Multiple INNER JOIN from the same table

I have a table of metals MetalID integer MetalName text MetalCode text Item table ItemID integer ItemName text ... Metal1 int Ref.-> metals.metalID Metal2 int Ref.-> metals.metalID Metal3 int Ref.-> metals.metalID I …

Read More
Self Referencing Table SQL query

I've table with four columns id, name, designation, manager_id. Table schema: CREATE TABLE "Employee_Information" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar, "designation" varchar, "manager_id" integer references employee_information(id) ); It is as follows ID Name …

Read More