日期:2014-05-16  浏览次数:20392 次

left join、inner join : on与where 区别

a表 ? left join? b表 ? ?时必定连接一张表

a表永远保留 ,操作的全是b表)

(1)如果on条件作用在b表字段上,则不符合条件的时候,当前b表所有的内容都清空
(2)如果加on条件作用在a表字段上,则不符合条件的时候,清空当前b表的所有记录
(3)where条件是作用在连接后的复合表的记录(a、b表当成一个整体),不符合条件,直接删除这条组合记录

table :?? STUDENT(id, name); ? ? ?? CLASS(id, class):
??????????????? id ?? name ? ? ? ? ? ? ? ? ? ? id ? ? ? ? ? ?class
???????????????? 1 ?? "张三" ? ? ? ? ? ? ? ? ? ? 1 ? ? ? ? ? ? no.1
???????????????? 2? “李四” ? ? ? ? ? ? ? ? ? 2 ? ? ? ? ? ? no.2
???????????????? 3? “王五”?????????????????? 3???????????? no.2
???????????????? 4??? "赵六"???????????????????

Sql语句1: SELECT a.id, a.name, b.class FROM STUDENT AS s LEFT JOIN CLASS AS c ON s.id=c.id AND s.name="张三";
id ? ? ?? name? ? ? ? ? class
? 1 ????? "张三" ? ? ? ?? no.1
? 2???? “李四” ? ? ? (不符合条件清空)
? 3???? “王五”?????? (不符合条件清空)
? 4?????? "赵六"???????? (不符合条件清空)

Sql语句2: SELECT a.id, a.name, b.class FROM STUDENT AS s LEFT JOIN CLASS AS c ON s.id=c.id AND c.class="no.2";
id ? ? ?? name? ? ? ? ? class
? 1 ????? "张三" ? ? ? ? (不符合条件清空)
? 2???? “李四” ? ? ? no.2
? 3???? “王五”?????? no.2
? 4?????? "赵六"???????? (不符合条件清空)

Sql语句3: SELECT a.id, a.name, b.class FROM STUDENT AS s LEFT JOIN CLASS AS c ON s.id=c.id WHERE s.name="张三";
id ? ? ?? name? ? ? ? ? class
? 1 ????? "张三" ? ? ? ?? no.1
(不符合条件清空)
(不符合条件清空)
(不符合条件清空)

Sql语句4: SELECT a.id, a.name, b.class FROM STUDENT AS s LEFT JOIN CLASS AS c ON s.id=c.id WHERE c.class="no.2";
id ? ? ?? name? ? ? ? ? class
(不符合条件清空)
? 2???? “李四” ? ? ? no.2
? 3???? “王五”?????? no.2
(不符合条件清空)

******************************************************
inner join 只有符合条件的数据才显示 ON 和WHERE 作用相同

Sql语句5: SELECT a.id, a.name, b.class FROM STUDENT AS s INNER JOIN CLASS AS c ON s.id=c.id AND s.name="张三";
id ? ? ?? name? ? ? ? ? class
? 1 ????? "张三" ? ? ? ?? no.1
?(不符合条件清空)
?(不符合条件清空)
?(不符合条件清空)

Sql语句6: SELECT a.id, a.name, b.class FROM STUDENT AS s INNER JOIN CLASS AS c ON s.id=c.id AND c.class="no.2";
id ? ? ?? name? ? ? ? ? class
(不符合条件清空)
? 2???? “李四” ? ? ? no.2
? 3???? “王五”?????? no.2
(不符合条件清空)

Sql语句7: SELECT a.id, a.name, b.class FROM STUDENT AS s INNER