[java踩坑录] mysql的左连接的条件位置引出的问题

我就是我 2023-06-05 05:52 37阅读 0赞

## 标题 ##

**这个问题苦害了我几个月，直到今天突然顿悟，发现问题和解决方案**

以前听说过后来没注意，其实都怪自己菜balabala

--------------------

## 工作中我们有这样一个需求，左连接的时候会加上一些条件判断 ##

先上表结构  
![在这里插入图片描述][20191008151714751.png]![在这里插入图片描述][20191008151723885.png]![在这里插入图片描述][20191008151738668.png]

**以我将要作为的案例做简单的描述**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NDI1MjQz_size_16_color_FFFFFF_t_70]首先用户1拥有3个手机和2个食物，如果我们要查询所有的手机和食物，则:

SELECT
    	u.`name` as '用户名',p.`name` as '手机',f.`name` as '食物'
    FROM
    	`user` u
    	LEFT JOIN phone p ON p.user_id = u.id
    	LEFT JOIN food f ON f.user_id = u.id 
    WHERE
    	u.`name` = '用户1'

![在这里插入图片描述][20191008151037345.png]  
将查询结果映射成对象以后

用户1
              手机:                                食物:
         手机1,手机2,手机3                        水果,蔬菜

--------------------

现在有这样一个需求，假设需要查询条件为不要手机2  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NDI1MjQz_size_16_color_FFFFFF_t_70 1]假设手机2欠费，则SQL为:

SELECT
    	u.`name` as '用户名',p.`name` as '手机',f.`name` as '食物'
    FROM
    	`user` u
    	LEFT JOIN phone p ON p.user_id = u.id
    	LEFT JOIN food f ON f.user_id = u.id 
    WHERE
    	u.`name` = '用户1' 
    	AND p.`name` != '手机2'

![在这里插入图片描述][20191008151416755.png]  
将查询结果映射成对象以后

用户1
              手机:                                食物:
            手机1,手机3                           水果,蔬菜

--------------------

貌似还没出现什么问题，现在又需要一个条件，那就是食物不能查询已经过期的，当食物过期的情况下理想结果为  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NDI1MjQz_size_16_color_FFFFFF_t_70 2]

![在这里插入图片描述][20191008151637552.png]  
**查询结果竟然是空的！**

而我们的理想结果应该是  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NDI1MjQz_size_16_color_FFFFFF_t_70 3]由此引发的mysql数据库左连接的问题，在我实际的项目中经常这样干，导致出了问题很无奈，一大堆左连接，当一个表的条件不满足的时候导致其他表也全部没有了

![在这里插入图片描述][20191008152051651.png]![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NDI1MjQz_size_16_color_FFFFFF_t_70 4]本来我们是要得到这样的结果，别着急

--------------------

我们需要明确一点，查询条件和连表条件  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NDI1MjQz_size_16_color_FFFFFF_t_70 5]  
这个是我真实项目的写法，问题的缘由就是因为s\_upload\_file表不一定有数据，其实里面就是存了一些图片.按照这样的写法，我只能保证我的订单关联的产品表一定要有图片，否则无法展示

由于这里没有截图查询条件，而图中和s\_upload\_file有关的几个条件拆分来看都应该叫连表条件

## 什么是连表条件和查询条件: ##

1.  查询条件: 对整个搜索结果有质的变化，当条件成立的时候，对结果的影响是必然的
2.  连表条件: 不影响整个搜索结果

--------------------

通俗的讲，在上面的例子中，查询条件就是

WHERE u.`name` = '用户1'

只能是用户1，如果用户表不是用户1就不展示，查询条件影响了整个搜索结果

而连表条件应该是

AND p.`name` != '手机2'
    AND f.state = '未过期'

**食物过期和不过期，不应该影响整个搜索结果，你食物过期了不应该影响手机的查询  
正如同我的订单，订单里的商品没有图片，商品也应该展示，而不是没办法查询没有图片的商品**

--------------------

通过拆分查询条件和连表条件来完成正确的左连接

SELECT
    	u.`name` AS '用户名',
    	p.`name` AS '手机',
    	f.`name` AS '食物' 
    FROM
    	`user` u
    	LEFT JOIN phone p ON p.user_id = u.id AND p.`name` != '手机2'
    	LEFT JOIN food f ON f.user_id = u.id AND f.state = '未过期' 
    WHERE
    	u.`name` = '用户1'

连表的时候就应该指定食物只能为未过期的

在这种拆分条件的情况下，我们得到了想要的正确结果![在这里插入图片描述][20191008153153101.png]

--------------------

**最后一个例子**

错误SQL:

SELECT * FROM b_product bp
    LEFT JOIN s_upload_file suf ON suf.resourceId = bp.id
    WHERE 1=1
    and suf.resourceType = 3
    and suf.sort = 99

正确SQL:

SELECT * FROM b_product bp
    LEFT JOIN s_upload_file suf ON suf.resourceId = bp.id and suf.resourceType = 3 and suf.sort = 99
    WHERE 1=1

--------------------

## 总结:将连表需要的条件放到左连接后面，而不是放在where后面！ ##

[20191008151714751.png]: https://img-blog.csdnimg.cn/20191008151714751.png
[20191008151723885.png]: https://img-blog.csdnimg.cn/20191008151723885.png
[20191008151738668.png]: /images/20230601/368bc974982841c49f0ce7caf8e0ca17.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NDI1MjQz_size_16_color_FFFFFF_t_70]: /images/20230601/fa9d98026cbc402bba104ea9383783e5.png
[20191008151037345.png]: /images/20230601/6a45a72b9b9f4e0bb66579edccee7426.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NDI1MjQz_size_16_color_FFFFFF_t_70 1]: /images/20230601/27bc134eee2f469cb9a6b28bf0089d68.png
[20191008151416755.png]: /images/20230601/22c5ef2ac0a14c899b3a2372bc56234d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NDI1MjQz_size_16_color_FFFFFF_t_70 2]: /images/20230601/dd70735e6cf24fb6b4bc631529489990.png
[20191008151637552.png]: /images/20230601/40094230268a43e5bdc8d0ceaf2b839c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NDI1MjQz_size_16_color_FFFFFF_t_70 3]: /images/20230601/8a4f6f7487b44339a4f6231a82b1df47.png
[20191008152051651.png]: https://img-blog.csdnimg.cn/20191008152051651.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NDI1MjQz_size_16_color_FFFFFF_t_70 4]: /images/20230601/69bae689d6e64d1f9c50c6dc09206e82.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NDI1MjQz_size_16_color_FFFFFF_t_70 5]: /images/20230601/c5d97a78fa354f1693d8b166afc1d429.png
[20191008153153101.png]: /images/20230601/2e47c4456c4343298222da3ef7aab181.png