当多个表相关联时，就会出现此问题。
[En]
This problem occurs when multiple tables are associated.
例如其中单位串以单位表表示的货物表
[En]
Such as a table of goods in which the string of units is represented in the table of units
但这个单位的id之后进行了删除，并且不再奏效。
如下：
select *
from (select pg0.id,pg0.init_size,pg0.goods_id,
pg0.goods_move_size,pg0.execution,pg0.operation,
pg0.create_time,pg0.update_time
from psog_purchase_log pg0 where pg0.is_del=0
and pg0.store_log_id = 15)pg
left join psog_goods g on pg.goods_id = g.id
left join psog_cate c on g.cate_id = c.id
left join psog_unit u on g.unit_id = u.id
where g.is_del = 0 and c.is_del = 0 and u.is_del = 0
可以看到最终where语句判定u.is_del=0，判定单位表不为空
但其实有时候要查询一些记录数据，可能跟这个数值没有太大关系，以后商品单位空了也没关系。
[En]
But in fact, sometimes we have to query some record data, which may not have much to do with this value, and it doesn’t matter if the commodity unit is empty later.
然后，它可能会更改为如下所示：
[En]
Then it might be changed to something like this:
select *
from (select pg0.id,pg0.init_size,pg0.goods_id,
pg0.goods_move_size,pg0.execution,pg0.operation,
pg0.create_time,pg0.update_time,pg0.is_del
from psog_purchase_log pg0 where pg0.is_del=0
and pg0.store_log_id = #{storeLogId})pg
left join
(select g0.id,g0.cate_id,g0.unit_id,g0.goods_name,g0.goods_code,g0.is_del from psog_goods g0 where g0.is_del=0)g
on pg.goods_id = g.id
left join
(select c0.id,c0.is_del,c0.cate_name from psog_cate c0 where c0.is_del=0)c
on g.cate_id = c.id
left join (select u0.unit,u0.id,u0.is_del from psog_unit u0 where u0.is_del=0)u
on g.unit_id = u.id
可以看到上面每个判定数据有效的is_del在单独的关联时就已经做了排查，
这样的作用是基于最left的表为基准，之后的即使未关联到的某id=某id，
那么要查询的select * 里面的数据只变为空，而该行数据以最左边的表，即pg表为基准不管其它关联与否，要查询的
空字段和非空字段之间的关系不是太大。
[En]
The relationship between empty and non-empty fields is not too great.
总结：
上面的sql查询时必须要能关联到类似商品分类商品单位名称的等等数据时才可以，否则那一行不予返回
而下面的这个sql只要pg表能查到那么之后的不管子表数据删除了没有，主表的数据还会展示
Original: https://www.cnblogs.com/ukzq/p/16263415.html
Author: DATA_MONK
Title: mysql多表关联时可能出错的地方，如搜索出的记录数据变少了。
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/505387/
转载文章受原作者版权保护。转载请注明原作者出处！