ChatGPT is pretty impressive
do these two SQL statements return the same data?
Nice
truncate table F_PLAN;
insert into F_PLAN values (29,5009);
insert into F_PLAN values (29,5009);
mysql> SELECT FPE.ID,
-> FPE.GUID
-> FROM F_PLAN FPE
-> WHERE (FPE.ID IN
-> (SELECT MAX(FPE.ID)
-> FROM F_PLAN
-> WHERE FPE.GUID
-> IN (5009, 5008)));
+------+------+
| ID | GUID |
+------+------+
| 29 | 5009 |
| 29 | 5009 |
+------+------+
2 rows in set (0.43 sec)
mysql> SELECT FPE.ID,
-> FPE.GUID
-> FROM F_PLAN
-> FPE,
-> (SELECT GUID, ID
-> FROM F_PLAN
-> WHERE GUID IN(5009,5008)
-> ) T1
-> where FPE.GUID = T1.GUID
-> and FPE.ID = T1.ID;
+------+------+
| ID | GUID |
+------+------+
| 29 | 5009 |
| 29 | 5009 |
| 29 | 5009 |
| 29 | 5009 |
+------+------+
4 rows in set (0.02 sec)
On Sat, Feb 4, 2023 at 12:15 AM Sayan Malakshinov <xt.and.r@xxxxxxxxx>
wrote:
Hi Kyle,
If id is not unique, the second query can return more rows. For example,
truncate your table and insert just these 2 rows:
insert into F_PLAN values (29,5009);
insert into F_PLAN values (29,5009);
Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner
Oracle ACE
http://orasql.org
On Sat, 4 Feb 2023, 05:04 kyle Hailey, <kylelf@xxxxxxxxx> wrote:
Looks to return the same data.
mysql> SELECT FPE.ID,
-> FPE.GUID
-> FROM F_PLAN FPE
-> WHERE (FPE.ID IN
-> (SELECT MAX(FPE.ID)
-> FROM F_PLAN
-> WHERE FPE.GUID
-> IN (5009, 5008)));
+------+------+
| ID | GUID |
+------+------+
| 8 | 5008 |
| 29 | 5009 |
| 38 | 5008 |
| 59 | 5009 |
| 68 | 5008 |
| 89 | 5009 |
| 98 | 5008 |
+------+------+
7 rows in set (0.40 sec)
mysql> SELECT FPE.ID,
-> FPE.GUID
-> FROM F_PLAN
-> FPE,
-> (SELECT GUID, ID
-> FROM F_PLAN
-> WHERE GUID IN(5009,5008)
-> ) T1
-> where FPE.GUID = T1.GUID
-> and FPE.ID = T1.ID;
+------+------+
| ID | GUID |
+------+------+
| 8 | 5008 |
| 29 | 5009 |
| 38 | 5008 |
| 59 | 5009 |
| 68 | 5008 |
| 89 | 5009 |
| 98 | 5008 |
+------+------+
7 rows in set (0.31 sec)
Create table F_PLAN(
id bigint,
Guid bigint)
;
insert into F_PLAN values (17,5007);
insert into F_PLAN values (20,5000);
insert into F_PLAN values (29,5009);
insert into F_PLAN values (38,5008);
insert into F_PLAN values (47,5007);
insert into F_PLAN values (50,5000);
insert into F_PLAN values (59,5009);
insert into F_PLAN values (68,5008);
insert into F_PLAN values (77,5007);
insert into F_PLAN values (80,5000);
insert into F_PLAN values (89,5009);
insert into F_PLAN values (98,5008);
insert into F_PLAN values (NULL,5008);
insert into F_PLAN values (777,NULL);
On Thu, Feb 2, 2023 at 2:42 PM Kim Berg Hansen <kibeha@xxxxxxxxx> wrote:
You're welcome, Chris.
Note: *If* my explanation is correct, then both original query and the
rewrite might very well produce wrong output. As your lead developer states
"*Both of your versions do not cater for the latest version, right?" *
So if the actual intention of the query *should have been* to cater
for latest version (if the intent was something like my example using FPE2
table alias), you'll rather need to rewrite it to become correct, and not
worry about getting same output as original.
Cheerio
/Kim
On Thu, Feb 2, 2023 at 4:09 PM Chris Taylor <
christopherdtaylor1994@xxxxxxxxx> wrote:
So, to sum up this reply plus your other one, one potential reason for
my not understanding this is that the table alias used in the inner query
might be incorrect?
Because that would make sense as I've never seen a max() function
applied in a subquery to an outer object (and I understood this was a
correlated subquery, I just couldn't wrap my head around what it was
"wanting to do")
Your explanations seem to make a lot of sense.
Thanks,
Chris
On Thu, Feb 2, 2023 at 9:49 AM Kim Berg Hansen <kibeha@xxxxxxxxx>
wrote:
In the original subquery, the use of FPE.{something} makes the
subquery correlated - usually you would only correlate when using EXISTS,
not when using IN.
What happens because of the correlation basically seems to be, that
the subquery will select rows from F_PLAN (inner table), but *only*
if FPE (outer table) has either of two particular values in COL3.
*If* FPE.COL3 has those values, the subquery will either return 1
row containing the ID from the outer FPE row, or zero rows if the F_PLAN
table is empty.
If on the other hand FPE.COL3 does *not *have those values, the
subquery returns zero rows.
Because of the correlation, this happens for each row in FPE - so the
IN evaluates as true if the subquery returns 1 row with the ID in
question
(then it becomes FPE.ID = FPE.ID), but *not* true if the subquery
returns 0 rows (then it becomes FPE.ID = NULL).
In total this is what the rewritten query emulates.
It returns those FPE rows where COL3 has the desired values, ID is
not null, and there exists at least one row in the F_PLAN table.
This does the same as the original.
Typically an IN subquery is used *not *correlated, like for example:
SELECT FPE.COL1,
FPE.COL2,
FPE.COL3,
FPE.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
,
FPE.COL4,
FPE.VERSION
FROM F_PLAN FPE
WHERE (FPE.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
IN
(SELECT MAX (FPE2.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
)
FROM F_PLAN FPE2
WHERE FPE2.COL3 IN
('some_guid_id_1',
'some_guid_id_2')
)
)
ORDER BY FPE.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
Though that could perhaps better have been rewritten using analytic
functions (depending on circumstances.)
Cheerio
/Kim
Regards
Kim Berg Hansen
Oracle ACE Director
Author of Practical Oracle SQL
<https://www.apress.com/gp/book/9781484256169>
http://www.kibeha.dk
kibeha@xxxxxxxxx
@kibeha
<http://twitter.com/kibeha>
On Thu, Feb 2, 2023 at 3:15 PM Chris Taylor <
christopherdtaylor1994@xxxxxxxxx> wrote:
@Dominic / @Sayan
So my SQL skills have a strong weakness when it comes to
understanding when/how to use EXISTS (NOT EXISTS) regularly. Can you
help
me understand by explaining how this rewrite is equivalent when the
first
query uses a MAX function?
(I hate that I have to ask but I'm having a hard time understanding
how this works out)
My lead developer mentions this:
*"If I’m not mistaken this is a table where for every single new
flight plan (even if for the same leg) we have a new row, and the select
seeks to get the latest.*
*Both of your versions do not cater for the latest version, right?"*
What's killing me is that the MAX() function in the original is
referencing the outer table which breaks my brain.
(I'm trying to work through it myself as well to understand)
Original
Rewritten
SELECT FPE.COL1,
FPE.COL2,
FPE.COL3,
FPE.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
,
FPE.COL4,
FPE.VERSION
FROM F_PLAN FPE
WHERE (FPE.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
IN
(SELECT MAX (FPE.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
)
FROM F_PLAN
WHERE FPE.COL3 IN
('some_guid_id_1',
'some_guid_id_2')
)
)
ORDER BY FPE.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
SELECT FPE.COL1,
FPE.COL2,
FPE.COL3,
FPE.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=v4j5UFcyATdPbEaoauNsvCRbq3ARLaoRQSfAob4XXPY%3D&reserved=0>
,
FPE.COL4,
FPE.VERSION
FROM F_PLAN FPE
WHERE FPE.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=v4j5UFcyATdPbEaoauNsvCRbq3ARLaoRQSfAob4XXPY%3D&reserved=0>
is
not null
and exists (SELECT 0 FROM F_PLAN)
and FPE.COL3 IN ('some_guid_id_1','some_guid_id_2')
ORDER BY FPE.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=v4j5UFcyATdPbEaoauNsvCRbq3ARLaoRQSfAob4XXPY%3D&reserved=0>
Thanks,
Chris
On Wed, Feb 1, 2023 at 12:00 PM Dominic Brooks <
dombrooks@xxxxxxxxxxx> wrote:
Oh good spot – the FPE. Alias within MAX(FPE.ID) seems so obvious
now.... how did I miss it 😊
*From: *Sayan Malakshinov <xt.and.r@xxxxxxxxx>
*Sent: *01 February 2023 16:57
*To: *gogala.mladen@xxxxxxxxx
*Cc: *oracle-l@xxxxxxxxxxxxx
*Subject: *Re: Sanity Check - Correlated Select Subquery SQL
rewrite equivalent? Or no?
Since that subquery has no any columns from F_PLAN nor predicates
by columns from F_PLAN, we can rewrite original
SELECT FPE.COL1,
FPE.COL2,
FPE.COL3,
FPE.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605540044%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=65mR53YTMHUZdLJhWpm%2FNsMkXaFC5%2BRm8v3S5Xn%2FTJw%3D&reserved=0>
,
FPE.COL4,
FPE.VERSION
FROM F_PLAN FPE
WHERE (FPE.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605540044%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=65mR53YTMHUZdLJhWpm%2FNsMkXaFC5%2BRm8v3S5Xn%2FTJw%3D&reserved=0>
IN
(SELECT MAX (*FPE.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605540044%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=65mR53YTMHUZdLJhWpm%2FNsMkXaFC5%2BRm8v3S5Xn%2FTJw%3D&reserved=0>*
)
FROM F_PLAN
WHERE* FPE.COL3* IN
('some_guid_id_1',
'some_guid_id_2')))
ORDER BY FPE.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=v4j5UFcyATdPbEaoauNsvCRbq3ARLaoRQSfAob4XXPY%3D&reserved=0>
as
SELECT FPE.COL1,
FPE.COL2,
FPE.COL3,
FPE.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=v4j5UFcyATdPbEaoauNsvCRbq3ARLaoRQSfAob4XXPY%3D&reserved=0>
,
FPE.COL4,
FPE.VERSION
FROM F_PLAN FPE
WHERE FPE.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=v4j5UFcyATdPbEaoauNsvCRbq3ARLaoRQSfAob4XXPY%3D&reserved=0>
is not null
and exists (SELECT 0 FROM F_PLAN)
and FPE.COL3 IN ('some_guid_id_1','some_guid_id_2')
ORDER BY FPE.ID
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=v4j5UFcyATdPbEaoauNsvCRbq3ARLaoRQSfAob4XXPY%3D&reserved=0>
On Wed, Feb 1, 2023 at 4:42 PM Mladen Gogala <
gogala.mladen@xxxxxxxxx> wrote:
On 2/1/23 11:19, Clay Jackson (Clay.Jackson) wrote:
What Dominic said –
Among other things –
where FPE.COL3 = T1.COL3
is NOT part of the predicate in the original query
Not only that, the 2nd query doesn't have MAX function in it,
thereby being definitely not equivalent. I would probably try to
separate
the subquery into a WITH clause, materialize and do join.
Regards
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com ;
<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdbwhisperer.wordpress.com%2F&data=05%7C01%7C%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=KRb7WaFr9WygzFaKIlO2adcfe2pXNgJQ3VzpVMl4Z7U%3D&reserved=0>
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org
<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Forasql.org%2F&data=05%7C01%7C%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2FkgXwMfPbJtaD6tkKVyxrEtyoIeLiCv75AfguRa1W5Q%3D&reserved=0>
