mysql查询语句left join和right join问题（日常工作遇到问题总结-仅供个人学习）

一、需求：只需要找出6条数据

错误：

1、--  语句1、显示1101条数据  SELECT a.* FROM td_neo_crm_order_pay_detail AS a LEFT JOIN       (SELECT * FROM td_neo_crm_order_pay_detail WHERE pay_type = "transfer" AND state_deleted = 0) AS b      on b.pay_id = a.pay_id AND b.currency_code = a.currency_code  WHERE a.pay_type="other" AND a.state_deleted = 0 

原因：LEFT JOIN  左表满，右表不存在数据时，也会显示左边的数据；当右表不存在时，条件在前面也无法限制住b.currency_code = a.currency_code

错误2、--  语句2、显示1101条数据  SELECT a.* FROM td_neo_crm_order_pay_detail AS a LEFT JOIN       (SELECT * FROM td_neo_crm_order_pay_detail WHERE pay_type = "transfer" AND state_deleted = 0) AS b      on b.pay_id = a.pay_id  WHERE a.pay_type="other" AND a.state_deleted = 0 

原因：LEFT JOIN  左表满，右表不存在数据时，也会显示左边的数据

正确1、--  语句1、显示6条数据-正确-b.currency_code = a.currency_code放到后面  SELECT a.* FROM td_neo_crm_order_pay_detail AS a LEFT JOIN       (SELECT * FROM td_neo_crm_order_pay_detail WHERE pay_type = "transfer" AND state_deleted = 0) AS b      on b.pay_id = a.pay_id  WHERE a.pay_type="other" AND a.state_deleted = 0 AND b.currency_code = a.currency_code

原因：LEFT JOIN  左表满，右表不存在数据时，也会显示左边的数据；当右表不存在时，条件在后面b.currency_code = a.currency_code，由于a.currency_code部位null，此时b.currency_code为null，所以限制住了

正确2、--  语句2、显示6条数据-正确--去掉b.currency_code = a.currency_code，改为  RIGHT JOIN    SELECT a.* FROM td_neo_crm_order_pay_detail AS a RIGHT JOIN       (SELECT * FROM td_neo_crm_order_pay_detail WHERE pay_type = "transfer" AND state_deleted = 0) AS b      on b.pay_id = a.pay_id  WHERE a.pay_type="other" AND a.state_deleted = 0

原因：RIGHT JOIN 右表满，左边数据被限制在右表范围，满足需求

正确3、--  语句5、显示6条数据-正确--使用RIGHT JOIN  并且AND b.currency_code = a.currency_code放在后面  SELECT a.* FROM td_neo_crm_order_pay_detail AS a RIGHT JOIN       (SELECT * FROM td_neo_crm_order_pay_detail WHERE pay_type = "transfer" AND state_deleted = 0) AS b      on b.pay_id = a.pay_id  WHERE a.pay_type="other" AND a.state_deleted = 0 AND b.currency_code = a.currency_code

原因：RIGHT JOIN 右表满，左边数据被限制在右表范围，满足需求

正确4、 --  语句6、显示6条数据-正确--使用RIGHT JOIN，并且AND b.currency_code = a.currency_code放在前面  SELECT a.* FROM td_neo_crm_order_pay_detail AS a RIGHT JOIN       (SELECT * FROM td_neo_crm_order_pay_detail WHERE pay_type = "transfer" AND state_deleted = 0) AS b      on b.pay_id = a.pay_id  AND b.currency_code = a.currency_code WHERE a.pay_type="other" AND a.state_deleted = 0 

原因：RIGHT JOIN 右表满，左边数据被限制在右表范围，满足需求