1.现在又两个表,A(a,b,c)和B(d,e,f)
2.查询a,b,d,e,条件是c = f
有两种形式:
1)select A.a, A.b, B.d, b.e
from A, B
where A.c = B.f;
2)select A.a, A.b, B.d, b.e
from A inner join B on A.c = B.f疑问:这两个sql查询的结果集应该是一样的吧?内连接的话就可以用这个多表查询实现吧?它们俩的效率差别如何?别的连接(左连接,右连接等)不能用这种方式替代吧?
2.查询a,b,d,e,条件是c = f
有两种形式:
1)select A.a, A.b, B.d, b.e
from A, B
where A.c = B.f;
2)select A.a, A.b, B.d, b.e
from A inner join B on A.c = B.f疑问:这两个sql查询的结果集应该是一样的吧?内连接的话就可以用这个多表查询实现吧?它们俩的效率差别如何?别的连接(左连接,右连接等)不能用这种方式替代吧?
在SQL Server的查询分析器中,两种查询语句的执行计划完全一样,所以应该没有效率上的差别。
但是确实看到过一些SQL规范提到不要用where实现Join。
至于left join/right join,过去SQL Server有过*=和=*的where子句形式,后来好像不再支持了。那种搞法不直观,是不太好。还是老老实实用left join这种关键字可靠些。
SELECT * FROM STUDENTS S INNER JOIN STUDENTS_SC SC ON S.S_ID = SC.S_ID;
SELECT * FROM STUDENTS S, STUDENTS_SC SC WHERE S.S_ID = SC.S_ID;
外连接:
左连接:
SELECT * FROM STUDENTS S LEFT JOIN STUDENTS_SC SC ON S.S_ID = SC.S_ID;
SELECT * FROM STUDENTS S, STUDENTS_SC SC WHERE S.S_ID = SC.S_ID(+);
右连接:
SELECT * FROM STUDENTS S RIGHT JOIN STUDENTS_SC SC ON S.S_ID = SC.S_ID;
SELECT * FROM STUDENTS S, STUDENTS_SC SC WHERE S.S_ID(+) = SC.S_ID;
全外连接:
SELECT * FROM STUDENTS S FULL JOIN STUDENTS_SC SC ON S.S_ID = SC.S_ID;
SELECT *
FROM STUDENTS S, STUDENTS_SC SC
WHERE S.S_ID(+) = SC.S_ID
UNION
SELECT * FROM STUDENTS S, STUDENTS_SC SC WHERE S.S_ID = SC.S_ID(+);
自己看下吧~对应的上下两个是一样的效果