高分请教关于Join这种SQL的用法

[Lastcsdner]

哪位能把Left Join、Right Join、inner Join、cross Join这些join用法通过举例等方法说明白，100分送上。不要贴SQL-Online-Book上的内容，我看了，不是很理解，贴这个就恕不给分，希望能看到一些易于理解的解释。

[Lastcsdner]

100分不够可以再加100，反正我的1000多分也没什么用处。

[daijingjie2002]

如有表A
ID,A
1 A
2 B
3 C
表B
ID, B
1 C
2 D
SELECT * FROM A LEFT JOIN B ON A.ID=B.ID
结果
ID A ID B
1 A 1 C
2 B 2 D
3 C NULL NULL
SELECT * FROM A Right JOIN B ON A.ID=B.ID
结果
ID A ID B
1 A 1 C
2 B 2 D

[daijingjie2002]

SELECT * FROM A INNER JOIN B ON A.ID=B.ID
结果
ID A ID B
1 A 1 C
2 B 2 D

[daijingjie2002]

SELECT * FROM A INNER JOIN B
结果
ID A ID B
----------- ---- ----------- ----
1 A 1 C
1 A 2 D
2 B 1 C
2 B 2 D
3 C 1 C
3 C 2 D

[Frewin]

自己在查询分析器里运行就知道啦
create table #A(id int, e varchar(30))
insert into #a
Select 1,'a'
union select 2,'b'
union select 3,'c'

create table #B(id int ,f varchar(30))
insert into #b
Select 1,'b'
union select 2,'c'
union select 5,'c'
select #a.* from #a inner join #b on #a.id =#b.id

select #a.* from #a cross join #b

drop table #a,#b

[Frewin]

自己在查询分析器里运行就知道啦
create table #A(id int, e varchar(30))
insert into #a
Select 1,'a'
union select 2,'b'
union select 3,'c'

create table #B(id int ,f varchar(30))
insert into #b
Select 1,'b'
union select 2,'c'
union select 5,'c'
select #a.* from #a inner join #b on #a.id =#b.id
select #a.*,#b.* from #a left join #b on #a.id =#b.id
select #a.*,#b.* from #a right join #b on #a.id =#b.id
select #a.* from #a cross join #b

drop table #a,#b

[JafyLiu]

create table #A
(
f1 int,
f2 char(3)
)

insert into #A
select 1, 'AAA'
insert into #A
select 2, 'BBB'
insert into #A
select 3, 'CCC'

create table #B
(
f1 int,
f3 char(2)
)

insert into #B
select 1, '一'
insert into #B
select 4, '四'
insert into #B
select 5, '五'


select *
from #A
inner join #B on #A.f1 = #B.f1

select *
from #A
left join #B on #A.f1 = #B.f1

select *
from #A
right join #B on #A.f1 = #B.f1

select *
from #A
full outer join #B on #A.f1 = #B.f1

select *
from #A
cross join #B

drop table #A
drop table #B

[daijingjie2002]

Left Join左连接也就是说列出左边表所有列，右边表有符合条件的就显示，没有就现实NULL
、Right Join与上相反
、inner Join它返回两个表中的所有列，但只返回在联接列中具有相等值的行
、cross Join它在WHERE 加上条件和INNER JOIN 一样如:
SELECT * FROM A INNER JOIN B WHERE A.ID=B.ID
ID A ID B
1 A 1 C
2 B 2 D

[rfq]

外连结
LEFT JOIN
RIGHT JOIN
FULL JOIN

内连结
INNER JOIN