Change RIGHT JOIN into LEFT JOIN

Question

In a query like this, how do you change this right join into a left join:

SELECT Z.col
FROM X INNER JOIN Y ON X.col == Y.col AND Y.status IN ('value1', 'value2')
RIGHT JOIN Z ON Z.col == X.col

The above is a simplified query, this is the actual query in question:

SELECT orders.id                                                  AS id,
       orders.name                                                AS name,
       li.item                                                    AS item,
       li.size                                                    AS size,
       coalesce(fli.quantity, li.quantity)                        AS qty,
       coalesce(l.name, 'online store')                           AS store,
       timezone('UTC-2', orders.created_at)                       AS date,
       coalesce(fli.price * fli.quantity, li.price * li.quantity) AS price,
       li.total_discount                                          AS discount,
       'order'                                                    AS type
FROM fulfillment_line_items fli
         INNER JOIN fulfillments f on fli.fulfillment_id = f.id and f.status IN ('fulfilled', 'success')
         RIGHT JOIN line_item li on fli.id = li.id
         INNER JOIN orders ON li.order_id = orders.id and orders.name = '#282814'
         LEFT JOIN locations l on f.location_id = l.id;

The reason for this change is that I'm using SQLAlchemy (a Python ORM) which doesn't support right joins.

SAMPLE DATA

Orders:

SELECT *
FROM ORDERS WHERE ID = 3875388358828;

+-------------+---------------------------------+---------------------------------+---------------+----------------------+-----------------+-----------+------------+----------------+------------------+--------+---------------------------------+-----------+-----------+-----------+-----------+-----------+-------+--------------+----+
|id           |created_at                       |updated_at                       |total_discounts|total_line_items_price|total_outstanding|total_price|cancelled_at|financial_status|fulfillment_status|name    |processed_at                     |source_name|note       |customer_id|location_id|user_id    |gateway|total_shipping|tags|
+-------------+---------------------------------+---------------------------------+---------------+----------------------+-----------------+-----------+------------+----------------+------------------+--------+---------------------------------+-----------+-----------+-----------+-----------+-----------+-------+--------------+----+
|3875388358828|2021-07-05 08:20:57.000000 +00:00|2021-07-09 06:34:19.000000 +00:00|0              |265                   |0                |265        |NULL        |paid            |fulfilled         |#1083501|2021-07-05 08:20:56.000000 +00:00|pos        |314496369  |-1         |63074828460|10475667509|manual |0             |NULL|
+-------------+---------------------------------+---------------------------------+---------------+----------------------+-----------------+-----------+------------+----------------+------------------+--------+---------------------------------+-----------+-----------+-----------+-----------+-----------+-------+--------------+----+

LINE_ITEM:

SELECT *
FROM line_item WHERE ORDER_ID = 3875388358828;

+--------------+--------+------------------+--------------+-----+-------------+-----+-----+----+-------------+--------------------+
|id            |quantity|fulfillment_status|total_discount|price|pre_tax_price|tax  |item |size|order_id     |fulfillable_quantity|
+--------------+--------+------------------+--------------+-----+-------------+-----+-----+----+-------------+--------------------+
|10034198872236|1       |fulfilled         |0             |165  |144.74       |20.26|48901|XXL |3875388358828|0                   |
|10034198905004|1       |NULL              |0             |100  |87.72        |12.28|57575|XXL |3875388358828|0                   |
+--------------+--------+------------------+--------------+-----+-------------+-----+-----+----+-------------+--------------------+

FULFILLMENTS:

SELECT *
FROM fulfillments WHERE ORDER_ID = 3875388358828;

+-------------+---------------------------------+---------------------------------+-----------+-------------+---------+
|id           |created_at                       |updated_at                       |location_id|order_id     |status   |
+-------------+---------------------------------+---------------------------------+-----------+-------------+---------+
|3423769559212|2021-07-05 08:20:58.000000 +00:00|2021-07-09 06:33:28.000000 +00:00|63074828460|3875388358828|cancelled|
|3430761332908|2021-07-09 06:34:11.000000 +00:00|2021-07-09 06:34:11.000000 +00:00|63074828460|3875388358828|success  |
+-------------+---------------------------------+---------------------------------+-----------+-------------+---------+

FULFILLMENT_LINE_ITEMS:

SELECT *
FROM fulfillment_line_items WHERE fulfillment_id IN (3423769559212,3430761332908);
+--------------+--------+------------------+--------------+-----+-------------+-----+-----+----+--------------+--------------------+
|id            |quantity|fulfillment_status|total_discount|price|pre_tax_price|tax  |item |size|fulfillment_id|fulfillable_quantity|
+--------------+--------+------------------+--------------+-----+-------------+-----+-----+----+--------------+--------------------+
|10034198872236|1       |fulfilled         |0             |165  |144.74       |20.26|48901|XXL |3423769559212 |0                   |
|10034198905004|1       |NULL              |0             |100  |87.72        |12.28|57575|XXL |3423769559212 |0                   |
|10034198872236|1       |fulfilled         |0             |165  |144.74       |20.26|48901|XXL |3430761332908 |0                   |
+--------------+--------+------------------+--------------+-----+-------------+-----+-----+----+--------------+--------------------+

EXPECTED RESULT:

+-------------+--------+-----+----+---+-----------------+--------------------------+-----+--------+-----+
|id           |name    |item |size|qty|store            |date                      |price|discount|type |
+-------------+--------+-----+----+---+-----------------+--------------------------+-----+--------+-----+
|3875388358828|#1083501|48901|XXL |1  |Jumia consignment|2021-07-05 10:20:57.000000|165  |0       |order|
|3875388358828|#1083501|57575|XXL |1  |online store     |2021-07-05 10:20:57.000000|100  |0       |order|
+-------------+--------+-----+----+---+-----------------+--------------------------+-----+--------+-----+

Yes! It was mind boggling to me, kept wondering how I can "join two tables at once" because in effect that's what was happening in the right join. Now to figure out how to transform this into an SQLAlchemy query... — Mohammad Ayoub, Apr 24 '22 at 07:53
You can click on some button/icon now to say this is a duplicate. I don't get what you mean by '"join two tables at once"'. And there's nothing 'happening' in right join that isn't mirror-image happening in left join. Re 'Now ...' What is the problem, x right join y on c is y left join x on c (ignoring column order). Also: Googling 'site:stackoverflow.com right join sqlalchemy': https://stackoverflow.com/q/11400307/3404097 etc. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) When you don't have a duplicate question: [mre]. — philipxy, Apr 24 '22 at 08:08
LEFT JOIN ON returns INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Similarly for RIGHT JOIN ON & right table rows. Always know what INNER JOIN ON you want as part of an OUTER JOIN ON. After a LEFT/RIGHT JOIN ON a WHERE, INNER JOIN or HAVING that requires a right/left [sic] table column to be not NULL removes rows with introduced NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. Another faq. But one has to write many clear, concise & precise phrasings of one's question/problem/goal to be able to (re)search. — philipxy, Apr 24 '22 at 08:10
Join two tables at once meant nullifying the result-set from the original joins prior to the right join, but instead using left join. None of these threads helped except for the specific one linked because none of them had an inner join in the middle that filtered out the results. — Mohammad Ayoub, Apr 24 '22 at 08:11
[Problems with INNER JOIN and LEFT/RIGHT OUTER JOIN](https://stackoverflow.com/q/16442900/3404097) And to pin down one's problem one must make a [mre]. For debug 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. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Apr 24 '22 at 08:13
Still don't understand 'nullifying the result-set from the original joins prior to the right join, but instead using left join', you are not writing clearly. Always: Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. (But comments are for asking for clarifications, please clarify via edits not comments, & I'll stop trying to get clarification on writing of yours that isn't part of the question.) — philipxy, Apr 24 '22 at 08:18
`X left join Y on ... Y right join Z on Z.col = X.col` nullifies all the results from X and Y if the final condition is not met. That's what I mean by nullifying the prior result-set. — Mohammad Ayoub, Apr 24 '22 at 08:18

score 1 · Answer 1 · answered Apr 24 '22 at 07:30

A RIGHT JOIN B is equivalent to B LEFT JOIN A in SQL, so we can swap the position of joining fulfillment_line_items,line_item.

SELECT orders.id                                                  AS id,
        orders.name                                                AS name,
        li.item                                                    AS item,
        li.size                                                    AS size,
        coalesce(fli.quantity, li.quantity)                        AS qty,
        coalesce(l.name, 'online store')                           AS store,
        timezone('UTC-2', orders.created_at)                       AS date,
        coalesce(fli.price * fli.quantity, li.price * li.quantity) AS price,
        li.total_discount                                          AS discount,
        'order'                                                    AS type
FROM line_item li
 LEFT JOIN fulfillment_line_items fli on fli.id = li.id
 INNER JOIN fulfillments f on fli.fulfillment_id = f.id and f.status IN ('fulfilled', 'success')
 INNER JOIN orders ON li.order_id = orders.id and orders.name = '#282814'
 LEFT JOIN locations l on f.location_id = l.id;

Wouldn't the inner join on `fulfillments f` remove all the items with no fulfillment IDs? Edit: Just confirmed that it does - this answer does not provide the required result unfortunately. — Mohammad Ayoub, Apr 24 '22 at 07:34
The key here is that there's an inner join followed by the right join, with filtering conditions in the first inner join, which limits the values passed to the right join, nullifying results from multiple tables in the process, making the coalesce function work. I can't for the life of me figure how to do this with a left join. — Mohammad Ayoub, Apr 24 '22 at 07:36
@MohammadAyoub expect I don't know your query logic and there isn't a order data as `orders.name = '#282814',` it might work as https://dbfiddle.uk/?rdbms=postgres_13&fiddle=b3ec1cadfb31c224e0c5d29e8031758a — D-Shih, Apr 24 '22 at 08:19

Change RIGHT JOIN into LEFT JOIN

1 Answers1