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
B![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAJcAAABbCAIAAADx14DhAAAHDUlEQVR4nO1cwaGrIBCkqRzoJidKydUW3j13z6+CNJAm6IB/QGAXVl2CmPV95pSg4rgDizKi+hm4OKy1yjn38/PjJEEaHw+xrKy1ylrr+VkxkMbHQywrO1TkQywrO1TkQywrO1TkQywrewEVX4+bUur2eB1SdUNtn0TpeVdKqfuz/mxc/EEV/b5bUVuvbTfgQ8U6fKUv/i0Vl+u832/KY7nkpZ3jwoXl/R5L426ROjyQdz0sPmTNMGrPu0K4PV6rteGLo4lmKqLq78/N+OxtTXWFi9vPKhwVl+pAVF6P21KnL7w9XvFS7s/48/Z4wQbvf4YKFK9TcfmUNedR84wLPmVtlX0RXYuPzEZ8trfebp7qPW3gi7jTF2EHzCOfLriIKmzwMKqsOFHxWuND1xx+YN4ktbzqKhVBkyVAxmd1a6wk0akYRapVzFLUnoo4VUQwuPFVzGsOfHD0jleRzJDb8dneing+K24F6lSE7YbXF2HfYNDZjtd2X4w4qy9mJ1S3x2MzPrvRw9WlnTmxsrUqAr1YKua5IQ0PO8z2cwNZc5ZakUA7Ku5mMZxRcQtBvZ+Iz270AqKM3JZfmVFTDgnDMUdFCw/k0uOO02XNZbQS4R0Vd2+miXER9prt+OxFL5eRnb4u8rzYjoOeOE+JUq2If1xFfDNxxKTBGVHK7loZ+NsqHg+xrOxQkQ+xrOxQkQ+xrCypYssU7iHTv+Vc13aN/eecRbOy8lU87aS7EMvKlirSs/v5o1T4D92DMHOUH/sRsxSv7JGLaUeQz36ky0HujGYPluPFsiJUtGUjon2JeFMMNvfoi/mDM8OOIAlvuxykNwIe0CWzYqm4Sg00InrnHioypkBJwmszq1veCH5uE8vK8lVECBWVMw1yVMwIP9A+XG/kEqxsdV+EuERfDGC1+rUQyGZlSRXzeUfalyicWfLYj1AbLxZhlstReCNXYGVXnvqL26PcPXjAMR0N8HvOAAe18WIQXnc5yJ2PUPE0Vmsqfhnd+XyUMcSysv+Tiq0uh1hW9n9SsRViWdmhIh9iWdmhIh9iWVlr1c/AxWFlrgj//f39NgUCklmljCoHkuMlDUPFOkhmNVTkQjKroSIXklkNFbmQzCpTcTZKKTN/kZbrFa/ZKPraJu2nv/T03ipsZuUJKKUnqhQR4xeu9MX3pJkq8vesRA8VJ63M7ErGk8bqrRc2sprNEv73pGPlb0/LOTebDwrdroqL8npaqsjaUWgYCrePtCcRBy765a5cRRyRrcIDVAy1gtYP+MwmxJZf6HZVfE8mXcs8z2FjusKyL74nHU8Af1fiNBXfkzFGZ3mKLDyAFewKIYSQT/zNL3TbKk4apW/Qw9SGinC3lu54morgLiAmPLqwnRXoFaFP9FUxa4UpJeNsU/bFlVxUizNVjIQjd7KwnRVKiSdl1NkomBvTWXFfXPaZzSI7arwf47xxEaT9FFqy8IBxkVAxdVDQVfmFbudJA9/VxlsZrTXsp6k81ovuej5UtNM9KkksjQJZ9ikKW1mByFCnB1mMXzie+mshmdVQkQvJrIaKXEhmpbxf/DtwWTjnhtfPhWRWI6NyIZnVUJELyayGilxIZjVU5EIyq6EiF5JZ5SqShrJzeE58HY2HQ2ZHo/T6sUeK2XX0+vEUWh+vf7mGpkntxsN7qEh5/cAcwP5MD68/tWLgNHTz+pdT7rSCZML5bdgQbjy8X+5aa15IxD5eP8AnJlSlM5Ufs1q4Vm/74eerCMt7ef2p/nSybi5xsd9a4d9REafTTl5/PBVMPEPFT0CriFNoJ6/f17w+9Hwtoy5XmL2k0374ySrm42Afr3/S5J17P68/wV8eWQjfkp1CEmo8/Jh4kVjz+stM4Hp4/fmLZeDtkPLxY3j9/SCZ1VCRC8mshopcSGY1vP7Lww2vnw/JrEZG5UIyq6EiF5JZDRW5kMxqqMiFZFZDRS4ks9pXsZzn7I0+8eJ7/fQC/HZWqd5zvP787NdXke/1kwvw21nF1YHQ7+rj9UPnPf2ejZ7iZCxy6ONC9bCl/ALAe9JhxRxuhufOhntwvH5yAf6RrICKvZwpqHT4PZukSnDfMjsnrcQlvgBANEPwMtXXnam8fNM9aEKxTrvvun70I1sh7AWLS46XRavLBZNfAACKL+0ItfLiOw5nq/jORCQW4B/Jiu6LR3v9vhdRI8XyL3XGSZvJf83BzG71CwDSVcR5hVyAfywrSpvDvf7Z6GkCu/o3Dd6huvgVA6O1nt5uMrrovuhlwFJF7KfL8vrJBfjNrMB7IOhNxi5e/wKogR+3yrEivgiUD4F+t/AFgJBjTfaxAOCnmxPeu6nw+skF+H2+d9PX6298LbgOxRdXJD9fSwOp4ko77IF0qvwlXsnxkoYxA1cHyayG1395ON8XrbXfblIDTfgHU64pNvZpEnwAAAAASUVORK5CYII=)
无条件连接
select * from A cross join B
有条件连接
select * from A full join B on A.name = B.name
[指定条件,全外连接]
select * from A full join B using(name)
[指定条件,全外连接,去重]
select * from A natural join B
[自动寻找同名字段为条件,内连接,去重]
select * from A natural full join B
[自动寻找同名字段为条件,全外连接,去重]
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顺序很可能能够使它运行的更快。
二、子查询(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