这个查询有什么问题？我得到空结果

Question

SELECT `acart`.`order_number` AS `admin_order_number`, 
     `acart`.`user_id` AS `admin_user_id`, 
     `acart`.`created_by` AS `admin_created_by`, 
     `rcart`.`order_number` AS `renew_order_number`, 
     `rcart`.`user_id` AS `renew_user_id`, 
     `rcart`.`created_by` AS `renew_created_by`, 
     `scart`.`order_number` AS `shopping_order_number`, 
     `scart`.`user_id` AS `shopping_user_id`, 
     `scart`.`created_by` AS `shopping_created_by` 
FROM `cdp_order_transaction_master` AS `master` 
LEFT JOIN `cdp_admin_shopping_cart` AS `acart` 
    ON `acart`.`order_number`=`master`.`order_number` 
LEFT JOIN `cdp_renew_cart` AS `rcart` 
    ON `rcart`.`order_number`=`master`.`order_number` 
LEFT JOIN `cdp_shopping_cart` AS `scart` 
    ON `scart`.`order_number`=`master`.`order_number` 
WHERE master.order_number IS NULL 

让我解释一下我的问题，如果订单是全成那么它会去根据不同的情况，但如果随后的秩序无法cdp_order_transaction_master表和其他3台（cdp_admin_shopping_cart，cdp_renew_cart，cdp_shopping_cart）它不会去cdp_order_transaction_master表，并留在其他表， 所以我要失败的顺序是不存在的cdp_order_transaction_master，可以出现在任何其他表（cdp_admin_shopping_cart，cdp_renew_cart，cdp_shopping_c艺术）

Answer 1

WHERE master.order_number IS NULL

这是你的主表，一个d所有其他表的连接条件都在此列上。

您试图加入空为空

基于您的评论，请尝试：

select 'cdp_admin_shopping_cart' as `err_table`, a1.order_number 
from cdp_admin_shopping_cart a1 
where not exists (select 1 from cdp_order_transaction_master a2 where a2.order_number = a1.order_number) 

union all 

select 'cdp_renew_cart' as `err_table`, a1.order_number 
from cdp_renew_cart a1 
where not exists (select 1 from cdp_order_transaction_master a2 where a2.order_number = a1.order_number) 

union all 

select 'cdp_shopping_c‌​art' as `err_table`, a1.order_number 
from cdp_shopping_c‌​art a1 
where not exists (select 1 from cdp_order_transaction_master a2 where a2.order_number = a1.order_number)