MySql中遇到左查询无效问题

今天写sql发现使用left join 没有把左边表的数据全部查询出来,让我郁闷了一会,后来仔细研究了一会才知道自己犯了个常识性的错误(我是菜鸟)

这是原sql 

  

 

这样的查询并不能将tb_line这张表的数据都查询出来,好尴尬...

后面我才知道原来当我们进行多表查询,在执行到where之前,会先形成一个临时表

而on就是临时表中的条件筛选,使用left join则不管条件是否为真,都会查询出左边表的数据,条件为假的,则显示为null

where则是在临时表生成之后的过滤条件,

在第一张图中,我将tb_vehicle这张表的过滤条件放在where之中,那left join所产生条件为假的数据,则会在where 的 v.del_flag='0'中被过滤掉(因为条件为假的数据,del_flag都为空)

所以我看似使用了left join ,实际上这样写与使用inner join的结果是一样的

正确sql如下:

    

在临时表中就做好条件筛选,这样就能够得到左边表的数据

 

总结:

    使用left join  并需要做条件查询的时候,需要仔细斟酌改条件筛选放在on后面还是where后面

 

方法一 将过滤条件放在on后面，是在联合查询之前，先对右表进行查询过滤，将查询过滤的结果作为临时表，再和左表‘invite_interview’进行联合查询；

常见相关误区

1、误区一、左连接查询的返回数据记录条数并非是左表的数据总条数

     左连接查询，以左表为主，右表中无对应左表的记录时，右表以null填充；

     当右表中有多条数据对应左表时，左表的记录和对应右表的多条记录便会联合出现多条记录，这种情况下联合查询的总条数便会多于左表的数据记录；

     当只想返回左表的数据条数时，可通过group by 左表主键即可。

误区二、where 后面添加过滤条件 r.`project_id` != 1,返回的结果集中，除了会过滤联合查询结果中 r.`project_id` = 1的数据，同样会将 r.`project_id` is null的数据过滤掉哦

误区四、on 可以添加过滤条件

    联合查询时，on 后面可以设置两个表的关联字段，还可以添加过滤条件，右表先通过on后面的过滤条件返回结果集为临时表，再将临时表和左表联合查询返回结果集。
————————————————
版权声明：本文为CSDN博主「zpjing~.~」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u010865136/article/details/89927456

 

left join中关于where和on条件的几个知识点：

    1.多表left join是会生成一张临时表，并返回给用户

    2.where条件是针对最后生成的这张临时表进行过滤，过滤掉不符合where条件的记录，是真正的不符合就过滤掉。

    3.on条件是对left join的右表进行条件过滤，但依然返回左表的所有行，右表中没有的补为NULL

    4.on条件中如果有对左表的限制条件，无论条件真假，依然返回左表的所有行,但是会影响右表的匹配值。也就是说on中左表的限制条件只影响右表的匹配内容，不影响返回行数。

结论：

    1.where条件中对左表限制，不能放到on后面

    2.where条件中对右表限制，放到on后面，会有数据行数差异，比原来行数要多

 

测试：

创建两张表：

CREATE TABLE t1(id INT,name VARCHAR(20));

insert  into `t1`(`id`,`name`) values (1,'a11');

insert  into `t1`(`id`,`name`) values (2,'a22');

insert  into `t1`(`id`,`name`) values (3,'a33');

insert  into `t1`(`id`,`name`) values (4,'a44');

 

CREATE TABLE t2(id INT,local VARCHAR(20));

insert  into `t2`(`id`,`local`) values (1,'beijing');

insert  into `t2`(`id`,`local`) values (2,'shanghai');

insert  into `t2`(`id`,`local`) values (5,'chongqing');

insert  into `t2`(`id`,`local`) values (6,'tianjin');

 

测试01：返回左表所有行，右表符合on条件的原样匹配，不满足条件的补NULL

root@localhost:cuigl 11:04:25 >SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id;

+------+------+----------+

| id   | name | local    |

+------+------+----------+

|    1 | a11  | beijing  |

|    2 | a22  | shanghai |

|    3 | a33  | NULL     |

|    4 | a44  | NULL     |

+------+------+----------+

4 rows in set (0.00 sec)

 

测试02：on后面增加对右表的限制条件：t2.local='beijing'

结论02：左表记录全部返回，右表筛选条件生效

root@localhost:cuigl 11:19:42 >SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id and t2.local='beijing';

+------+------+---------+

| id   | name | local   |

+------+------+---------+

|    1 | a11  | beijing |

|    2 | a22  | NULL    |

|    3 | a33  | NULL    |

|    4 | a44  | NULL    |

+------+------+---------+

4 rows in set (0.00 sec)

 

测试03：只在where后面增加对右表的限制条件：t2.local='beijing'

结论03：针对右表，相同条件，在where后面是对最后的临时表进行记录筛选，行数可能会减少；在on后面是作为匹配条件进行筛选，筛选的是右表的内容。

root@localhost:cuigl 11:20:07 >SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id where t2.local='beijing';  

+------+------+---------+

| id   | name | local   |

+------+------+---------+

|    1 | a11  | beijing |

+------+------+---------+

1 row in set (0.01 sec)

 

测试04：t1.name='a11' 或者 t1.name='a33'

结论04：on中对左表的限制条件，不影响返回的行数，只影响右表的匹配内容

root@localhost:cuigl 11:24:46 >SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id and t1.name='a11'; 

+------+------+---------+

| id   | name | local   |

+------+------+---------+

|    1 | a11  | beijing |

|    2 | a22  | NULL    |

|    3 | a33  | NULL    |

|    4 | a44  | NULL    |

+------+------+---------+

4 rows in set (0.00 sec)

root@localhost:cuigl 11:25:04 >SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id and t1.name='a33';

+------+------+-------+

| id   | name | local |

+------+------+-------+

|    1 | a11  | NULL  |

|    2 | a22  | NULL  |

|    3 | a33  | NULL  |

|    4 | a44  | NULL  |

+------+------+-------+

4 rows in set (0.00 sec)

 

测试05：where t1.name='a33' 或者 where t1.name='a22'

结论05：where条件是在最后临时表的基础上进行筛选，显示只符合最后where条件的行

root@localhost:cuigl 11:25:15 >SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id where t1.name='a33';  

+------+------+-------+

| id   | name | local |

+------+------+-------+

|    3 | a33  | NULL  |

+------+------+-------+

1 row in set (0.00 sec)

root@localhost:cuigl 11:27:27 >SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id where t1.name='a22';

+------+------+----------+

| id   | name | local    |

+------+------+----------+

|    2 | a22  | shanghai |

+------+------+----------+

1 row in set (0.00 sec)

转摘: https://www.cnblogs.com/cuisi/p/7685893.html
————————————————
版权声明：本文为CSDN博主「栗子木」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_42214548/article/details/80324111