理解 inner join 和 outer join
inner join :从两个或多个表中,仅返回满足关联条件的行。
left (outer) join :tableA left join tableB 返回表A中所有的记录,对表B中没有符合关联条件的记录返回NULL
full (outer) join:tableA full join tableB 返回表A和B中所有的记录,没有符合关联条件的记录返回NULL。
TABLEA:
C_ID | C_ZT | C_NAME |
1 | 关闭 | 工单1 |
2 | 正常 | 工单2 |
3 | 关闭 | 工单3 |
4 | 正常 | 工单4 |
注意:表B中第三条记录的C_NAME为"工单5" 不是"工单3"
TABLEB:
C_ID | C_ZT | C_NAME |
1 | 关闭 | 工单1 |
2 | 正常 | 工单2 |
3 | 关闭 | 工单5 |
4 | 正常 | 工单4 |
=================================================
- select a.*,b.* from tablea a inner join tableb b on (a.c_id=b.c_id and a.c_name = b.c_name)
select a.*,b.* from tablea a inner join tableb b on (a.c_id=b.c_id and a.c_name = b.c_name)
rownum | C_ID | C_ZT | C_NAME | C_ID | C_ZT | C_NAME |
1 | 1 | 关闭 | 工单1 | 1 | 关闭 | 工单1 |
2 | 2 | 正常 | 工单2 | 2 | 正常 | 工单2 |
3 | 4 | 正常 | 工单4 | 4 | 正常 | 工单4 |
- select a.*,b.* from tablea a left join tableb b on (a.c_id=b.c_id and a.c_name = b.c_name)
select a.*,b.* from tablea a left join tableb b on (a.c_id=b.c_id and a.c_name = b.c_name)
rownum | C_ID | C_ZT | C_NAME | C_ID | C_ZT | C_NAME |
1 | 1 | 关闭 | 工单1 | 1 | 关闭 | 工单1 |
2 | 2 | 正常 | 工单2 | 2 | 正常 | 工单2 |
3 | 4 | 正常 | 工单4 | 4 | 正常 | 工单4 |
4 | 3 | 关闭 | 工单3 | - | - | - |
- select a.*,b.* from tablea a right join tableb b on (a.c_id=b.c_id and a.c_name = b.c_name)
select a.*,b.* from tablea a right join tableb b on (a.c_id=b.c_id and a.c_name = b.c_name)
id | C_ID | C_ZT | C_NAME | C_ID | C_ZT | C_NAME |
1 | 1 | 关闭 | 工单1 | 1 | 关闭 | 工单1 |
2 | 2 | 正常 | 工单2 | 2 | 正常 | 工单2 |
3 | - | - | - | 3 | 关闭 | 工单5 |
4 | 4 | 正常 | 工单4 | 4 | 正常 | 工单4 |
- select a.*,b.* from tablea a full join tableb b on (a.c_id=b.c_id and a.c_name = b.c_name)
select a.*,b.* from tablea a full join tableb b on (a.c_id=b.c_id and a.c_name = b.c_name)
返回五条记录:
第四条是别名为A的记录,由于在别名为B的表中没有找到记录所以B中的字段值返回了空
第四条是别名为B的记录,由于在别名为A的表中没有找到记录所以A中的字段值返回了空
rownum | C_ID | C_ZT | C_NAME | C_ID | C_ZT | C_NAME |
1 | 1 | 关闭 工单1 | 1 | 关闭 | 工单1 | |
2 | 2 | 正常 工单2 | 2 | 正常 | 工单2 | |
3 | 4 | 正常 工单4 | 4 | 正常 | 工单4 | |
4 | 3 | 关闭 工单3 | - | - | - | |
5 | - | - | - | 3 | 关闭 | 工单5 |
如果查询语句为:
- select gd.*,gd2.* from tableA gd full join tableB gd2 on (gd.c_id = gd2.c_id and gd.c_name = '工单5');
select gd.*,gd2.* from tableA gd full join tableB gd2 on (gd.c_id = gd2.c_id and gd.c_name = '工单5');
输出结果为:
rownum | C_ID | C_ZT | C_NAME | C_ID | C_ZT | C_NAME |
1 | 1 | 关闭 | 工单1 | - | - | - |
2 | 2 | 正常 | 工单2 | - | - | - |
3 | 3 | 关闭 | 工单3 | - | - | - |
4 | 4 | 正常 | 工单4 | - | - | - |
5 | - | - | - | 1 | 关闭 | 工单1 |
6 | - | - | - | 2 | 正常 | 工单2 |
7 | - | - | - | 3 | 关闭 | 工单5 |
8 | - | - | - | 4 | 正常 | 工单4 |
注意是8行。
- select gd.*,gd2.* from tableA gd full join tableB gd2 on (gd.c_id = gd2.c_id and gd2.c_name = '工单5');
select gd.*,gd2.* from tableA gd full join tableB gd2 on (gd.c_id = gd2.c_id and gd2.c_name = '工单5');
rownum | C_ID | C_ZT | C_NAME | C_ID | C_ZT | C_NAME |
1 | 3 | 关闭 | 工单3 | 3 | 关闭 | 工单5 |
2 | 1 | 关闭 | 工单1 | - | - | - |
3 | 4 | 正常 | 工单4 | - | - | - |
4 | 2 | 正常 | 工单2 | - | - | - |
5 | - | - | - | 1 | 关闭 | 工单1 |
6 | - | - | - | 2 | 正常 | 工单2 |
7 | - | - | - | 4 | 正常 | 工单4 |
注意是7行。
再看下面的:
- select gd.*,gd2.* from tableA gd left join tableB gd2 on (gd.c_id = gd2.c_id and gd.c_name = '工单5');
select gd.*,gd2.* from tableA gd left join tableB gd2 on (gd.c_id = gd2.c_id and gd.c_name = '工单5');
rownum | C_ID | C_ZT | C_NAME | C_ID | C_ZT | C_NAME |
1 | 1 | 关闭 | 工单1 | - | - | - |
2 | 2 | 正常 | 工单2 | - | - | - |
3 | 3 | 关闭 | 工单3 | - | - | - |
4 | 4 | 正常 | 工单4 | - | - | - |
- select gd.*,gd2.* from tableA gd left join tableB gd2 on (gd.c_id = gd2.c_id and gd2.c_name = '工单5');
select gd.*,gd2.* from tableA gd left join tableB gd2 on (gd.c_id = gd2.c_id and gd2.c_name = '工单5');
rownum | C_ID | C_ZT | C_NAME | C_ID | C_ZT | C_NAME |
1 | 3 | 关闭 | 工单3 | 3 | 关闭 | 工单5 |
2 | 1 | 关闭 | 工单1 | - | - | - |
3 | 4 | 正常 | 工单4 | - | - | - |
4 | 2 | 正常 | 工单2 | - | - | - |
- select gd.*,gd2.* from tableA gd left join tableB gd2 on (gd.c_id = gd2.c_id and gd.c_name = '工单2');
select gd.*,gd2.* from tableA gd left join tableB gd2 on (gd.c_id = gd2.c_id and gd.c_name = '工单2');
rownum | C_ID | C_ZT | C_NAME | C_ID | C_ZT | C_NAME |
1 | 1 | 关闭 | 工单1 | - | - | - |
2 | 2 | 正常 | 工单2 | 2 | 正常 | 工单2 |
3 | 3 | 关闭 | 工单3 | - | - | - |
4 | 4 | 正常 | 工单4 | - | - | - |
以前我一值会记录on中的条件和where中一样,会对最终的结果做过滤,实际上是理解错了。
比如A left join B 无论在ON 子句中加什么条件去过滤A表中的记录是不可能的,都会输出A表中所有的记录,唯一有区别的时,如果在做两个表关联的记录时,如果找不到对应的关联记录就对B表中的字符赋予NULL。
效果等同于,先对两个条进行内联接,然后都对A表中没有匹配上的记录全部输出,对应B中的字段赋予NULL。
where 子句中的条件是会对联接后的记录做过滤的。比如:
- select gd.*,gd2.* from tableA gd left join tableB gd2 on (gd.c_id = gd2.c_id ) where gd.c_name = '工单2';
select gd.*,gd2.* from tableA gd left join tableB gd2 on (gd.c_id = gd2.c_id ) where gd.c_name = '工单2';
只会查询到一条记录,并不是四条记录
rownum | C_ID | C_ZT | C_NAME | C_ID | C_ZT | C_NAME |
1 | 2 | 正常 | 工单2 | 2 | 正常 | 工单2 |