Database error - RIGHT and FULL OUTER JOINs are not currently supported

Question

I was trying to RIGHT JOIN two tables using this query:

SELECT Persons.firstname, company.lastname
FROM Persons
RIGHT JOIN company ON Persons.firstname=company.firstname;

which comes with this error:

RIGHT and FULL OUTER JOINs are not currently supported

How can we get rid of this?

Note: I am using Mozilla DB manager.

Marry

Does this answer your question? FULL OUTER JOIN with SQLite – Gangland

Answer 1

By doing a left join and switching the tables

SELECT Persons.firstname, company.lastname
FROM company
LEFT JOIN Persons ON Persons.firstname = company.firstname;

Mccartney

Answer 2

For FULL OUTER JOIN UNION the result of LEFT and RIGHT( again swapped LEFT JOIN) JOIN results...

 SELECT Persons.firstname,company.lastname FROM Persons LEFT JOIN company ON 
 Persons.firstname=company.firstname
 union 
 SELECT Persons.firstname, company.lastname FROM company LEFT JOIN Persons ON 
 Persons.firstname=company.firstname;

Stratagem

I think you need UNION ALL instead of UNION as explained in Sqlite FULL OUTER JOIN emulation. – Eminent

Answer 3

Actually, I think you can either:

1. Do exactly as Siddappa posted

OR

2. Use UNION ALL and add WHERE Persons.firstname IS NULL to the second select statement. If you only do the UNION ALL without the null check, you can get duplicate rows in the result.

I think #2 is probably more efficient.

Phyllys