给菜鸟详解MS SQL 中:inner join 相关的语句

inner   join   与   where   a=b
out   join   与   where   a*=b*
left   join   与     where   a*=b
right   join   与   where   a=b*


------解决方案--------------------
1.
create table table1
(id int,
name varchar(10))
create table table2
(id int,
score int)
insert into table1 select 1, 'lee '
insert into table1 select 2, 'zhang '
insert into table1 select 4, 'wang '
insert into table2 select 1,90
insert into table2 select 2,100
insert into table2 select 3,70
select * from table1
select * from table2
------------------------
id name
----------- ----------
1 lee
2 zhang
4 wang

id score
----------- -----------
1 90
2 100
3 70
-----------------------------
select * from table1 left join table2 on table1.id=table2.id --left outer join
----------------------------------------------------
id name id score
----------- ---------- ----------- -----------
1 lee 1 90
2 zhang 2 100
4 wang NULL NULL
----------------------------------------------------
select * from table1 right join table2 on table1.id=table2.id --right outer join
----------------------
id name id score
----------- ---------- ----------- -----------
1 lee 1 90
2 zhang 2 100
NULL NULL 3 70
----------------------
select * from table1 full join table2 on table1.id=table2.id --full outer join
---------------------
id name id score
----------- ---------- ----------- -----------
1 lee 1 90
2 zhang 2 100
4 wang NULL NULL
NULL NULL 3 70
------------------------
select * from table1 join table2 on table1.id=table2.id
select * from table1 inner join table2 on table1.id=table2.id
-- <====>
select a.*,b.* from table1 a,table2 b where a.id=b.id
select * from table1 cross join table2 where table1.id=table2.id --不能用on
--------------------------------------------------
id name id score
----------- ---------- ----------- -----------
1 lee 1 90
2 zhang 2 100
------------------------------------------------
select * from table1 cross join table2
select * from table1,table2
------------------------------------------------
id name id score
----------- ---------- ----------- -----------
1 lee 1 90
2 zhang 1 90
4 wang 1 90
1 lee 2 100
2 zhang 2 100
4 wang 2 100
1 lee 3 70
2 zhang 3 70
4 wang 3 70
-------------------------------------------------
drop table table1,table2
------解决方案--------------------
呵呵 不错 听课还有分拿。。