create table aa(pid integer,sid integer) insert into aa values(1,1)insert into aa values(3,2)insert into aa values(4,1)create table bb(pid integer,aid integer) insert into bb values(1,11) insert into bb values(1,22) insert into bb values(3,12)insert into bb values(4,41) insert into bb values(4,42)select aa.pid,sid,aid from aa left join bb on aa.pid=bb.pid
drop table aa,bb create table aa(pid int,sid int) insert into aa values(1,1) insert into aa values(3,2) insert into aa values(4,1) create table bb(pid int,aid int) insert into bb values(1,11) insert into bb values(1,22) insert into bb values(3,12) insert into bb values(4,41) insert into bb values(4,42) select aa.pid,sid,aid from aa left join bb on aa.pid=bb.pidpid sid aid ----------- ----------- ----------- 1 1 11 1 1 22 3 2 12 4 1 41 4 1 42(5 row(s) affected)
select A.pid,A.sid,B.aid from A,B where A.pid=B.pid ??
insert into c select pid,sid,aid from B left join A on A.pid=B.pidselect * from from C/* pid sid aid ----------- ----------- ----------- 1 1 11 1 1 22 3 2 12 4 1 41 4 1 42 */
insert into C select isnull(a.pid,c.pid),isnull(sid,0),isnull(aid,0) from B b left join A a on a.pid=b.pidselect * from from C/* pid sid aid ----------- ----------- ----------- 1 1 11 1 1 22 3 2 12 4 1 41 4 1 42 */
select A.pid,A.sid,B.aid from A left join B on A.pid = B.pid
create table a(pid int,sid int) insert into a values(1,1) insert into a values(3,2) insert into a values(4,1) create table b(pid int,aid int) insert into b values(1,11) insert into b values(1,22) insert into b values(3,12) insert into b values(4,41) insert into b values(4,42) select b.pid , a.sid , b.aid from b left join a on b.pid = a.piddrop table a, b/* pid sid aid ----------- ----------- ----------- 1 1 11 1 1 22 3 2 12 4 1 41 4 1 42(所影响的行数为 5 行) */
create table a(pid int,sid int) insert into a values(1,1) insert into a values(3,2) insert into a values(4,1) create table b(pid int,aid int) insert into b values(1,11) insert into b values(1,22) insert into b values(3,12) insert into b values(4,41) insert into b values(4,42) go--1 select b.pid , a.sid , b.aid from b inner join a on b.pid = a.pid /* pid sid aid ----------- ----------- ----------- 1 1 11 1 1 22 3 2 12 4 1 41 4 1 42(所影响的行数为 5 行) */--2 select b.pid , a.sid , b.aid from b left join a on b.pid = a.pid /* pid sid aid ----------- ----------- ----------- 1 1 11 1 1 22 3 2 12 4 1 41 4 1 42(所影响的行数为 5 行) */ drop table a, b
insert into aa values(1,1)insert into aa values(3,2)insert into aa values(4,1)create table bb(pid integer,aid integer)
insert into bb values(1,11)
insert into bb values(1,22)
insert into bb values(3,12)insert into bb values(4,41)
insert into bb values(4,42)select aa.pid,sid,aid from aa left join bb on aa.pid=bb.pid
drop table aa,bb
create table aa(pid int,sid int)
insert into aa values(1,1)
insert into aa values(3,2)
insert into aa values(4,1) create table bb(pid int,aid int)
insert into bb values(1,11)
insert into bb values(1,22)
insert into bb values(3,12)
insert into bb values(4,41)
insert into bb values(4,42) select aa.pid,sid,aid
from aa
left join bb on aa.pid=bb.pidpid sid aid
----------- ----------- -----------
1 1 11
1 1 22
3 2 12
4 1 41
4 1 42(5 row(s) affected)
select
A.pid,A.sid,B.aid
from
A,B
where
A.pid=B.pid ??
select pid,sid,aid
from B
left join A
on A.pid=B.pidselect * from from C/*
pid sid aid
----------- ----------- -----------
1 1 11
1 1 22
3 2 12
4 1 41
4 1 42
*/
select isnull(a.pid,c.pid),isnull(sid,0),isnull(aid,0)
from B b
left join A a
on a.pid=b.pidselect * from from C/*
pid sid aid
----------- ----------- -----------
1 1 11
1 1 22
3 2 12
4 1 41
4 1 42
*/
select A.pid,A.sid,B.aid
from A left join B on
A.pid = B.pid
insert into a values(1,1)
insert into a values(3,2)
insert into a values(4,1) create table b(pid int,aid int)
insert into b values(1,11)
insert into b values(1,22)
insert into b values(3,12)
insert into b values(4,41)
insert into b values(4,42) select b.pid , a.sid , b.aid
from b left join a
on b.pid = a.piddrop table a, b/*
pid sid aid
----------- ----------- -----------
1 1 11
1 1 22
3 2 12
4 1 41
4 1 42(所影响的行数为 5 行)
*/
insert into a values(1,1)
insert into a values(3,2)
insert into a values(4,1) create table b(pid int,aid int)
insert into b values(1,11)
insert into b values(1,22)
insert into b values(3,12)
insert into b values(4,41)
insert into b values(4,42)
go--1
select b.pid , a.sid , b.aid
from b inner join a
on b.pid = a.pid
/*
pid sid aid
----------- ----------- -----------
1 1 11
1 1 22
3 2 12
4 1 41
4 1 42(所影响的行数为 5 行)
*/--2
select b.pid , a.sid , b.aid
from b left join a
on b.pid = a.pid
/*
pid sid aid
----------- ----------- -----------
1 1 11
1 1 22
3 2 12
4 1 41
4 1 42(所影响的行数为 5 行)
*/
drop table a, b