SQL:Join

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"