此文有网上多片好文 拼接而成
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录(以左表主表,右表为辅表)
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录(以右表主表,左表为辅表)
inner join(等值连接) 只返回两个表中联结字段相等的行(交集)
左联接和右联接 又称为外连接
**************
* left join *
* 左连接 *
**************
1. mysql> select * from a;
+----+
| ID |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+----+
6 rows in set (0.05 sec)
mysql> select * from b;
+----+
| ID |
+----+
| 1 |
| 2 |
| 3 |
+----+
2. mysql> select a.id,b.id from a left join b on a.id=b.id;
+----+------+
| id | id |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
+----+------+
左表(a,主表)的记录将会全部表示出来,而右表(b,辅表)只会显示符合搜索条件的记录(例子中为: a.id = b.id).
b表记录不足的地方均为NULL.
on 后面的 and 条件语句
说明: and 后的条件语句 只会对 辅表(b表)产生作用,不会对主表(a表)产生作用。即 左连接是以左边的表 作为主表。
因为 on条件是 在生成临时表时 使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
mysql> select a.id,b.id from a left join b on a.id=b.id and b.id <3;
+----+------+
| id | id |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
+----+------+
6 rows in set (0.00 sec)
mysql> select a.id,b.id from a left join b on a.id=b.id and a.id <3;
+----+------+
| id | id |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
+----+------+
where
mysql> select a.id,b.id from a left join b on a.id=b.id where b.id <3;
+----+------+
| id | id |
+----+------+
| 1 | 1 |
| 2 | 2 |
+----+------+
where条件是在临时表生成好后,再对临时表进行过滤的条件。
这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
*****************
* right join *
* 右连接 *
*****************
mysql> select b.id,a.id from a right join b on b.id=a.id;
+----+------+
| id | id |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
右表(b,主表)的记录将会全部表示出来,而左表(a,辅表)只会显示符合搜索条件的记录(例子中为: b.id = a.id).
a表记录不足的地方均为NULL.
on 后面的 and 条件语句
说明:and 后的条件语句 只会对 辅表(a表)产生作用,不会对主表(b表)产生作用。即 右连接是以右边的表 作为主表。
因为 on条件是 在生成临时表时 使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
mysql> select b.id,a.id from a right join b on b.id=a.id and a.id <3;
+----+------+
| id | id |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | NULL |
+----+------+
mysql> select b.id,a.id from a right join b on b.id=a.id and b.id <3;
+----+------+
| id | id |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | NULL |
+----+------+
where
mysql> select a.id,b.id from a right join b on a.id=b.id where a.id <3;
+------+----+
| id | id |
+------+----+
| 1 | 1 |
| 2 | 2 |
+------+----+
where条件是在临时表生成好后,再对临时表进行过滤的条件。
这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
**************
* inner join *
* 内连接 *
**************
在inner join中 两张表是是平等的,限制条件会对两表张表都起使用。
inner join 中on后面的限制条件将全部起作用,这与where的执行结果是一样的
取两个表的交集
mysql> select b.id,a.id from a join b on b.id=a.id and a.id <3;
+----+----+
| id | id |
+----+----+
| 1 | 1 |
| 2 | 2 |
+----+----+
mysql> select b.id,a.id from a join b on b.id=a.id where a.id <3;
+----+----+
| id | id |
+----+----+
| 1 | 1 |
| 2 | 2 |
+----+----+
***********************
* on和where的区别 *
***********************
1.
先笛卡尔积, 然后再on过滤, 如果join是inner的, 就继续往下走, 如果join 是left join,
就把on过滤掉的左主表中的数据再添加回来; 然后再执行where里的过滤;
on中不是最终过滤, 因为后面left join还可能添加回来, 而where才是最终过滤.
只有当使用外连接(left, right)时, on 和 where 才有这个区别, 如果用inner join, 在哪里制定都一样,
因为on 之后就是where, 中间没有其它步骤.
2.
SQL语句如下:
SELECT *
FROM 表1
LEFT JOIN 表2 ON 表1.id = 表2.id AND 表2.Name != 'ff'
WHERE 表1.NAME != 'aa'
步骤1:返回笛卡尔积(SELECT * FROM 表1 CROSS JOIN 表2)
步骤2:应用ON筛选器(当前的条件为 表1.id = 表2.id AND 表2.Name != 'ff')
步骤3:添加外部行
这一步只对OUTER JOIN起作用,如果是LEFT JOIN会以左边的表为保留表,如果是RIGHT JOIN会以右边的表为保留表。所谓外部行是指,保留表中的行。
即使第二步的ON过滤掉了一些行,在这一步,会根据保留表添加第二步过滤掉的行。当前的例子,不存在这种情况。
步骤4.应用WHERE筛选器(当前是Name != ‘aa’)过滤前三步所生成虚拟表的数据。
总结:如果SQL用的是Left Join ,On后面的条件对Left的表没有作用,只对Right的表有过滤作用,Where语句可以对Left的表有过滤作用
如果SQL用的是Right Join ,On后面的条件对Right的表没有作用,只对Left的表有过滤作用,Where语句可以对Right的表有过滤作用
转载于:https://blog.51cto.com/wxhh12/1244293