left join

DB: MySQL

 

1. create table and insert values:

create table t1(id int, name varchar(30));
insert into t1(id, name) values(1, 'a');
insert into t1(id, name) values(2, 'b');

create table t2(id int, name varchar(30));
insert into t2(id, name) values(1, 'c');
insert into t2(id, name) values(3, 'd');

 

2. left join:

mysql> select t1.*, t2.* from t1 left join t2 on t1.id = t2.id;
+------+------+------+------+
| id   | name | id   | name |
+------+------+------+------+
|    1 | a    |    1 | c    |
|    2 | b    | NULL | NULL |
+------+------+------+------+
2 rows in set (0.00 sec)

 若单单只用left join，则表示先把t1表里面的所有数据都取出来，然后再把表t2满足on后面条件的行取出来

 

3. left join + where:

mysql> select t1.*, t2.* from t1 left join t2 on t1.id = t2.id
    -> where t1.id = 1;
+------+------+------+------+
| id   | name | id   | name |
+------+------+------+------+
|    1 | a    |    1 | c    |
+------+------+------+------+
1 row in set (0.00 sec)

 这种情况是在第2种情况的前提下加了个where，可以这样来理解：先把表t1的数据都取现来，然后再把表t2满足on后面条件的行取出来，最后再把得到的所有数据除去不满足where条件的行

 

4. left join/inner join区别：

我觉得可以这样来理解：left join其实就是 = 表t1的所有行记录(t1.*, NULL, NULL...) 与 inner join的结果集(t1.*, t2.*)的一个合集

 

5. on条件:

mysql> select t1.*, t2.* from t1 left join t2 on t1.id = t2.id and 1 = 2;
+------+------+------+------+
| id   | name | id   | name |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    | NULL | NULL |
+------+------+------+------+
2 rows in set (0.00 sec)

--same to select t1.*, t2.* from t1 left join t2 on 1 = 2;

 

on条件与where条件完全就是两码事，也许有人会认为下面这两条SQL是一个意思，那就是搞错了，呵呵。。。

mysql> select t1.*, t2.* from t1 left join t2
    -> on t1.id = t2.id and t1.id = 1;
+------+------+------+------+
| id   | name | id   | name |
+------+------+------+------+
|    1 | a    |    1 | c    |
|    2 | b    | NULL | NULL |
+------+------+------+------+
2 rows in set (0.00 sec)

mysql> select t1.*, t2.* from t1 left join t2
    -> on t1.id = t2.id 
    -> where t1.id = 1 or t1.id is null;
+------+------+------+------+
| id   | name | id   | name |
+------+------+------+------+
|    1 | a    |    1 | c    |
+------+------+------+------+
1 row in set (0.00 sec)

--看出来两条SQL的区别了吧