首先我们要知道连接方式有:左连接、右连接、内连接、全连接以及交叉连接等几种连接方式
那么我们先说一下常用的内连接把:
内部连接 inner join(join 或 inner join) 两表都满足的组合,只返回符合条件的table1和table2的列
inner join (等值连接或内连接):只返回两个表中联结字段相等的行
SELECT * FROM `xs` INNER JOIN `cj` ON xs.id = cj.id ,SELECT * FROM `xs`,`cj` WHERE xs.id = cj.id
左连接 left join(左联接):返回包括左表中的所有记录和右表中联结字段相等记录;
A表 left join(left join 或 left outer join) B表 左连,以A表为基础,A表的全部数据,B表有的组合。没有的为null
SELECT * FROM `xs` LEFT JOIN `cj` ON xs.id = cj.id and cj.score = 96
右连接 right join(右联接):返回包括右表中的所有记录和左表中联结字段相等的记录。
A表 right join(right join 或 right outer join) B表 右连,以B表为基础,B表的全部数据,A表的有的组合。没有的为null
全连接 full outer(full join 或 full outer join) 全连 两表相同的组合在一起,
A表有,B表没有的数据(显示为null),同样B表有A表没有的显示为(null)
还有就是mysql不支持full join
SELECT * FROM `xs` RIGHT JOIN `cj` ON xs.id = cj.id
会报 1054
我们可以采用:
left join + union(可去除重复数据)+ right join
select * from A left join B on A.id = B.id (where 条件) union select *
from A right join B on A.id = B.id (where条件);
交叉连接:cross join:
概念:没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。
(table1和table2交叉连接产生3*3=9条记录);交叉连接:cross join (不带条件where...)
数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。
在使用left jion时,on和where条件的区别如下:
1、 on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
on、where、having这三个都可以加条件的子句中,on是最先执行,where次之,having最后。有时候如果这先后顺序不影响中间结果的话,那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计,它就可以减少中间运算要处理的数据,按理说应该速度是最快的。
根据上面的分析,可以知道where也应该比having快点的,因为它过滤数据后才进行sum,所以having是最慢的。但也不是说having没用,因为有时在步骤3还没出来都不知道那个记录才符合要求时,就要用having了。
在两个表联接时才用on的,所以在一个表的时候,就剩下where跟having比较了。在这单表查询统计的情况下,如果要过滤的条件没有涉及到要计算字段,那它们的结果是一样的,只是where可以使用rushmore技术,而having就不能,在速度上后者要慢。
在多表联接查询时,on比where更早起作用。系统首先根据各个表之间的联接条件,把多个表合成一个临时表后,再由where进行过滤,然后再计算,计算完后再由having进行过滤。由此可见,要想过滤条件起到正确的作用,首先要明白这个条件应该在什幺时候起作用,然后再决定放在那里
联接优化:
1)永远用小结果集驱动大的结果集
2)尽可能避免复杂的Join和子查询;
3)实际上,在 MySQL 中(仅限于 MySQL) CROSS JOIN 与 INNER JOIN 的表现是一样的,在不指定 ON 条件得到的结果都是笛卡尔积,反之取得两个表完全匹配的结果。 INNER JOIN 与 CROSS JOIN 可以省略 INNER 或 CROSS 关键字,因此下面的 SQL 效果是一样的:
4)Where 和连接性区别?
Where是在from直接取得结果集基础上再使用where条件进行数据过滤
连接性是在 from 取数据时直接使用 on 后连接条件进行数据过滤
ON 条件(“A LEFT JOIN B ON 条件表达式”中的ON)用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后,WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。
在使用Left (right) join的时候,一定要在先给出尽可能多的匹配满足条件,减少Where的执行。如:
select * from A
inner join B on B.name = A.name
left join C on C.name = B.name
left join D on D.id = C.id
where C.status>1 and D.status=1;
select * from A
inner join B on B.name = A.name
left join C on C.name = B.name and C.status>1
left join D on D.id = C.id and D.status=1
从上面例子可以看出,尽可能满足ON的条件,而少用Where的条件。从执行性能来看第二个显然更加省时。