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