create table a(id int) insert into a values(1) insert into a values(2) insert into a values(3) create table b(id int) insert into b values(2) insert into b values(2) insert into b values(2) insert into b values(3) insert into b values(4) insert into b values(4) goselect a.* , b.* from a inner join b on a.id = b.id /* id id ----------- ----------- 2 2 2 2 2 2 3 3(所影响的行数为 4 行) */select a.* , b.* from a left join b on a.id = b.id /* id id ----------- ----------- 1 NULL 2 2 2 2 2 2 3 3(所影响的行数为 5 行) */select a.* , b.* from a right join b on a.id = b.id /* id id ----------- ----------- 2 2 2 2 2 2 3 3 NULL 4 NULL 4(所影响的行数为 6 行) */drop table a , b
--1.这个要看你的连接条件了,像你上面的数据来看,肯定是会重复的数据 -- 看例子(inner join): with tab as ( select 1 id from dual union all select 2 id from dual union all select 3 id from dual ), tab2 as ( select 2 id from dual union all select 2 from dual union all select 2 from dual union all select 3 from dual union all select 4 from dual union all select 4 from dual ) select * from tab inner join tab2 on tab.id = tab2.id ------------------------------------------------------ --结果: tab.id tab2.id 2 2 2 2 2 2 3 3------------------------------------------------------2.左连接和右连接都是外连,这个显示的数据,要看以那个表为主表, ---外连的意思是只显示主表的数据,副表有就显示,没有就不显示 --例子(左连接): with tab as ( select 1 id, '' name from dual union all select 2 id, '' from dual union all select 3 id, '' from dual ), tab2 as ( select 2 id, '' name from dual union all select 2, '' from dual union all select 2, '' from dual union all select 3, '' from dual union all select 4, '' from dual union all select 4, '' from dual ) select * from tab left join tab2 on tab.id = tab2.id --------------------------------------------------------- --结果: 2 2 2 2 2 2 3 3 1 NULL --例子(右连接): with tab as ( select 1 id, '' name from dual union all select 2 id, '' from dual union all select 3 id, '' from dual ), tab2 as ( select 2 id, '' name from dual union all select 2, '' from dual union all select 2, '' from dual union all select 3, '' from dual union all select 4, '' from dual union all select 4, '' from dual ) select * from tab right join tab2 on tab.id = tab2.id -------------------------------------------- --结果: 2 2 2 2 2 2 3 3 NULL 4 NULL 4
inner join的时候,结果数据是2,2,2,3; left join 的时候,结果数据是1,2,2,2,3; right join的时候,结果数据是2,2,2,3,4,4
2,2,2,3left join
1,2,2,2,3right join
2,2,2,3,4,4
insert into a values(1)
insert into a values(2)
insert into a values(3)
create table b(id int)
insert into b values(2)
insert into b values(2)
insert into b values(2)
insert into b values(3)
insert into b values(4)
insert into b values(4)
goselect a.* , b.* from a inner join b on a.id = b.id
/*
id id
----------- -----------
2 2
2 2
2 2
3 3(所影响的行数为 4 行)
*/select a.* , b.* from a left join b on a.id = b.id
/*
id id
----------- -----------
1 NULL
2 2
2 2
2 2
3 3(所影响的行数为 5 行)
*/select a.* , b.* from a right join b on a.id = b.id
/*
id id
----------- -----------
2 2
2 2
2 2
3 3
NULL 4
NULL 4(所影响的行数为 6 行)
*/drop table a , b
--1.这个要看你的连接条件了,像你上面的数据来看,肯定是会重复的数据
-- 看例子(inner join):
with tab as
(
select 1 id from dual union all
select 2 id from dual union all
select 3 id from dual
),
tab2 as
(
select 2 id from dual union all
select 2 from dual union all
select 2 from dual union all
select 3 from dual union all
select 4 from dual union all
select 4 from dual
)
select * from tab inner join tab2 on tab.id = tab2.id
------------------------------------------------------
--结果:
tab.id tab2.id
2 2
2 2
2 2
3 3------------------------------------------------------2.左连接和右连接都是外连,这个显示的数据,要看以那个表为主表,
---外连的意思是只显示主表的数据,副表有就显示,没有就不显示
--例子(左连接):
with tab as
(
select 1 id, '' name from dual union all
select 2 id, '' from dual union all
select 3 id, '' from dual
),
tab2 as
(
select 2 id, '' name from dual union all
select 2, '' from dual union all
select 2, '' from dual union all
select 3, '' from dual union all
select 4, '' from dual union all
select 4, '' from dual
)
select * from tab left join tab2 on tab.id = tab2.id
---------------------------------------------------------
--结果:
2 2
2 2
2 2
3 3
1 NULL --例子(右连接):
with tab as
(
select 1 id, '' name from dual union all
select 2 id, '' from dual union all
select 3 id, '' from dual
),
tab2 as
(
select 2 id, '' name from dual union all
select 2, '' from dual union all
select 2, '' from dual union all
select 3, '' from dual union all
select 4, '' from dual union all
select 4, '' from dual
)
select * from tab right join tab2 on tab.id = tab2.id
--------------------------------------------
--结果:
2 2
2 2
2 2
3 3
NULL 4
NULL 4
left join 的时候,结果数据是1,2,2,2,3;
right join的时候,结果数据是2,2,2,3,4,4