PostgreSQL-join多表连接查询和子查询金沙官网线上

 

一、多表连接查询

1、连接方式概览

[inner] join

内连接:表A和表B以元组为单位做一个笛卡尔积,记为表C,然后在C中挑选出满足符合on 语句后边的限制条件的内容。

left [outer] join

左外连接:在内连接的基础上,将A中有但C中没有的元组也加上。由于C的列数比A的行数多,所以这新增的元组左边照搬A,右边为null。

right [outer] join

右外连接:在内连接的基础上,将B中有但C中没有的元组也加上。由于C的列数比B的行数多,所以这新增的元组右边照搬B,左边为null。

full [outer] join

全外连接:相当于在内连接的基础上同时做左连接和右连接,表A和表B中的都要,两边不满足条件的都为null。

 

cross join

与上述内外连接可接连接条件不同,这一个本质是 select * from tableName1, tableName2 这种无条件交叉连接的简写,如

select * from tableName1 cross join tableName2

仅是做笛卡尔积,表A的每一行都和表B的每一行相连接,没有条件,不可后接on,结果是两表行的乘积和列的和。

 

2、条件语法概览

table1 { [inner] | { left| right| full} [outer] } join table2 on boolean_expression

用 ON 关键词后接连接条件,结果中会包含两表中条件引用到的列(条件列无论是否同名皆可)

注:select * from table1,table2 where table1.column1=table2.column1 这是一种可替代select *from table1 [inner] join table2 on boolean_expression 的快捷语法

单纯使用内连接的话可以像前者这样写,简便直白

table1 { [inner] | { left| right| full} [outer] } join table2 using ( join column list )

用 USING 关键词后接一个用逗号分隔的字段名列表,这些字段必须是连接表共有的并且其值必须相同,连接结果去重。(条件列同名,手动指定,去重)

因此,USING (a, b, c) 等效于 ON (table1.a = table2.a AND table1.b = table2.b AND table1.c = table2.c) ,结果中abc字段各仅一个。

table1 natural { [inner] | { left| right| full} [outer] } join table2

NATURAL 是 USING 的缩写形式:它自动形成一个由两个表中同名的字段组成的 USING 列表。

USING 和 NATURAL 的原理是一样的,靠两表中同名的字段连接值相同的部分,不够灵活。(条件列同名,自动寻列,去重)

 

3、简单的栗子

有两表A和B

A金沙官网线上 1 B金沙官网线上 2

 

无条件连接

select * from A cross join B

金沙官网线上 3

 

有条件连接

select * from A full join B on A.name = B.name

[指定条件,全外连接]

金沙官网线上 4

select * from A full join B using(name)

[指定条件,全外连接,去重]

金沙官网线上 5

select * from A natural join B

[自动寻找同名字段为条件,内连接,去重]

金沙官网线上 6

select * from A natural full join B

[自动寻找同名字段为条件,全外连接,去重]

金沙官网线上 7

 

4、更多表连接

连接ABCDE多个表可以这样: 也可以这样:
select * from (((
A inner join B on A.a = B.b)
inner join C on C.c = A.a)
inner join D on D.d = C.c)
inner join E on E.e = D.d
select * from
A inner join B on A.a = B.b
inner join C on C.c = A.a
inner join D on D.d = C.c
inner join E on E.e = D.d

 

大多数数据库系统会分析整个from子句,然后尝试确定组合连接表的最有效方式,也就是说数据库不不一定会从最里边的括号开始执行查询。这很可能打乱你的逻辑设计,得到意外的结果。一些数据库系统中的优化器对于join定义的顺序很敏感。如果你发现使用很多join的查询在一个较大的数据库上执行花很长时间,通过改变join顺序很可能能够使它运行的更快。

 

本文出处:http://www.cnblogs.com/wy123/p/6238844.html 

二、子查询(subquery)

在子查询中用到一些关键字,分别是“ANY、EXISTS、IN、SOME”,在这些关键字之前还可以添加“NOT”。

通常的用法如:

EXISTS (subquery)

expression [NOT] IN (subquery)

row_constructor [NOT] IN (subquery)

expression operator ANY (subquery)

expression operator SOME (subquery)

row_constructor operator ANY (subquery)

row_constructor operator SOME (subquery)

 

半连接:对于“subquery”,使用IN、EXISTS等谓词表示存在即可,称之为半连接。

SELECT * FROM D

WHERE EXISTS

( SELECT * FROM E WHERE D.id= E.id

AND E.s > 2500)

ORDER BY x

 

反半连接:对于“subquery”,使用NOT IN谓词表示不存在即可,称之为反半连接。

SELECT * FROM E

WHERE id NOT IN

(SELECT id FROM D WHERE lid=1700)

ORDER BY id

 

 

 


整理自:

——

[原创]多表连接(join)小结 - ybwang1989 - 博客园

——

数据库查询中的表连接(一) - 那海蓝蓝的日志 - 网易博客

数据库查询中的表连接(二) - 那海蓝蓝的日志 - 网易博客

——

JOIN操作PostgreSql » InBi's Blog

  

  最近遇到一个存储过程在某些特殊的情况下,效率极其低效(同时服务器CPU资源占用急剧上升,导致整个服务器相应缓慢)
  至于底下到什么程度我现在都没有一个确切的数据,因为预期很快就可以查询出来结果的SQL,实则半个小时都出不来,后面会有截图
  观察执行计划的时候发现中间有一步中出现一个类似如下非常规的连接提示警告,如下图

  金沙官网线上 8

 

  no join predicate 意思就是没有连接谓词,表之间join的时候没有指定连接谓词可以导致no join predicate,
  但是反过来也是一定成立的吗,明明写了连接条件,仍旧提示no join predicate,为什呢?
  下面先从no join predicate 入手开始,说明什么时候会出现no join predicate ,以及原因和解决办法。

 

 

 1,未指定连接条件下导致的no join predicate 

  两个表在没有指定连接条件的情况下,做运算的结果是计算器笛卡尔积,当然是没有连接谓词的,提示no join predicate 也很容易理解
  上一段简单的代码演示一下,如下创建两张表,#t1,#t2,至于测试数据为什么是这样子,我下面会继续做解释

create table #t1(id int,name varchar(100))
create table #t2(id int,name varchar(100))

insert into #t1 values (1,newid())
insert into #t1 values (1,newid())

insert into #t2 values (1,newid())
insert into #t2 values (1,newid())

首先看计算笛卡尔积的时候的执行计划,Nested Loops 中的红叉叉,就表明是没有连接谓词,当然这个查询SQL中也确实没有连接谓词,这种情况下也很容易理解。

金沙官网线上 9

 

2,指定了连接条件下的no join predicate 

  这里即便是指定了连接条件,仍然提示没有连接谓词,这个原因又是为什么呢?
  此时就需要看表中的数据特点了,从上面造的测试数据可以看出,#t1表id = 1 的是两行,#t2 表的同样,id = 1的数据也是两行
  此时两张表的join,是多对多的关系,多对多的情况下就是计算笛卡尔积,这就是这种情况下提示没有连接谓词的原因。
  详细请参考:http://www.cnblogs.com/liwei225/p/5056460.html,大神早就有详细的分析,感谢liwei225大神的分享

  金沙官网线上 10

  

  不过我这里还有一个疑问,还是上述两张表,指定连接条件,但是不指定查询条件,也就是没有where a.id = 1,此时就没有提示no join predicate
  这个原因我也没弄懂,后面再想想为什么,希望路过的大神帮忙解释一下,谢谢。

  金沙官网线上 11

 

 3,指定了连接条件的情况下,某些查询条件下会出现no join predicate 

  这是一个实际业务的SQL,从存储过程中扣出来的代码,因为有比较多的查询条件,最后组装的动态SQL也不完全一样,绝大多数情况下是没有问题的,
  但是当在where 条件中添加某一个查询条件之后,效率就开始严重下降,至于下降到什么程度,截图是运行了35分钟之后取消的
  在这个SQL运行期间,服务器CPU直接飙升至100%,并且是持续性的

  金沙官网线上 12

  截图一个对比测试的,仅仅在上面的SQL中加了一个OPTION(FORCE ORDER)查询提示,强制按照书写的表的顺序驱动,结果2秒钟就出来结果了
  执行计划跟上面是不一样的,同时也没有显示no join predicate,不能说加了一个强制提示就有了连接谓词,不加强制提示就没有连接谓词吧?
  从对比情况看,可以说明,没有非常严重的外界因素干扰,比如缺少索引,统计信息有问题等等
  倘若如此,加了OPTION(FORCE ORDER)查询提示的SQL与不加OPTION(FORCE ORDER)查询提示的SQL差别不可能这么大,一定是执行计划的选择出了问题。

  金沙官网线上 13

 

  那么就继续分析这个执行计划。
  通常情况下,我们会首先分析执行计划,什么索引使用(被抑制)了,索引碎片了,参数嗅探了,统计信息过期了(取样不够),都一一分析过,
  这些额外因素只会在一定程度上拖慢SQL的效率,而不是拖慢到如此相差几个数量级的程度
  那么来分析,没有加OPTION(FORCE ORDER)为什么会这么慢?
  实际上,这个SQL的执行计划只能从预估执行计划来看,因为实在等不到这个SQL运行完成而看实际执行计划
  如题,预估执行计划显式,中间有一步存在一个如上所述的没有连接谓词警告

本文由金沙官网线上发布于数据库,转载请注明出处:PostgreSQL-join多表连接查询和子查询金沙官网线上

您可能还会对下面的文章感兴趣: