9

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.

Mahozad
  • 18,032
  • 13
  • 118
  • 133
Amit
  • 556
  • 1
  • 7
  • 24
  • Does this answer your question? [FULL OUTER JOIN with SQLite](https://stackoverflow.com/questions/1923259/full-outer-join-with-sqlite) – Mahozad Feb 23 '23 at 10:22

3 Answers3

23

By doing a left join and switching the tables

SELECT Persons.firstname, company.lastname
FROM company
LEFT JOIN Persons ON Persons.firstname = company.firstname;
juergen d
  • 201,996
  • 37
  • 293
  • 362
7

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;
Siddappa Walake
  • 303
  • 5
  • 14
  • 1
    I think you need UNION ALL instead of UNION as explained in [Sqlite FULL OUTER JOIN emulation.](https://www.sqlitetutorial.net/sqlite-full-outer-join/) – builder-7000 Sep 02 '19 at 23:22
0

Actually, I think you can either:

  1. Do exactly as Siddappa posted

OR

  1. 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.

user2367072
  • 103
  • 1
  • 5