请教 join 的不同写法

[bean_sql]

--判断如果系统里存在't_EmployeeBasicInfo'表则删除该表
if OBJECT_ID('t_EmployeeBasicInfo','U') is not  null drop table t_EmployeeBasicInfo
go
--创建t_EmployeeBasicInfo表
create table t_EmployeeBasicInfo
(
	ID varchar(20),
	Name varchar(50),
	Department varchar(100)
)
go
--插入数据
insert into t_EmployeeBasicInfo
select 'ID0001','Andy','IT' union all
select 'ID0002','Job','BIO' union all
select 'ID0003','Jack','BEM' 
go
--判断如果系统里存在't_EmployeeEducation'表则删除该表
if OBJECT_ID('t_EmployeeEducation','U') is not  null drop table t_EmployeeEducation
go
--创建t_EmployeeEducation表
create table t_EmployeeEducation
(
	ID varchar(20),
	University varchar(50),
	Major varchar(50)
)
go
--插入数据
insert into t_EmployeeEducation
select 'ID0002','东南大学','Business' union all
select 'ID0003','华东师范','Software Development' union all
select 'ID0004','复旦大学','Medical professional'
go
--查询't_EmployeeBasicInfo'表数据
select * from t_EmployeeBasicInfo
go
--查询't_EmployeeEducation'表数据
select * from t_EmployeeEducation
go
--左连接 写法1
select	A.ID,
		A.Name,
		A.Department,
		B.ID,
		B.University,
		B.Major
from t_EmployeeBasicInfo A
left join t_EmployeeEducation B on A.ID=B.ID
--左连接 写法2
/*  请写出其他写法 */




go
--右连接 写法1
select	A.ID,
		A.Name,
		A.Department,
		B.ID,
		B.University,
		B.Major
from t_EmployeeBasicInfo A
right join t_EmployeeEducation B on A.ID=B.ID
--右连接 写法2
/*  请写出其他写法 */




go
--内连接 写法1
select	A.ID,
		A.Name,
		A.Department,
		B.ID,
		B.University,
		B.Major
from t_EmployeeBasicInfo A
inner join t_EmployeeEducation B on A.ID=B.ID

--内连接 写法2 
select	A.ID,
		A.Name,
		A.Department,
		B.ID,
		B.University,
		B.Major
from t_EmployeeBasicInfo A,t_EmployeeEducation B where A.ID=B.ID
go
--外连接 写法1 
select	A.ID,
		A.Name,
		A.Department,
		B.ID,
		B.University,
		B.Major
from t_EmployeeBasicInfo A
full join t_EmployeeEducation B on A.ID=B.ID
--外连接 写法2
/*  请写出其他写法 */




go
--交叉连接 写法1
select	A.ID,
		A.Name,
		A.Department,
		B.ID,
		B.University,
		B.Major
from t_EmployeeBasicInfo A
cross join t_EmployeeEducation B 
order by A.ID
--交叉连接 写法2
/*  请写出其他写法 */

[叶子]

很久以前有过这样的写法，但是貌似SQL SERVER 2000开始就不支持了。

--很久以前的链接查询是这样的 
 --右连接
 SELECT Student.sno ,
        Sname ,
        Ssex ,
        Sage ,
        Cno ,
        Grade
 FROM   Student ,
        SC
 WHERE  Student.Sno = SC.Sno(*) ; 
 
 --左连接： 
 SELECT Student.sno ,
        Sname ,
        Ssex ,
        Sage ,
        Cno ,
        Grade
 FROM   Student ,
        SC
 WHERE  Student.Sno(*) = SC.Sno ; 

现在的左连接，右连接 我只会left join 和 right join ，不会其他写法。

[jinjazz]

select    A.ID,
        A.Name,
        A.Department,
        B.ID,
        B.University,
        B.Major
from t_EmployeeBasicInfo A, t_EmployeeEducation B where A.ID*=B.ID

兼容级别必须是80

[jinjazz]

select    A.ID,
        A.Name,
        A.Department,
        B.ID,
        B.University,
        B.Major
from t_EmployeeBasicInfo A
cross join t_EmployeeEducation B 
order by A.ID

把cross join换成逗号

select    A.ID,
        A.Name,
        A.Department,
        B.ID,
        B.University,
        B.Major
from t_EmployeeBasicInfo A
, t_EmployeeEducation B 
 

[bean_sql]

左连接和有链接的 * 都是放在关联条件 = 符号的左右

--左连接：
    where A.ID*=B.ID
--右连接
    where A.ID=*B.ID

[Quote=引用 1 楼 的回复:]

很久以前有过这样的写法，但是貌似SQL SERVER 2000开始就不支持了。

SQL code
--很久以前的链接查询是这样的
--右连接
SELECT Student.sno ,
Sname ,
Ssex ,
Sage ,
Cno ,
Grade
FROM Student ,
……
[/Quote]

[黄_瓜]

cross join 的等价于

select    A.ID,
        A.Name,
        A.Department,
        B.ID,
        B.University,
        B.Major
from t_EmployeeBasicInfo A, t_EmployeeEducation B 
order by A.ID

[gogodiy]

最好还是使用规范写法，楼主给出的都是，其他不规范的，要么早就被淘汰，要么下一个版本就不支持了，没必要去学习了。
另外，楼主所说的其他写法，是不是说不用join，而且改写成使用子查询等方法？

[喜阳阳]

楼主写的这些，很全了。

[xujunjian428]

楼主威武，学习了，但是个人感觉再解释其意思更完美了

[xujunjian428]

楼主威武，学习了，但是个人感觉再解释其意思更完美了

[勿勿]

sql2000后就不支持了哦。很久之前的方法了