create table t1
(
id identity(1,1) int primary key,
classid int,
name char(10),
age int
)create table t2
(
id indentity(1,1) int primary key,
classname char(10)
)select * from t1,t2 where t1.classid=t2.idselect * from t1 inner join t2 on t1.classid=t2.idselect * from t1 left join t2 on t1.classid=t2.idselect * from t1 right join t2 on t1.classid=t2.idleft join 是左连接已左边表为准选出所有左边链接右边的没有则为空right join 和上面相反
但是select * from t1,t2 where t1.classid=t2.idselect * from t1 inner join t2 on t1.classid=t2.id这两条语句的功能似乎是一样的,有什么区别没有?
(
id identity(1,1) int primary key,
classid int,
name char(10),
age int
)create table t2
(
id indentity(1,1) int primary key,
classname char(10)
)select * from t1,t2 where t1.classid=t2.idselect * from t1 inner join t2 on t1.classid=t2.idselect * from t1 left join t2 on t1.classid=t2.idselect * from t1 right join t2 on t1.classid=t2.idleft join 是左连接已左边表为准选出所有左边链接右边的没有则为空right join 和上面相反
但是select * from t1,t2 where t1.classid=t2.idselect * from t1 inner join t2 on t1.classid=t2.id这两条语句的功能似乎是一样的,有什么区别没有?
select * from t1,t2 where t1.classid=t2.id
先要连接所有行,再进行条件过滤
select * from t1 inner join t2 on t1.classid=t2.id
直接连接符合的记录。
inner join 效率高一些吧
select * from t1,t2 where t1.classid=t2.id
=
select * from t1 inner join t2 on t1.classid=t2.id都是inner join,
from t1,t2 where... 是习惯写法,到底是什么连接需要看where后的写法,如果where后没有连接条件,那就是cross join,如果是=连接,就是inner join ,还有t1.classid*=t2.id(左连接)、t1.classid=*t2.id(右连接)、t1.classid<>t2.id(不等连接,很少用)
个人认为,最好用后者写法,减少歧义的可能