表结构如下:
tsecurity
security_id | name | isin_code |
---|---|---|
1 | test01 | 122016 |
2 | test02 |
tsecurityissuer
security_id | full_name | issue_status |
---|---|---|
1 | cmftest01 | 1 |
12 | cmftest012 | 2 |
1、 左连接 left join
描述:返回包括左表中的所有记录和右表中连接字段相等的记录。
除了关联条件,其他筛选条件不放在on后面,否则会导致条件失效
select * from tsecurity t left join tsecurityissuer ts on t.security_id = ts.security_id
security_id | name | isin_code | security_id | full_name | issue_status |
---|---|---|---|---|---|
1 | test01 | 122016 | 1 | cmftest01 | 1 |
2 | test02 |
select * from tsecurity t left join tsecurityissuer ts on t.security_id = ts.security_id and ts.issue_status=1; -- 查询出来上面关联出的所有数据
注:on后面的and条件没有起到任何作用
2、内连接 inner join
只返回两个表中连接字段相等的行。
select * from tsecurity t inner join tsecurityissuer ts on t.security_id = ts.security_id
security_id | name | isin_code | security_id | full_name | issue_status |
---|---|---|---|---|---|
1 | test01 | 122016 | 1 | cmftest01 | 1 |
内连接中on后面跟and条件有效:
select * from tsecurity t inner join tsecurityissuer ts on t.security_id = ts.security_id and ts.issue_status=0; --没有查询出任何数据
3、右连接 right join
返回包括右表中的所有记录和左表中连接字段相等的记录。
除了关联条件,其他筛选条件不放在on后面,否则会导致条件失效 与左连接一样
4、全连接 full join
返回左右表中所有的记录和左右表中连接字段相等的记录。
select * from tsecurity t full join tsecurityissuer ts on t.security_id = ts.security_id
security_id | name | isin_code | security_id | full_name | issue_status |
---|---|---|---|---|---|
1 | test01 | 122016 | 1 | cmftest01 | 1 |
2 | test02 | ||||
12 | cmftest012 | 2 |
select * from tsecurity t full join tsecurityissuer ts on t.security_id = ts.security_id and ts.issue_status=0
security_id | name | isin_code | security_id | full_name | issue_status |
---|---|---|---|---|---|
1 | test01 | 122016 | |||
2 | test02 | ||||
1 | cmftest01 | 1 | |||
12 | cmftest012 | 2 |
总结:
1、除了内连接以外,其余的连接方式下除了关联条件外其他的限定条件都不应该写在on后面,应该单独写在where条件后;
2、左连接的结果集行数不一定等于主表的行数,如果出现关联表和主表是一对多的关系,结果集行数会大于主表
3、左链接和右连接本质上性能没什么区别,性能主要取决于表数量的大小,
数据量少的表做主表也能增加性能;
4、有索引的表做附表增加查询效率(主键是对表记录的约束,索引是对表记录的索引,类似于对表记录进行标记,在查询时依赖索引可以快速到大标记的那个记录增加效率)
5、oracle中where条件是从上至下的,一般把可以过滤大多数据的查询条件写在前面,这样筛选出尽可能少的结果集后续在根据条件过滤时会更高效