在这样的查询中，如何将右连接更改为左连接:
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 ORM)不支持右连接。
示例数据
订单:
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|
+-------------+--------+-----+----+---+-----------------+--------------------------+-----+--------+-----+
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;