--判断如果系统里存在'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
/* 请写出其他写法 */
--右连接
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 ,不会其他写法。
A.Name,
A.Department,
B.ID,
B.University,
B.Major
from t_EmployeeBasicInfo A, t_EmployeeEducation B where A.ID*=B.ID兼容级别必须是80
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
where A.ID*=B.ID
--右连接
where A.ID=*B.ID
select A.ID,
A.Name,
A.Department,
B.ID,
B.University,
B.Major
from t_EmployeeBasicInfo A, t_EmployeeEducation B
order by A.ID
另外,楼主所说的其他写法,是不是说不用join,而且改写成使用子查询等方法?