将右联接改为左联接

Question

在这样的查询中，如何将此右联接更改为左联接：

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

以上是一个简化的查询，这是实际的查询：

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;

进行此更改的原因是我使用的是SQLAlchemy (一个Python )，它不支持正确的联接。

样本数据

命令：

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

成就：

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

预期结果：

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

Answer 1

A RIGHT JOIN B与SQL中的B LEFT JOIN A等价，因此我们可以交换加入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;