SQL:Join:修订间差异

来自WHY42
Riguz留言 | 贡献
Created page with "=Example= <syntaxhighlight lang="bash"> A B - - 1 3 2 4 3 5 4 6 </syntaxhighlight> ==Inner Join== <syntaxhighlight lang="bash"> select * from a INNER JOIN b on a.a = b.b; select a.*, b.* from a,b where a.a = b.b; a | b --+-- 3 | 3 4 | 4 </syntaxhighlight> ==Left Join== <syntaxhighlight lang="bash"> select * from a LEFT OUTER JOIN b on a.a = b.b; select a.*, b.* from a,b where a.a = b.b(+); a | b --+----- 1 | null 2 | null 3 | 3 4 | 4 </syntax..."
 
Riguz留言 | 贡献
 
(未显示同一用户的2个中间版本)
第1行: 第1行:
[[Image:Visual-join.png]]
=Example=
=Example=
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
第55行: 第57行:
null |    5
null |    5
</syntaxhighlight>
</syntaxhighlight>
= Join with duplicated=
<syntaxhighlight lang="bash">
create table employee(
    id int not null primary key,
    name varchar(20)
);
create table record(
    id int not null primary key,
    name varchar(20),
    code int
);
insert into employee(id, name)
values
(1, 'Riguz'),
(2, 'Zijin'),
(3, 'Zhangsan'),
(4, 'Zhangsan');
insert into record(id, name, code)
values
(1, 'Riguz', 1024),
(2, 'Zijin', 2000),
(3, 'Zijin', 2000),
(4, 'Zijin', 2000),
(5, 'Zhangsan', 3001),
(6, 'Zhangsan', 3001),
(7, 'Zhangsan', 3002);
</syntaxhighlight>
==Left join==
<syntaxhighlight lang="bash">
select * from employee e
left join record r on e.name=r.name;
"1" "Riguz" "1" "Riguz" "1024"
"2" "Zijin" "2" "Zijin" "2000"
"2" "Zijin" "3" "Zijin" "2000"
"2" "Zijin" "4" "Zijin" "2000"
"3" "Zhangsan" "5" "Zhangsan" "3001"
"3" "Zhangsan" "6" "Zhangsan" "3001"
"3" "Zhangsan" "7" "Zhangsan" "3002"
"4" "Zhangsan" "5" "Zhangsan" "3001"
"4" "Zhangsan" "6" "Zhangsan" "3001"
"4" "Zhangsan" "7" "Zhangsan" "3002"
</syntaxhighlight>
To remove duplication:
<syntaxhighlight lang="bash">
select * from employee e
  left join (
  select name,code from record
  group by name
  )r
on e.name=r.name;
"1" "Riguz" "Riguz" "1024"
"2" "Zijin" "Zijin" "2000"
"3" "Zhangsan" "Zhangsan" "3001"
"4" "Zhangsan" "Zhangsan" "3001"
</syntaxhighlight>


[[Category:Database]]
[[Category:Database]]
[[Category:SQL]]
[[Category:SQL]]

2023年10月12日 (四) 03:00的最新版本

Example

A    B
-    -
1    3
2    4
3    5
4    6

Inner Join

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left Join

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right Join

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full Join

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

Join with duplicated

create table employee(
    id int not null primary key,
    name varchar(20)
);

create table record(
    id int not null primary key,
    name varchar(20),
    code int
);

insert into employee(id, name)
values
(1, 'Riguz'),
(2, 'Zijin'),
(3, 'Zhangsan'),
(4, 'Zhangsan');

insert into record(id, name, code)
values
(1, 'Riguz', 1024),
(2, 'Zijin', 2000),
(3, 'Zijin', 2000),
(4, 'Zijin', 2000),
(5, 'Zhangsan', 3001),
(6, 'Zhangsan', 3001),
(7, 'Zhangsan', 3002);

Left join

select * from employee e
left join record r on e.name=r.name;

"1"	"Riguz"	"1"	"Riguz"	"1024"
"2"	"Zijin"	"2"	"Zijin"	"2000"
"2"	"Zijin"	"3"	"Zijin"	"2000"
"2"	"Zijin"	"4"	"Zijin"	"2000"
"3"	"Zhangsan"	"5"	"Zhangsan"	"3001"
"3"	"Zhangsan"	"6"	"Zhangsan"	"3001"
"3"	"Zhangsan"	"7"	"Zhangsan"	"3002"
"4"	"Zhangsan"	"5"	"Zhangsan"	"3001"
"4"	"Zhangsan"	"6"	"Zhangsan"	"3001"
"4"	"Zhangsan"	"7"	"Zhangsan"	"3002"

To remove duplication:

select * from employee e
  left join (
  select name,code from record
  group by name
  )r 
on e.name=r.name;

"1"	"Riguz"	"Riguz"	"1024"
"2"	"Zijin"	"Zijin"	"2000"
"3"	"Zhangsan"	"Zhangsan"	"3001"
"4"	"Zhangsan"	"Zhangsan"	"3001"