Re: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?

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_at_gmail.com> 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_at_kibeha.dk
> _at_kibeha
> <http://twitter.com/kibeha>
>
>
> On Thu, Feb 2, 2023 at 3:15 PM Chris Taylor <
> christopherdtaylor1994_at_gmail.com> wrote:
>
>> _at_Dominic / _at_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_at_hotmail.com>
>> 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_at_gmail.com>
>>> *Sent: *01 February 2023 16:57
>>> *To: *gogala.mladen_at_gmail.com
>>> *Cc: *oracle-l_at_freelists.org
>>> *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_at_gmail.com>
>>> 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>
>>>
>>>
>>>
>>

--
http://www.freelists.org/webpage/oracle-l