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

chat GPT says:
what is the difference between the following two SQL queries?

It is important to note that the first query will only return one row, as it is retrieving the row with the maximum ID value, while the second query will return multiple rows if there are multiple rows in the F_PLAN table with the same COL3 value in the list ('some_guid_id_1', 'some_guid_id_2').

Both queries will return the same data, as long as the data in the F_PLAN table does not change between the two queries. The difference lies in the way the data is filtered and retrieved from the F_PLAN table.

Query 1 uses a subquery to get the maximum ID of the F_PLAN table where the COL3 value is in the list of values ('some_guid_id_1', 'some_guid_id_2'). The main query then retrieves all columns of the F_PLAN table where the ID is equal to the maximum ID from the subquery.

Query 2 uses a derived table t1 to get the COL3 and ID of the F_PLAN table where the COL3 value is in the list of values ('some_guid_id_1', 'some_guid_id_2'). The main query then joins this derived table with the F_PLAN table on the COL3 and ID columns.

In both cases, the result set will contain the columns COL1, COL2, COL3, ID, COL4, and VERSION from the F_PLAN table, filtered by the COL3 values in the list ('some_guid_id_1', 'some_guid_id_2'). The resulting data will be ordered by ID.

On Wed, Feb 1, 2023 at 1:00 PM Chris Taylor < christopherdtaylor1994_at_gmail.com> wrote:

> That's what my gut was saying also - but having a devil of a time turning
> it into an equivalent version to get rid of the correlated subquery.
>
> Thanks,
> Chris
>
>
> On Wed, Feb 1, 2023 at 11:11 AM Dominic Brooks <dombrooks_at_hotmail.com>
> wrote:
>
>> No. Not equivalent. But they might appear to be depending on what data
>> you run it with.
>>
>> There might be constraints etc which mean they return the same results
>> under your circumstances. But semantically they are not equivalent.
>>
>>
>>
>>
>>
>> *From: *Chris Taylor <christopherdtaylor1994_at_gmail.com>
>> *Sent: *01 February 2023 15:40
>> *To: *oracle-l_at_freelists.org
>> *Subject: *Sanity Check - Correlated Select Subquery SQL rewrite
>> equivalent? Or no?
>>
>>
>>
>> Hey guys, just need a quick sanity check here.
>>
>> Are these queries equivalent? My gut says not exactly but maybe. The
>> results returned are identical in the test cases I ran, but I fear I'm
>> missing something.
>>
>> Original SQL (27 seconds to return 26 rows)
>> Modified Version - Same rows but 336 ms
>>
>>
>>
>> Original
>>
>> Modified
>>
>> 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%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,
>>
>> (SELECT COL3, ID
>>
>> FROM F_PLAN
>>
>> WHERE COL3 IN
>>
>> ('some_guid_id_1',
>>
>> 'some_guid_id_2')
>>
>> ) t1
>>
>> where FPE.COL3 = T1.COL3
>>
>> and 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>
>> = T1.ID
>> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ft1.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=HQamwon1ueUsJNjPKU2%2FZiWw0CQtDQZQk7xOBvvGKjs%3D&reserved=0>
>>
>> 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>
>>
>>
>>
>>
>>
>

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