Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the difference between Oracle and PostgreSQL subquery". Interested friends might as well take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what's the difference between Oracle and PostgreSQL subqueries?"

The exact expression should be that when the agg function appears in the having condition of the subquery and depends on the relevant fields of the parent query, Oracle supports it but PG does not.

Oracle

Create tables, insert data, execute queries, OK!

TEST-orcl@DESKTOP-V430TU3 > drop table tbl1;Table dropped.TEST-orcl@DESKTOP-V430TU3 > drop table tbl2;Table dropped.TEST-orcl@DESKTOP-V430TU3 > drop table tbl3;Table dropped.TEST-orcl@DESKTOP-V430TU3 > TEST-orcl@DESKTOP-V430TU3 > create table tbl1 (id int,c1 int,c2 int,c3 int); Table created.TEST-orcl@DESKTOP-V430TU3 > create table tbl2 (id int,c1 int,c2 int,c3 int); Table created.TEST-orcl@DESKTOP-V430TU3 > create table tbl3 (id int,c1 int,c2 int,c3 int) Table created.TEST-orcl@DESKTOP-V430TU3 > TEST-orcl@DESKTOP-V430TU3 > insert into tbl1 values; 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into tbl1 values; 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into tbl1 values; 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into tbl1 values; 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into tbl1 values; 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into tbl1 values 1 row created.TEST-orcl@DESKTOP-V430TU3 > TEST-orcl@DESKTOP-V430TU3 > insert into tbl2 values; 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into tbl2 values; 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into tbl2 values; 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into tbl2 values; 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into tbl2 values; 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into tbl2 values 1 row created.TEST-orcl@DESKTOP-V430TU3 > TEST-orcl@DESKTOP-V430TU3 > truncate table tbl3;Table truncated.TEST-orcl@DESKTOP-V430TU3 > insert into tbl3 values; 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into tbl3 values; 1 row created.TEST-orcl@DESKTOP-V430TU3 > TEST-orcl@DESKTOP-V430TU3 > commit;Commit complete.TEST-orcl@DESKTOP-V430TU3 > TEST-orcl@DESKTOP-V430TU3 > truncate table tbl3;Table truncated.TEST-orcl@DESKTOP-V430TU3 > insert into tbl3 values 1 row created.TEST-orcl@DESKTOP-V430TU3 > TEST-orcl@DESKTOP-V430TU3 > select a. Maxim sum (a.c1) as sum_c1,sum (a.c2) as sum_c2 2 from tbl1 a TBL 2b 3 where a.id = b.id 4 and exists (select 1 from tbl3 c where c.id = a.id group by c.id having sum (c.c1) > sum (a.c1)) 5 group by a.id ID SUM_C1 SUM_C2- 1 9 9TEST-orcl@DESKTOP-V430TU3

However, even if Oracle supports this way of writing, it is not recommended because the semantics of SQL are not friendly and difficult to understand.

PG

Create table, insert data, execute query, error.

[pg12@localhost] $psqlExpanded display is used automatically.psql (12.1) Type "help" for help. [local:/data/run/pg12]: 5120 pg12@testdb=# drop table tbl1;s; insert into tbl1 values (1min1); from tbl1;insert into tbl2 select * from tbl1;insert into tbl3 select * from tbl1;commit;ERROR: table "tbl1" does not exist [local:/data/run/pg12]: 5120 pg12@testdb=# drop table tbl2 ERROR: table "tbl2" does not exist [local:/data/run/pg12]: 5120 pg12@testdb=# drop table tbl3;ERROR: table "tbl3" does not exist [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=# create table tbl1 (id int,c1 int,c2 int,c3 int); CREATE TABLE [local:/data/run/pg12]: 5120 pg12@testdb=# create table tbl2 (id int,c1 int,c2 int,c3 int) CREATE TABLE [local:/data/run/pg12]: 5120 pg12@testdb=# create table tbl3 (id int,c1 int,c2 int,c3 int); CREATE TABLE [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=# insert into tbl1 values; INSERT 0 1 [local:/data/run/pg12]: 5120 pg12@testdb=# insert into tbl1 values INSERT 0 1 [local:/data/run/pg12]: 5120 pg12@testdb=# insert into tbl1 values; INSERT 0 1 [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=# insert into tbl2 select * from tbl1;INSERT 0 3 [local:/data/run/pg12]: 5120 pg12@testdb=# insert into tbl2 select * from tbl1 INSERT 0 3 [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=# insert into tbl3 select * from tbl1;INSERT 0 3 [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=# commit WARNING: there is no transaction in progressCOMMIT [local:/data/run/pg12]: 5120 pg12@testdb=# select a. Maxim sum (a.c1) as sum_c1,sum (a.c2) as sum_c2pg12@testdb-# from tbl1 a Magi TBL2 bpg12@testdb-# where a.id = b.idpg12@testdb-# and exists (select 1 from tbl3 c where c.id = a.id group by c.id having sum (c.c1) = sum (a.c1)) pg12@testdb-# group by a.id ERROR: aggregate functions are not allowed in WHERELINE 4:... ere c.id = a.id group by c.id having sum (c.c1) = sum (a.c1) ^ [local:/data/run/pg12]: 5120 pg12@testdb=#

The error is "aggregate functions are not allowed in WHERE", but how can the agg function appear in WHERE when the condition is clear in having? The reason is that PG believes that the a.c1 in the condition sum (c.c1) = sum (a.c1) appears in the parent query, which is considered to be a condition in WHERE.

At this point, I believe you have a deeper understanding of "what is the difference between Oracle and PostgreSQL subqueries?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.