Mysql中Left join ...on之后 and 与where的区别【待完善】

0.前言

create table a
(id int(10) not null primary key,
title varchar(20) not null ,
author varchar(10) not null,
date datetime not null default current_timestamp);


create table b
(author varchar(10) not null,
count int(10) not null);

insert into a values
(1,'study hadoop','lawson','2018-01-01'),
(2,'study hbase','lawson','2018-07-01'),
(3,'study opentsdb','stumb','2018-08-01'),
(4,'study kafka','lawson','2018-05-03'),
(5,'study spark','lawson','2018-04-01');


insert into b values
('lawson',10),
('jack',20);
  • 表a中的数据
mysql> select * from a;
+----+----------------+--------+---------------------+
| id | title          | author | date                |
+----+----------------+--------+---------------------+
|  1 | study hadoop   | lawson | 2018-01-01 00:00:00 |
|  2 | study hbase    | lawson | 2018-07-01 00:00:00 |
|  3 | study opentsdb | stumb  | 2018-08-01 00:00:00 |
|  4 | study kafka    | lawson | 2018-05-03 00:00:00 |
|  5 | study spark    | lawson | 2018-04-01 00:00:00 |
+----+----------------+--------+---------------------+
5 rows in set (0.00 sec)
  • 表b中的数据
mysql> select * from b;
+--------+-------+---------------------+
| author | count | date                |
+--------+-------+---------------------+
| lawson |    10 | 2018-07-01 00:00:00 |
| jack   |    20 | 2018-07-03 00:00:00 |
+--------+-------+---------------------+
2 rows in set (0.00 sec)

1.执行查询

  • 后接and
select *
from a
left join b
on 
(a.author = b.author
and a.date = '2018-07-01 00:00:00'
);
+----+----------------+--------+---------------------+--------+-------+
| id | title          | author | date                | author | count |
+----+----------------+--------+---------------------+--------+-------+
|  2 | study hbase    | lawson | 2018-07-01 00:00:00 | lawson |    10 |
|  1 | study hadoop   | lawson | 2018-01-01 00:00:00 | NULL   |  NULL |
|  3 | study opentsdb | stumb  | 2018-08-01 00:00:00 | NULL   |  NULL |
|  4 | study kafka    | lawson | 2018-05-03 00:00:00 | NULL   |  NULL |
|  5 | study spark    | lawson | 2018-04-01 00:00:00 | NULL   |  NULL |
+----+----------------+--------+---------------------+--------+-------+
5 rows in set (0.00 sec)

可以看到这里的and a.date = '2018-07-01 00:00:00'也被作为一个on条件的一部分进行join。如果整个on条件匹配成功，

  • 后接where
select *
from a
left join b
on a.author = b.author
where a.date = '2018-08-01 00:00:00';
+----+----------------+--------+---------------------+--------+-------+
| id | title          | author | date                | author | count |
+----+----------------+--------+---------------------+--------+-------+
|  3 | study opentsdb | stumb  | 2018-08-01 00:00:00 | NULL   |  NULL |
+----+----------------+--------+---------------------+--------+-------+
1 row in set (0.00 sec)

2.