SQLite and Right Joins or Outer Joins Alternatives

Wappler General Need Help
1

During my quest to get more acquainted with NodeJS and SQLite, I ran into this error message:

{“status”:“500”,“code”:“SQLITE_ERROR”,“message”:“select profile.* from profile right join login on login.login_id = profile.login_id order by profile.last_name ASC, profile.first_name ASC - SQLITE_ERROR: RIGHT and FULL OUTER JOINs are not currently supported”,“stack”:“Error: select profile.* from profile right join login on login.login_id = profile.login_id order by profile.last_name ASC, profile.first_name ASC - SQLITE_ERROR: RIGHT and FULL OUTER JOINs are not currently supported”}

Are there any other ways to circumvent this when using foreign keys and joins? Or should I just avoid using this MySQL method?

Thank you for your input or suggestions…

3

After researching the net, I have fount this bit of info:

Emulating SQLite full outer join

The following statement emulates the FULL OUTER JOIN clause in SQLite:

SELECT d.type,
         d.color,
         c.type,
         c.color
FROM dogs d
LEFT JOIN cats c USING(color)
UNION ALL
SELECT d.type,
         d.color,
         c.type,
         c.color
FROM cats c
LEFT JOIN dogs d USING(color)
WHERE d.color IS NULL;

Code language: SQL (Structured Query Language) (sql)

How the query works.

  • Because SQLilte does not support the RIGHT JOIN clause, we use the LEFT JOIN clause in the second SELECT statement instead and switch the positions of the cats and dogs tables.
  • The UNION ALL clause retains the duplicate rows from the result sets of both queries.
  • The WHERE clause in the second SELECT statement removes rows that already included in the result set of the first SELECT statement.

In this tutorial, you have learned how to use the UNION ALL and LEFT JOIN clauses to emulate the SQLite FULL OUTER JOIN clause.