数据库SQL左连接解惑!(第一版)
工具:Oracle 11gR2 ,cmd
时间:2019/7/2
今天遇到如下两张表,由于对left join理解不深,对其结果集产生了如下疑惑!
表T1:
T1_id | str |
---|---|
1 | A |
1 | B |
2 | C |
2 | D |
3 | E |
3 | F |
4 | G |
4 | H |
表T2:
T2_id | str |
---|---|
1 | a |
1 | b |
2 | c |
2 | d |
3 | e |
3 | f |
5 | g |
5 | h |
附:sql语句如下:
--建表语句
create table T1(t1_id int,str varchar(10));
create table T2(t2_id int,str varchar(10));
--测试数据
insert into T1 values (1,'A');
insert into T1 values (1,'B');
insert into T1 values (2,'C');
insert into T1 values (2,'D');
insert into T1 values (3,'E');
insert into T1 values (3,'F');
insert into T1 values (4,'G');
insert into T1 values (4,'H');
insert into T2 values (1,'a');
insert into T2 values (1,'b');
insert into T2 values (2,'c');
insert into T2 values (2,'d');
insert into T2 values (3,'e');
insert into T2 values (3,'f');
insert into T2 values (5,'g');
insert into T2 values (5,'h');
commit;
--执行如下查询语句
Select * from T1 left join T2 on T1. T1_id=T2. T2_id;
由于对left join 理解不是很深,对其结果产生了疑惑,猜测有两种结果
猜测一:
T1_ID | STR | T2_ID | STR |
---|---|---|---|
1 | A | 1 | a |
1 | B | 1 | a |
2 | C | 2 | c |
2 | D | 2 | c |
3 | E | 3 | E |
3 | F | 3 | E |
4 | G | ||
4 | H |
猜测二:
T1_ID | STR | T2_ID | STR |
---|---|---|---|
1 | B | 1 | a |
1 | A | 1 | a |
1 | B | 1 | b |
1 | A | 1 | b |
2 | D | 2 | c |
2 | C | 2 | c |
2 | D | 2 | d |
2 | C | 2 | d |
3 | F | 3 | e |
3 | E | 3 | e |
3 | F | 3 | f |
3 | E | 3 | f |
4 | H | ||
4 | G |
验证:结果同猜测二