1

I am new to SQL and have recently started implementing joins into my code, the data I wish to retrieve can be done with the following SQL statement. However, as you know SQLite3 does not support RIGHT OUTER and FULL OUTER JOINs.

Therefore I would like to re-write this statement using LEFT OUTER JOINs as only these are supported, any help would be appreciated.

Before you go ahead and mark this question as duplicate, I have looked at answers to other similar questions but none have explained the general rules when it comes to rearranging queries to use LEFT JOINs only.

I also think this particular example is slightly different in the sense that the table (periods) cannot be joined with either of the tables (teacher_subjects, classroom_subjects) without first joining the (class_placement) table.

FROM P
LEFT JOIN CP
 ON P.PID = CP.PID
RIGHT JOIN CS
 ON CP.CID = CS.CID
RIGHT JOIN TS
 ON CP.TID = TS.TID
WHERE (CP.CID IS NULL
      AND CP.TID IS NULL)
ORDER BY P.PID;

Unsurprisingly, the error I get from running this query is:

sqlite3.OperationalError: RIGHT and FULL OUTER JOINs are not currently supported

Sorry in advance if I am being really stupid but if you require any extra information please ask. Many Thanks.

Origami
  • 13
  • 3
  • 1
    *the data I wish to retrieve can be done with the following SQL statement* are you sure? It does not work in SQLite. Did you try it in any other database? Having a LEFT and 2 RIGHT joins at opposite sides seems very strange. – forpas Sep 08 '19 at 11:58
  • 1
    Please in code questions give a [mre]--cut & paste & runnable code; example input (as tabular initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS At minimal code that works you could think about what the next change to your query would be to get the next partial result you want. (See my answer.) But if you don't write & explore that code, how can you get unstuck? – philipxy Sep 08 '19 at 12:02
  • @forpas I know this statement does not work is SQLite, as I said I am completely new to SQL and StackOverflow if I totally honest. I wrote that statement knowing it does not work in SQLite but would achieve the desired result if run on a SQL platform that does support RIGHT JOIN so sorry for any confusion caused with the poorly worded question. Future reference I will explore the problem further on my own before coming here. Sorry for any inconvenience caused. – Origami Sep 08 '19 at 12:20
  • @Origami my question is how do you know that this code would return the expected results that you want **if** it worked in SQLite? Did you try it in a database that does support RIGHT joins? I ask this because I don't believe that such a query with LEFT and RIGHT joins would work. – forpas Sep 08 '19 at 12:24
  • @forpas Yeah you are right it is ambiguous as I never tested it, noted for future posts I will be more specific with actual testing and spend more time searching to make sure my question was not a duplicate. – Origami Sep 08 '19 at 12:34
  • 1
    Hi. I'm not sure why you deleted your table definitions, but per my earlier comment when you do give a [mre]--and composing one should be done before one considers posting a question--you should for SQL give DDL including table declarations & intializations. (If you dropped them in the name of minimality--recall Einstein, "as simple as possible, but no simpler".) Although the solution & how I chose to address it happen to not particularly need one. But notice that without example data that I could cut & paste I wasn't going to test run (& de-typo) a rearrangement of your actual code. – philipxy Sep 08 '19 at 13:03
  • Does this answer your question? [SQLite3 Simulate RIGHT OUTER JOIN with LEFT JOINs and UNION](https://stackoverflow.com/questions/9147025/sqlite3-simulate-right-outer-join-with-left-joins-and-union) – Mahozad Feb 23 '23 at 10:20

1 Answers1

1

Ignoring column order, x right join y on c is y left join x on c. This is commonly explicitly said. (But you can also just apply the definitions of the joins to your original expression to get subexpressions with the values you want.) You can read the from grammar to see how you can parenthesize or subquery for precedence. Applying the identity we get ts left join (cs left join (p left join cp on x) on y) on z.

Similarly, ignoring column order, x full join y on c is y full join x on c. Expressing full join in terms of left join & right join is a frequently asked duplicate.

philipxy
  • 14,867
  • 6
  • 39
  • 83