人大金仓多表查询

最新推荐文章于 2024-07-02 18:08:27 发布

阿龙的代码在报错

最新推荐文章于 2024-07-02 18:08:27 发布

阅读量2.4k

点赞数

文章标签： sql 数据库 sqlserver

本文链接：https://blog.csdn.net/yujinlong2002/article/details/130632119

版权

人大金仓多表查询

连接的查询类型

按照结果集的输出方式：

内连接查询、外连接查询、交叉连接查询

按照连接条件进行的比较运算符分为：

等值连接查询、非等值连接查询

其他特殊连接方式：

自然连接、自连接

实验环境的创建

# 创建表t1
create table t1(id1 int,id2 int, id3 int);
# 创建表t2
create table t2(id1 int,id4 int,id5 int);
# 插入表数据
insert into t1(id1,id2,id3) values (1,1,1),(2,2,2),(3,3,3);
insert into t2 values (1,4,4),(2,2,2),(4,6,6);
# 查看表数据
table t1;
 id1 | id2 | id3
-----+-----+-----
   1 |   1 |   1
   2 |   2 |   2
   3 |   3 |   3
# 查看表数据
table t2;
 id1 | id4 | id5
-----+-----+-----
   1 |   4 |   4
   2 |   2 |   2
   4 |   6 |   6

内连接查询

INNER JOIN

# 通过id1字段连接表t1和表t2
select * from t1 inner join t2 on t1.id1=t2.id1;
# 通过非标准sql的方式进行连接
select * from t1,t2 where t1.id1=t2.id1 ;

外连接

左外连接 LEFT OUTER JOIN
右外连接 RIGHT OUTER JOIN

全外连接 FULL OUTER JOIN

# 左外连接 LEFT OUTER JOIN
select * from t1 left outer join t2  on t1.id1 =t2.id1;
 id1 | id2 | id3 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   1 |   1 |   1 |   1 |   4 |   4
   2 |   2 |   2 |   2 |   2 |   2
   3 |   3 |   3 |     |     |

# 右外连接 RIGHT OUTER JOIN
select * from t1 right join t2 on t1.id1 = t2.id1;
 id1 | id2 | id3 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   1 |   1 |   1 |   1 |   4 |   4
   2 |   2 |   2 |   2 |   2 |   2
     |     |     |   4 |   6 |   6

# 全外连接 FULL OUTER JOIN
select * from t1 full join t2 on t1.id1 = t2.id1;
 id1 | id2 | id3 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   1 |   1 |   1 |   1 |   4 |   4
   2 |   2 |   2 |   2 |   2 |   2
   3 |   3 |   3 |     |     |
     |     |     |   4 |   6 |   6

# 使用where对连接的表进行筛选
select * from t1 full join t2 on t1.id1 = t2.id1 where t1.id1=1;
 id1 | id2 | id3 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   1 |   1 |   1 |   1 |   4 |   4

交叉连接（迪卡尔积连接）

select * from t1 cross join t2;
 id1 | id2 | id3 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   1 |   1 |   1 |   1 |   4 |   4
   1 |   1 |   1 |   2 |   2 |   2
   1 |   1 |   1 |   4 |   6 |   6
   2 |   2 |   2 |   1 |   4 |   4
   2 |   2 |   2 |   2 |   2 |   2
   2 |   2 |   2 |   4 |   6 |   6
   3 |   3 |   3 |   1 |   4 |   4
   3 |   3 |   3 |   2 |   2 |   2
   3 |   3 |   3 |   4 |   6 |   6
# 当命令提供了错无效的命令
select * from t1 inner join t2 on t1.id1>1;
 id1 | id2 | id3 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   2 |   2 |   2 |   1 |   4 |   4
   3 |   3 |   3 |   1 |   4 |   4
   2 |   2 |   2 |   2 |   2 |   2
   3 |   3 |   3 |   2 |   2 |   2
   2 |   2 |   2 |   4 |   6 |   6
   3 |   3 |   3 |   4 |   6 |   6
# 使用都逗号分割表名
select * from t1,t2;
id1 | id2 | id3 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   2 |   2 |   2 |   1 |   4 |   4
   3 |   3 |   3 |   1 |   4 |   4
   2 |   2 |   2 |   2 |   2 |   2
   3 |   3 |   3 |   2 |   2 |   2
   2 |   2 |   2 |   4 |   6 |   6
   3 |   3 |   3 |   4 |   6 |   6

非等值连接

select * from t1 inner join t2 on t1.id1 >= t2.id1;
 id1 | id2 | id3 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   1 |   1 |   1 |   1 |   4 |   4
   2 |   2 |   2 |   1 |   4 |   4
   2 |   2 |   2 |   2 |   2 |   2
   3 |   3 |   3 |   1 |   4 |   4
   3 |   3 |   3 |   2 |   2 |   2

自连接

select * from t2 a inner join t2 b on a.id4=b.id1;
 id1 | id4 | id5 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   2 |   2 |   2 |   2 |   2 |   2
   1 |   4 |   4 |   4 |   6 |   6

自然连接

select * from t1 natural inner join t2;
 id1 | id2 | id3 | id4 | id5
-----+-----+-----+-----+----- 
   1 |   1 |   1 |   4 |   4
   2 |   2 |   2 |   2 |   2

useing（）关键字

select * from t1 inner join t2  using(id1);
id1 | id2 | id3 | id4 | id5
test-# -----+-----+-----+-----+-----
test-#    1 |   1 |   1 |   4 |   4
test-#    2 |   2 |   2 |   2 |   2