SQL ISNULL，但如果不存在，也需要结果

Question

我正在尝试编写一个查询以从数据库中导出订单。到目前为止，如果"extrafields"中的某个字段为空或有内容，我可以让它提取记录，但如果"extra fields"数据库中不存在数据，我不知道如何将其包括在内。如果有人能提供任何帮助，我将不胜感激，以下是我目前所掌握的信息。

    SELECT 
    CASE WHEN ISNULL(e.content) THEN ol.code ELSE CONCAT(ol.code, ' ** ', e.content) END as `ItemNumber`,
CASE WHEN ISNULL(e.content) THEN ol.name ELSE CONCAT(ol.name, ' ** ', e.content) END as `ItemTitle`,
o.orderID AS orderId,
ol.productID AS orderItemId,
ol.qty AS Quantity,
ol.price + ol.taxamount AS CostPerUnit,  
'' AS ProductOption,
0 AS ItemTaxRate,
0 AS DiscountPercent,
ol.lineID
FROM
`jss_orders_headers` o,
`jss_orders_lines` ol
LEFT JOIN jss_orders_extrafields e ON e.lineID = ol.lineID
WHERE
e.content!=" " 
and ol.orderID = o.orderID 
AND e.extraFieldID NOT IN (130, 114, 113, 111, 84, 81)
and e.exValID > 0

Answer 1

当您在WHERE子句中引用左联接列（在您的案例中是jss_orders_extrafields中的列）时，您会强制该联接表现为INNER联接。相反，将这些测试作为联接条件的一部分。

...
LEFT JOIN jss_orders_extrafields e 
    ON e.lineID = ol.lineID
        AND e.content != ' '
        AND e.extreaFieldID NOT IN (130, 114, 113, 111, 84, 81)
        AND e.exValID > 0
...

此外，在连接样式上要保持一致，不要混合隐式和显式连接。

...
FROM `jss_orders_headers` o
    INNER JOIN `jss_orders_lines` ol
        ON o.orderID = ol.orderID
...

并从WHERE子句中删除o.orderID = ol.orderID测试。

所以，把它们放在一起：

SELECT CASE WHEN ISNULL(e.content) THEN ol.code ELSE CONCAT(ol.code, ' ** ', e.content) END as `ItemNumber`,
       CASE WHEN ISNULL(e.content) THEN ol.name ELSE CONCAT(ol.name, ' ** ', e.content) END as `ItemTitle`,
       o.orderID AS orderId,
       ol.productID AS orderItemId,
       ol.qty AS Quantity,
       ol.price + ol.taxamount AS CostPerUnit,  
       '' AS ProductOption,
       0 AS ItemTaxRate,
       0 AS DiscountPercent,
       ol.lineID
    FROM `jss_orders_headers` o
        INNER JOIN `jss_orders_lines` ol
            ON o.orderID = ol.orderID   
        LEFT JOIN jss_orders_extrafields e 
            ON e.lineID = ol.lineID
                AND e.content!=" " 
                AND e.extraFieldID NOT IN (130, 114, 113, 111, 84, 81)
                AND e.exValID > 0;