换成左连接left join 就可以了
select score.*,subjects.*from score left join subjects on subject.科目=score.科目 where 学号='123'
select score.*,subjects.*from score left join subjects on subject.科目=score.科目 where 学号='123'
调试欢乐多
(
subject varchar(8),
teacher varchar(8),
phone varchar(11)
)create table score
(
sno char(3),
subject varchar(8),
grade int
)
go
insert into subject values('语文', '张老师', '1234567')
insert into subject values('数学', '刘老师', '1234568')
insert into subject values('政治', '李老师', '123456789')
insert into score values('123', '语文', 86)
insert into score values('123', '数学', 90)
insert into score values('123', '英语', 78)
insert into score values('888', '语文', 89)
insert into score values('888', '数学', 70)
go
select sc.*, su.*
from score sc left join subject su on sc.subject = su.subject
where sc.sno = '123'
go
/*以下这种方法不符合ANSI的最新规定,在sql server 2000能用*/
select sc.*, su.*
from score sc, subject su
where sc.sno = '123'
and sc.subject *= su.subject
go